Access 2007 Backend for Multiple Locations - A General Approach to an Isolated Database

Question

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

In most cases, each place uses only its own data, but once a week or so, a third party will access some aggregated data from both places.

In my opinion, there are two possibilities for organizing data: a general approach where both places use and work with the same tables, where the field indicates whether the current data belongs to location 1 or 2. The huge problem that I see is the volume data that will be transmitted over the network (about twice), because (as far as I know) access is performed by the select operators locally, after data transfer. (I stand fixed). In addition, an additional request is required for each access.

An alternative would be to create a second set of tables for the second location and leave them “divided” (for example, rename them to loc1_tablex and / or create a second backend file), which will also facilitate backups.

I would prefer the second approach, because I do not see any shortcomings in this. It would be faster and, in my opinion, cleaner.

Am I missing something importing or would this approach be reasonable?

Edit:

After discussing the topic (we also received new information from our client), we decided to save the data in one backend and in the same tables. The structure of the table is completely equal, and we use the location identifier to control what data refers to a particular location (first approach). Tai to shed light on the access backend, I had a lack of information and learned a lot!

Answer 1

As already noted, when indexing, Access will not display more records if they are limited to one location or another.

However, I think the WAN will be used here, then I will most likely consider using a SQL server, since you can then aggregate the data, and this happens on the server side - it will be much faster than JET for these types of scenarios, assuming that you need such scenarios.

Another possibility here is to consider using Access 2010 and Office 365. In this case, you keep your normal access interface, but move the data to Office 365.

The beauty of this system is its autonomous and local data storage. This means that read-only reports and data on each copy of the access database will be very high and will occur at the local speed of the JET database. Any new data entered into the tables on the server will be automatically synchronized with those clients that everyone uses. As noted in connection with this automatic autonomous mode, the system works very well and even continues to work if your Internet connection is interrupted or interrupted for periods of time.

About the only drawback of moving your data to Office 365, are there a few additional steps that you need to take to ensure that link integrity is correctly transferred to Office 365.

In a nutshell, this extra step means that you break (delete) the relationship between the tables, and then use the New Relationship Wizard to set up relationships that are simply compatible with 365 office.

The beauty of this system starts at a low cost of $ 6 per month. And you can hang many users with this account. Not only that, but such a setting will also allow you to publish and create web forms for entering data by everyone, and theoretically you should not even distribute access forms, but use web versions. then with the data in it. Thus, these forms and data could be seen on a smartphone or iPad, if you did.

Here's a step-by-step view showing how I am breaking relationships between tables and then reconnecting them correctly for use in Office 365:

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

Last but not least, you can load data with consistent integrity using office 2010, and therefore theoretically link to these tables using the front ends of access 2007. However, access 2007 does not have automatic offline mode and performance will not be close to that what you get when using access 2010 with this setting.

Therefore, I think Office 365 is a potential ideal solution here, since you will receive a real-time update of any external interface in the field.

While the above video was about sending data and using regular Front access, you can create and use web forms in Access at Office 365, as shown in this video:

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

ActiveX or silverlight is not required here.