Access 2007 Backend for multiple locations - shared vs isolated database approach

Question

I'm building a database scheme for two different headquarters. Each location uses the same data-scheme, but has its own data. Both will have access to the same location, a shared network folder where the corresponding access-backend will be placed. Both will also use an equal Front-End, where depending on the user(-account) the specific data will be shown. Each location will have two main tables which will grow by ~4000-5000 records per year.

Most of the time each location only uses its own data, but once every week or so a third party will access some aggregated data from both locations.

In my opinion there are two possibilities to organize the data: a shared approach, where both locations use & work with the same tables, where a field indicates whether the current data belongs to location 1 or 2. A huge problem I see is the amount of data which would be transferred through the network (roughly doubled), because (as far as I know) access runs the select statements locally, after transferring the data. (I stand corrected). Besides that an additional query would be neccessary for each access.

The alternative would be to create a second set of tables for the second location and keep them 'separated' (e.g. renaming them to loc1_tablex and/or create a second backend-file), which also would make a backup easy.

I would prefer the second approach, because I can't really see any downside to it. It would be faster and in my opinion cleaner.

Did I miss anything importing or would this approach be reasonable?

Edit:

After discussing the topic further (we also got some new information from our client), we decided to store the data in one backend and in the same tables. The table structure is fully equal and we are using a location id to control what data belongs to which location (first approach). Ty for shedding some light on the access backend, I had a lack of information there and learned a lot!

Answer 1

As noted, with indexing, Access will not pull down more records when they are restricted to one location or the other.

However, I think a WAN is going to be used here, then I would likely consider the use of SQL server since you can then aggregate data and this occurs server side – going to be much faster then JET for these types of scenarios, assuming you need such scenarios.

The other possibility here is to consider using Access 2010 and office 365. In this case, you keep your regular Access front end, but move the data up to office 365.

The beauty of this system is you have off-line and a local data store. This means that reporting and read only data on each copy of the access database is going be very high speed and occur at local JET database speed. Any new data entered into the tables on the server will AUTOMATICALLY be synchronized down to those clients that everyone is using. As noted because of this automatic offline mode, then the System Works really great and even continues to work if your Internet connection is broken or interrupted for periods of time.

About the only downside to moving your data up to office 365, is there are a few extra steps you have to do to ensure referential integrity get "correctly" moved up to office 365.

In a nutshell this extra step means you break (remove) the relations between the tables, and then use the new relationships wizard to setup the relationships simple be compatible with office 365.

The beauty of this system is it starts for low cost of $6 per month. And you can hang lots of users off that one account. Not only that, but such a setup would also allow you to publish and build web forms for data entry by everybody, and you could in theory not even have to distribute the access forms, but use web ones. then with the data in it. As such those forms and data could be viewed on something like a smart phone or a iPad if you did this.

Here's a step by step the view that shows how I'd break the relationships between tables, then reconnect them in the correct way for utilizing on office 365:

http://www.youtube.com/watch?v=3wdjYIby_b0&fmt=22&hd=1

Last but not least, you can upload the data with refferential integrity intact using office 2010, and therefore in theory then link to these tables using access 2007 front ends. However access 2007 does not have the automatic offline mode, and performance will not be near close to what you get when using access 2010 with this setup.

So I think office 365 is a potential ideal solution here since you'll get real time updating of any front end out in the field.

While the above video was about sending up data, and using regular Access front end, you can build and use web forms in Access on office 365 as this video shows:

http://www.youtube.com/watch?v=AU4mH0jPntI

No activeX or silverlight is required here.