Linking Split Database Frontend with Multiple Backends

Question

I have an Access 2010 database I use to manage events for several clients. I want to split the database in such a way that each client has its own backend. The frontend would be the same for all clients, and the tables in the backend would be the same - just the data would be different. I need some help finding out 1) If this is good idea or if there is a better way to keep the data separated by client, 2) how to create the different backends with empty tables so I can add new data, and 3) how to change the links to the different backends from the frontend.

This is not a shared database. I am the only person who uses it - I just want to keep the data separate.

Thanks for your insights! J.

Answer 1

If you want to give the app to your client at some future date, then, when that future date comes, Create a new blank file. Then run Make Table queries for all your tables filtering for CustomerID to the new file.

What I would do is have an unbound combobox on your startup form to select the Customer you want to work on. Make sure you leave this form open all the time you can hide it but not close it.

Then all your forms should use queries as the Recordsource. The criteria for the CustomerID column should be:

=Forms!startupformname!cboCustomer

Then each form will open only for the customer you selected. Need to work on a different customer, then just change the selected customer.

Answer 2

Thanks. I can work with that. I'm already doing something similar to work with different year data for the same client, but with temporary variables instead of a combobox.

Answer 3

I have to admit it seemed like a good idea at the time, but the more I looked into it I started thinking maybe not. Originally my thought was that eventually I might want to turn over the database to the client and would not want them to have access to another client's data.

I would welcome suggestions on how to keep client data separate and on how I can set it up so when I'm working on one client all I see is that client's data.

I know that's not much to go on, and I don't want to get into how the whole database is set up, but a suggestion may spark an idea for me that would help me move forward.

Answer 4

Can you explain what you think you would gain by doing this? While it is certainly possible, I don't see any real value that will make the extra work worthwhile.

If you can tell us what you expect to gain we may be able to offer some better ideas.

Answer 5

Splitting your database into separate systems for individual clients seems like a bad idea.  Your table records should include the client they relate with.  Then when you want to see particular information about an item related to a specific client, you specify that client as part of your query criteria.