Semi-Complex question about multiple databases

[cowboy]

I have multiple databases that have the exact same table format just with individual information. They are split up so multiple can work on the project at one time. I want to have a central database that can suck up all the information into 1 table. I did some google searches on this and read different approaches for about an hour but was unsatisfied, what is the best way to join the information?

[NTC]

well; first let's be sure the obvious isn't overlooked; you mention they are split - - but you don't specifically say that they are geographically separated. So if these multiple databases are all on the same LAN - then they should all be linked to the same Back End file.

Next you say "I want to have a central database that can suck up all the information into 1 table" - - there's no way any relational database is consolidated into just 1 table - - so that's really an odd requirement; but maybe your terminology was just imprecise and you meant into 1 db rather than 1 table.

So if you have these multiple dbs geographically separated - then there are issues; some of which are outside of Access per se:
Generic advice on remote Access database options where one has only generic internet and not a high speed private WAN with Terminal Services capabilities…

Option 1 – go with Access’ embedded replication feature (if .mdb format – not available with .accdb 2007 format) presuming you can co-locate or vpn them together. If you are not familiar with this feature - read up on it in a quality text book. It takes some management.

Option B is to go with web architecture. find a web developer - turn over to him/her your Access db for them to look at as the prototype design...and get their quote. You will pay to have them develop it and then there will be the recurring for the hosting company...

Option III is the AccessTables.com service; this allows everyone to operate a copy of the Access db locally and then you send in the tables - they consolidate/replicate all the data - - and return to everyone a consolidated set of tables. Is great as long as the requirement is not for instantaneous shared data. If periodic updates is sufficient and the user base are all part of a team - this can be a good solution.

Option 3.5 is an online commercial db service - I would recommend DabbleDB.com Intuit also has a product : QuickBase - but it is quite pricey and intended for corporate users. Dabble is very reasonably priced and pretty cool. When using a db service you are in their sandbox and must live with the features and look they offer; the redesign using Dabble is a bit of work & learning and there is definitely missing many features one takes for granted in Access.

Option 5 is to have remote users get into a single Access db using a commercial VPN service such as www.GoToMyPC.com. This will also have a monthly fee. The PC must always be on for the user. A bit of a latency/lag experience. Only one user can log on at a time...and, importantly, the log on user will have the ability to see everything on the PC - not just the Access application....

No right or wrong per se – just right or wrong for one’s situation…finding the right tool for the job…or redefining the job for the available tool…..

[cowboy]

What do you mean by "then they should all be linked to the same Back End file."?

What I have is a database that I have copied to several folders on the same network server. Each database is assigned data from a different job site. The reason they are split is so if I am working on job site A another worker can work on job site B. If there is another way to do this and you can create what you have called "Back End file" that everyone could access at the same time I would be very interested on how to do that. Thank you for your help and explanations.

[NTC]

definitely invest in a good text book like Inside Out for your version. You can find it on Amazon or any big box book store..

the standard implementation of a multiuser Access is db is split with a common Back End (BE) shared among all users that have a Front End (FE) on their desktop.

all the table data should be merged so there is only a single set of tables - - and to differentiate projects just be sure there is a unique project ID with each record....

[cowboy]

I talked with my boss and he likes the idea and is willing to invest in the book and x number of work hours to learn how be more proficient with Microsoft Access.

I have 2003 SP3

the book you recommend is:

http://www.booksamillion.com/product/9780735615137

?? I just want to make sure this is a good one before we buy it and it is the one you are talking about. Thanks for your help.

[NTC]

yes - that is a good book