Application for Multiple Databases

[TracieLee]

Need to know the best design for this sort of thing..... I have Multiple client Databases which all have similar tables containing names and addresses. I need to have one central database that can link to each and write queries that will run against each database individually.
Is the best way to import each table to a dummy table, run the update and export it back again?
Is it possible to use a listbox populated with linked tables to select a table, which is then used as a parameter for a query?

 

[Galaxiom]

Would definitely not use a dummy table. You can change the linked tables for each client. This would use the After Update procedure of a combobox to link to the appropriate tables.

However, instead I would consider combining all the client databases after adding a field to each record to indicate the clientID it belongs with. Then add clientID to all the queries with a where clause that compares the clientID with the one selected on a master form.

Also add something to clearly display the current client on every form so you don't find youself working on the wrong one.

 

[TracieLee]

I was afraid that was the solution. It is not really an option to combine the databases into one, so I was trying to find a quick fix. Can I develop a front end to select a table and have that selection passed as a parameter into a generic query?
How do I use the AfterUpdate in a combobox to link to the table I choose?

 

[Banana]

Yes you can use a parameter query.

If you don't mind your queries living & dying by the form, you can use control reference... something like this:

SELECT *
FROM SomeTable
WHERE ClientID = [Forms]![MyForm]![ClientID];

However, I'd heed Galaxiom's suggestion for combining the database. It will serve you the best in long run. Why can't you combine it?

 

[Galaxiom]

In plain sql you cannot even use a variable to select a different table or field unless you construct the query in VBA. Even this is a lot of work and besides, you want to select a different database. So you have to change the linked tables to a different database or work entirely with recordsets in VBA.

Otherwise you could put all the backend databases onto an SQL server and convert all the queries to PassThrough. When the client changes, convert all the connection strings to the appropriate backend. There are several catches in this approach.

It sounds like you have not yet built the front end for this. If so combining is absolutely definitely the way to go. It just requires a set of append queries with one field added.

If the data is brought in by clients from offsite then set up an importing and updating procedure to get the data into your combined backend.

 

[Atomic Shrimp]

I agree that combining them and adding a clientID would be the most robust solution.

A possible workaround:
Create an empty database
Link the tables in from each of the different instances - so you might end up with linked tables named something like:
tblAddresses_Acme
tblAddresses_Zenith
tblAddresses_Prima
etc

Then in this database, create a bunch of union queries (one for each group of tables), like this:

SELECT tblAddresses_Acme.*, "Acme" AS SourceDB
FROM tblAddresses_Acme

UNION

SELECT tblAddresses_Zenith.*, "Zenith" AS SourceDB
FROM tblAddresses_Zenith

UNION

SELECT tblAddresses_Prima.*, "Prima" AS SourceDB
FROM tblAddresses_Prima;

Then use these union queries as if they were big, combined tables.

You could also use the results of these queries in the process of combining the databases later on.

 

[TracieLee]

Thanks for that. As you have correctly identified, I have not yet made a start on it yet, just wanted to make sure I was heading in the right direction when I do. I am sure I will be in touch once I start and need help with the next hurdle!!! Thanks so much for your time. Cheers!!

 

[TracieLee]

Banana, Each of the client files are set up differently. Most have some of the same fields, but the naming conventions are different. My Application needs to take all of the data from an individual file, and be able to manipulate it depending on the state of each individual file. Each client would send through an excel or csv file which we clean up to our standard to use in mailings. Ideally I need the front end to be used by more than one person and have the data in the client file updated for printing. There are hundreds of regular clients and some one-off clients, so the user needs to be able to identify the file that needs cleansing and run the appropriate queries on it. I hope this explains better what I am trying to do.

 

[Atomic Shrimp]

Yikes - if the data structures aren't consistent across these multiple database, then you've got even more work to do. Trying to create forms and reports that can cope with a set of input data that is variable in structure is going to be really difficult - perhaps impossible.

What you need to do is create uniform versions of the data first. Fortunately, you may be able to do this without altering any of them - again, by linking them into a single new database, then creating queries based on them - queries designed to work on their individual differences, but designed to generate uniform output.

Let's say, for example, that you have two dbs - one for company Acme, another for company Pinnacle.
The Acme db contains customer addresses in a table named Customers, with fields: Custname, CustAddr1,CustAddr2,CustAddr3,CustAddr4,CustAddr5,Phone, ContactName
The Pinnacle db contains customer addresses in a table named tblClients, with fields: Customer, Address1,Address2,Address3,Address4,zip,tel, person

You can link both of these tables into your combination db, then you can create two queries - one based on each table, renaming the output columns so that they are the same as the output fields in the other query - like this:

SELECT
Customers.Custname AS Customer_Name, 
Customers.CustAddr1 AS Address_Line1, 
Customers.CustAddr2 AS Address_Line2, 
Customers.CustAddr3 AS Address_Line3, 
Customers.CustAddr4 AS Address_line4, 
Customers.CustAddr5 AS Address_line5, 
Customers.Phone AS Telephone, 
Customers.ContactName AS Contact, 
FROM Customers;

and

SELECT
tblClients.Customer AS Customer_Name, 
tblClients.Address1 AS Address_Line1, 
tblClients.Address2 AS Address_Line2, 
tblClients.Address3 AS Address_Line3, 
tblClients.Address4 AS Address_line4, 
tblClients.zip AS Address_line5, 
tblClients.tel AS Telephone, 
tblClients.person AS Contact, 
FROM tblClients;

- Then use a union query (as discussed above), but based on the first set of queries, rather than the raw tables.

 

[Galaxiom]

Continuing on from AtomicShrimp's technique I would create a table to hold the translation information and use VBA to build the required query. This will be much easier to maintain than building custom queries to suit each client.

 

[Atomic Shrimp]

That sounds like a vastly more elegant method - new additions to the system only require new translation records in the table - but there's more initial work to do to get there (and it requires reasonably advanced coding, I think)

 

[TracieLee]

Galaxiom, forgive my ignorance, are you talking about the translation info being part of the initial import? I know I need to get the initial data into a standard format, but it may be beyond me to achieve it. It would be nice to be able to select which fields to import and rename to our standard When the original files are brought into or linked to the database. Am I on the right track?

 

[Galaxiom]

The idea is the table could have records with the ClientID, StandardFieldnameID and the text of the matching fieldname from the import data.

I would porobably go further by replacing the ClientID with ImportTypeID. The client records would then have a field where you could choose a particular ImportTypeID. This way you could use the same import type for multiple clients who happened to have the same layout of original data.

When you press your import button the VBA would generate a query using the information in this table and then run it.

But it can be done in other ways. Since you are going to be importing from csv or Excel you could set up a separate saved import template for each client or type of import so the fields are named to match your standard names during that import.

It would also be possible to record the name of a particular import template in the client record and have the import completed automatically in VBA.

 

[TracieLee]

If I am importing every client's information into a master table, do I need to clear out the table each time I have finished with it? Each client has changes to their data every month?

 

[Galaxiom]

You could delete the old data just before importing the new. That way you have the current information if you need to consult it for some reason.

If you had any need for previous information you could include an import batch field and keep it all.