How Do I? (Access 2000)

Topic

New Reply

I have created multiple copies of a database. I have them all named the same but in different folders on my hard drive. Is there a way that I can query all the databases for information at the same time? I mean for example in database A I have john Smith and in Database B I have Reginald Arnold but both customers have activated their service I want to create a query or report to look in both Database A and B to find anyone who activated their service. If anyone can help I have a blank copy of the database I can send zipped it is 196K Thank you.

Reply | Quote

Replies

Why do you have multiple copies of the same database with the same name in different folders, and what exactly is it that you’re trying to do? If you’ve put segments of your data in different databases, you’re asking for trouble, because it will rapidly become impossible to maintain. What was the idea behind this?

Reply | Quote

Each of the databases are used for different offices that I have. Each database keeps track of the same information but for different customers from different markets. The person in charge of each office did not want their data combined with any other offices data. So what I am trying to do is find a way to query all of them from one main database or something like that.

Reply | Quote

You have built yourself a nightmare. Data from one office doesn’t contaminate another, no matter what the offices in question might think, and it isn’t necessary for everyone to see all the records if the database is structure properly. What you should have done was to look into partial replication which would have allowed each office to see its own data but would still sync that data back to a single data store that you could use for reporting and other purposes. As it is, it is going to be very slow and awkward to create union queries for the data from all those separate databases, assuming that it will work at all.

Reply | Quote

How woudl I do what you are proposing as far as using a main database but without allowing access to other offices information? I am new to this I have built databases and such but not jumped deeply into the power that access has to offer. Would it be easier to see the database I was talking about? If so I can email you a blank copy.

Reply | Quote

It isn’t necessary to see the database. There are several methods, but they depend on what kind of network arrangement you have. Any method would require that each record be identified by Office as well as any other information, because you would need that in order to filter the recordset for a particular office.

One way is to use Access security and assign each office to a separate user group. When the users log in, you would check the user group they belonged to and filter records for that office. Another way is to use partial replication, which is more complicated but can even be used on machines like laptops that are not permanently connected to a network. Check the Microsoft site for information on partial replication.

None of the methods are simple but neither is trying to manipulate a bunch of separate databases to make them behave like a single coherent database.

Reply | Quote

are you saying that john smith is database a
but not in database b

Reply | Quote

Yes the databases are for different offices so the information collected is the same but all custoers are different. The person in charge of each office did not want their information mixed with the information of any other office.

Reply | Quote

Charlotte is absolutely right here. We have spent the last 18 months fixing databases because our client insisted early on that they didn’t want any data from different departments comingled. Then they discovered that they had to maintain addresses in multiple databases, the right hand never knew what the left hand was up to, etc.

You indicate that all of the databases are on your hard drive – if that is always the case, replication would work pretty well. On the other hand if they are normally located on the user’s hard drive and the users are some distance apart so modems would be involved, replication can take quite a while. It also tends to bloat the database significantly. I would probably look at the idea of filtering data so users only see their own if it can be implemented without a huge effort.

Reply | Quote

If you do not want to use security or replication, a “cheap” way to accomplish this is to create a new, central, database that has only pointers to the tables on the other databases. Link to each database’s tables and give the local link name a different name for each (it is required that they be different anyway, but you could name them so it makes sense to you).

For example, if each office database has an Address table, link Address1 to office number 1’s Address table, link Address2 to office number 2, Address table, etc. Next, create a UNION query that combines all Address tables together:

SELECT FirstName, LastName, Address, City, State, Zip FROM Address1

UNION SELECT FirstName, LastName, Address, City, State, Zip FROM Address2

UNION SELECT FirstName, LastName, Address, City, State, Zip FROM Address3;

Call this the Address query. Now, any other queries, reports, forms, etc. that were used to refer to the Address table (in the office databases) will now refer to the compendium of Address tables in all offices in this composite database! It will run slower than the other two ways (security and replication), but it will do the job.

Reply | Quote

Thank you all for your help!

Reply | Quote

I thought I would put my 2 cents in …

I also agree with Charlotte, however, I have wore the shoes “the boss wants it”. After learning the hard way, I found it better to sit the boss down and explain why (sometimes) their idea isn’t the most practical and efficient not to mention logical. If you can explain this on their terms, you will save yourself a lot of headaches.

At any rate, I would make copies of the 4 db’s and in your spare time, clean them up as Charlotte mentioned. In the intrim combine the tables, assuming the tables have the same name, fields have the same name, and the structure is the same. Move the tables to a front end DB and link the tables to each office. Trust me they won’t know the difference.

Good Luck,

Reply | Quote