Splitting an Access Database into Front-End and Back-End

[jjc9809]

Hello Everyone,

I have a several questions about splitting an Access database into a Front-End and Back-End.

Question1. Since the Back-End database is on the network server and linked back to the Front-End database on the Local C Drive Folder on a persons PC. What is the security issues that come up with this use in a multi_user environment. We will have about 300 users using the database and how do they know they are pulling up the most updated backed up version?

Question 2. Will the split database increase the speed? We will have persons opening and using in all parts of Alabama, North and South.


Question 3. Will the split database help us with the problem of some inviduals getting locked out of the database and unable to log back in?. Some people are blocked by other users in other parts of Alabama. The persons can go out and log back in and be back into the application.

Questions 4. Does having the FE on the Local C Drive folder on everyone's machine work best?

Question 5. How does updating the database work? Do updates occur in the FE only?

Question 6. How can I place the FE on ervyone's Local C Drive Folder? Can the user just copy and paste the most updated FE on the server and place on their Local C Drive?

[GolferGuy]

1. I've never dealt with this large of a user base. My experience is about 5 or 6 people, all in one location in a small, privately held company, so security was not a consideration. But 300 users makes me think that an Access database Back-End (BE) really should be a SQL Server BE (Back-End). Once you get over 10 users, you need to watch the response time closely. Over 20 users, and if there is very much time spent by these users on the database, it will start slowing down very quickly. But 300 users, it should be one or two quick questions a day, otherwise, Access will not be able to handle the load.

2. The split database will not, by itself increase the speed. What it does is allows easy path for maintenance to the FE database. And allows the data to be backed up every day without having all the bytes that are used in the procedures, forms, reports, VBA, queries, etc. having to be backed up with the data. Any increase in speed is just a nice by-product of the split database.

3. With the FE database on everyone's personal C: drive, you should have no problems with lockouts. You may run into problems with more than one person trying to access the same record, at which point you will have the second user having to wait for that record.

4. Yes, everyone should have the FE database on their own C: drive.

5. The only reason to ever update/do maintenance on, the BE database is for database design changes. Adding a field to a table for example. That really should not happen very often. When it does happen, you have to let all the users know that the system will be down at a certain time for maintenance. You can not update the BE database while people are using it.

6. It would be best to write a procedure to copy the new FE from the server onto the local C: drive. What I did was to put a table in the BE and a match table in the FE databases for version control. When the FE database opened, it would check to be sure that the local (FE) table, latest version number matched the latest version number in the linked version table (from the BE database). When these two latest version records did not match, the FE database opened another Access database that took over the situation and copied the new FE database from the server location unto the users local C: drive into the predetermined folder on the C: drive, then finally opened this new version of the FE from the C: drive. This type of procedure allows you to just do the required maintenance to the Master copy of the FE database, update the version table in the Master FE database, and place this new Master FE database onto the server. Once that is done, then update the version table in the BE database. That action will then cause each local FE database to be refreshed when each user opens the database. Very nice, automatic, refresh for every user.

Finally: Again, I would highly suggest you consider converting your BE database to a SQL Server database because of the size of the user base.