Want to link multiple tables

[jh3016]

I have an Access database that will be used by 10 different people across the U.S. Everyone will have the same table names, just the data would be different. I originally thought of splitting the database, but everyone complains because that is too slow.

My next thought would be to have each person have their database on their server in their district and then just link to the master database. Is this possible?

Then I thought of - supposed each person kept their database on their local drive and then just sent me the database and I would export the information each week. Is there a macro that could automate this?

I need help thinking this through.

Thanks in advance.

 

[bhoran]

Essentially you can do any of those things.

Why does each individual need their own database?

If you had one database that can be accessed by all people that would probably be best as you can build it to avoid integrity issues (i.e. duplicate records etc etc.)

Otherwise you can set up a database with all the other databses tables linked but this obviously means you need to build several databases or at least maintain several databases.

You can only do the above if users have physical access to the areas that these databses are stored.

 

[jh3016]

Thanks for the quick response.

The information contained is highly confidential. How would I make sure each manager can only go into theirs?

(Note, I am not great with Access, I'm still just learning.)

 

[bhoran]

You can set-up user-level security, it is a little tricky but there is a lot of good info on the FAQ's on this site and at

http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp

Once you have the security setup you can allow user to only look at certain data via the queries and formd you assign to that user or group of users.

As I say it is a little tricky and can take a little bit setup but it seems to work fairly well.

 

[bhoran]

However, there are flaws in the security, I am not totally up with it all but there is plenty of info on this site. I have seen references that suggest if your data needs to be super secure MS Access may not be the best product for you.

If you were to have separate databases for each manager this security issue would still not be solved.

I guess you could run code so that only records where the user name matches the user logged in could be seen or updated and you stamped each record with the current user name when it has been added or modified that would also help with the security.

Security seems to be a tricky area and I would be lying if I said I fully understood all the issues around it, that is why I have clarified my last post.

Cheers