Split Database Concept

[JithuAccess]

Hello Guys,

I would like to clarify whether my understanding about Split Database is correct or Not.

I have a Database in my Network Drive and there are 20 Users are going to Access this Same Database. This may cause Access Database to be slow and chances to get my Database get corrupt. To Avoid this I have to use Split Database Method.

For this I have Split my Database which is currently located in the Netwrok Drive (e.g. "M:\Master Database\All Programs.Accdb") to a New Folder in "M:\Split Database".

Now there is a Back End Database in my New Folder "M:\Split Database" as "All Programs_be.Accdb"

Now whatever the Data I enter in the Access Database which is located in "M:\Master Database\All Programs.Accdb" will be copy in the Database "M:\Split Database\All Programs_be.Accdb"

Then, I have to copy my Friend End Database which is Located in "M:\Master Database\All Programs.Accdb" to all the 20 User's computers. They will be getting a Local copy of the Database and whatever the data they are entering will be update in the Back End Database of "M:\Split Database\All Programs_be.Accdb".

If I have to add a new Table or a new Form, I have to do this in the Access Database which is located in "M:\Master Database\All Programs.Accdb". After Adding or Editing the Tables and Forms, I need to again Split the Database and copy the Updated Access Database "M:\Master Database\All Programs.Accdb" to my 20 User's Computer.

This is what I understand by Split Database.

Could you guys kindly let me know whether this is correct?

Thanks

 

[plog]

If I have to add a new Table or a new Form, I have to do this in the Access Database which is located in "M:\Master Database\All Programs.Accdb". After Adding or Editing the Tables and Forms, I need to again Split the Database and copy the Updated Access Database "M:\Master Database\All Programs.Accdb" to my 20 User's Computer.

Just to be clear, let's agree on terms:

BE - back end database--there is one instance of this and it holds all the tables of your database--no other objects.
FE - front end database--this has as many instances as you have users, it holds linked table connections to the BE and all the Forms/Queries/Reports/Modules

When you want to add a new table you add it to the BE, then you update the FE with a link to it and distribute the new FE to everyone. When you want to add a new Query/Form/Report/Module you only add it to the FE and distribute the new FE to everyone. There is never a need to "re-split" anything because you just add the new object to the appropriate database.

Lastly, I would suggest not distributing the FE files. Instead let them live on the network and provide users a shortcut to them. That way you simply replace the FE databases and the shortcuts still point to the correct file on the network even though its been updated. That way there's no need to move the FE files to the users computers which gets tricky.

 

[JithuAccess]

Just to be clear, let's agree on terms:

BE - back end database--there is one instance of this and it holds all the tables of your database--no other objects.
FE - front end database--this has as many instances as you have users, it holds linked table connections to the BE and all the Forms/Queries/Reports/Modules

When you want to add a new table you add it to the BE, then you update the FE with a link to it and distribute the new FE to everyone. When you want to add a new Query/Form/Report/Module you only add it to the FE and distribute the new FE to everyone. There is never a need to "re-split" anything because you just add the new object to the appropriate database.

Thanks a lot

 

[plog]

I added a paragraph to my response after you replied--just making sure you see it.

 

[theDBguy]

Also, make sure all users have the same M:\ drive mapped to the same network share or folder.

 

[JithuAccess]

I added a paragraph to my response after you replied--just making sure you see it.

So if there are 20 users, I have to create 20 copies of the Front Ends in the Network Folder?

Thanks

 

[theDBguy]

So if there are 20 users, I have to create 20 copies of the Front Ends in the Network Folder?

Thanks

No. You only need one copy of the FE in the network folder. You just tell all 20 users to download a copy of it onto their Desktop.

 

[isladogs]

I disagree with one part of Plog's answer.
Each user should have their own copy of the front end on their own computer ...not run the FE from a network location.
Whilst that makes distributing FE updates slightly harder, the benefits in terms of performance improvements far outweigh that.
In addition there is less risk of the FE becoming corrupted

 

[JithuAccess]

No. You only need one copy of the FE in the network folder. You just tell all 20 users to download a copy of it onto their Desktop.

Sorry I understand this Incorrectly.

So the users will be downloading their copy of the database in their Desktop, for Example, and when they do the Data Entry, these Data will be saved in the BE version of the Access which is located in the Network Folder right?

Thanks,

 

[moke123]

Some developers use a front end updater which downloads a new copy of the front end each time a user launches the program. This ensures they are always using the most current front end and makes distribution easier as you only replace the new frontend file to be downloaded on the server.

BTW, dont ever work on or C&R the front or back ends while they are on the server. Copy them to your Dev. Machine, do what you need and place them back on the server.

 

[theDBguy]

Sorry I understand this Incorrectly.

So the users will be downloading their copy of the database in their Desktop, for Example, and when they do the Data Entry, these Data will be saved in the BE version of the Access which is located in the Network Folder right?

Thanks,

Yes, provided, like I said earlier, everyone is mapped to the same M:\ drive. To avoid that problem, you can use the UNC path to the server for linking your BE tables to your FE.

 

[moke123]

There's a lot of info on split databases here . . .https://www.fmsinc.com/MicrosoftAccess/DatabaseSplitter/index.html

 

[JithuAccess]

There's a lot of info on split databases here . . .https://www.fmsinc.com/MicrosoftAccess/DatabaseSplitter/index.html

Thanks a lot

 

[The_Doc_Man]

I'm with Colin here. Plog's comments about FE location also raised a flag with me.

The reason that you distribute 20 copies to 20 user machines is FILE LOCKING. I'll try to be brief. (But those who know me will probably roll their eyes heavenward and mutter "here we go again...")

When you do anything that opens a file, the first thing that Windows does is check for file locks. Access is still trying to open the file but it has to get through Windows to do it. File rule #1 is that the system that holds the file manages its locks. If you have a private copy of the file on your (local) machine then lock management is local. The file AND ITS FOLDER will be locked. (Probably with a shared lock even for a local machine.) However, because each user has their own local copy of the FE file, none of them touch YOUR copy of the FE file. Therefore, you have ZERO lock contention (or lock collisions). No collision? No chance of running into a lock conflict that would trip up the opening process. Further, the lock data structures are local to the machine on which the FE is running. There is no network involvement.

File rule #2 is that ownership counts big-time. When you copy that FE to the local machine, YOU OWN IT. Further, it is in YOUR folder. In the process of file access arbitration (little "a" as opposed to Access activity), ownership is everything. File permissions are local to the machine on which the FE resides AND they are favorable 99+% of the time.

Move the FE to a folder on a shared machine and LOTS of things change. Even if you have a set-aside folder for your actions, the shared machine is now the arbiter of locks and permissions. Things that operated at nanosecond speeds on a local machine are 500 to 1000 times slower even on a really fast network. The clogging effect of having traffic to that machine to arbitrate lock and permissions access in the presence of a mixed bag of users would be about as pleasant as a mixed bag of cats (that you just whacked).

Where physically possible, ALWAYS physically distribute the FE files that are part of a split pair. Now, you CAN have a central repository that holds the "public copy" of the FE file and use one of the many methods found in this forum to automatically update the FE via a batch job that copies the "public copy" to your local folder and then launches it. So there is nothing wrong with having the FE file in a shared location. But you should NEVER actually allow people to RUN the "public copy" in its public location. That way leads to lock collisions with a good probability of causing a user's session to terminate due to incompatible locking.

 

[The_Doc_Man]

when they do the Data Entry, these Data will be saved in the BE version of the Access which is located in the Network Folder right?

No. The FE maps to the BE. It acts as a go-between, taking the user's data and storing it in the tables, which are in the BE file. The FE file (in the "pure" case) NEVER stores anything locally. There ARE exceptions, but general user data entry is not one of them.

 

[Umpire]

Jumping in here with some questions/ need for clarification of my own. (Noob user. please be gentle.)
The back end is ONLY tables? And the front end is everything else?
And if I want to make a change, I copy the BE file to my PC, make the change, then copy back to the server, overwriting the file I originally copied.
If I want to change a form or the appearance of my switchboard I only have to change the front end. Then get a new copy of the Front end to the users either by giving them a copy/ using the .bat file method or having them down load the new file.

But don't I need both the front and back end together to make changes to the front end? I thought the entire point of the FE was so that it could not be changed by users. How does access know I am not the user but the one writing the FE?
This is the part that confuses me the most, doing updates after splitting.

 

[isladogs]

The easiest way is to assign user levels where the program admin/developer can do things but standard users have more limited access.

Also the developer will always work with a 'dummy' BE file -NEVER with live data. Changes are normally only made to the BE when the developer is ready to upload a new version of the FE

 

[theDBguy]

The back end is ONLY tables? And the front end is everything else?

Short answer is "yes!"

And if I want to make a change, I copy the BE file to my PC, make the change, then copy back to the server, overwriting the file I originally copied.

If you're talking about design changes (e.g. table structure, such as adding new fields), then the short answer is "No!" You don't want to overwrite any new data with the old ones. Long answer is you make sure no other users are using the BE. You then open it in Exclusive mode. You modify the BE design from the network, so that when you're done, all the users get to use the same/new BE. You can of course download a copy of the BE and make the changes locally. However, make sure no one will be using the database BE while you are making changes locally. This is to preserve the same data from your copy to the one you'll eventually put back on the server.

But don't I need both the front and back end together to make changes to the front end?

This is what @isladogs was saying. You keep a local copy of the BE for your FE development, so that, any changes you make to the data won't affect the real data seen by the users.

I thought the entire point of the FE was so that it could not be changed by users.

If you don't want regular users making design changes to your FE, then you can give them a ACCDE version of it. You keep the master ACCDB version to make your changes.

 

[Umpire]

If you don't want regular users making design changes to your FE, then you can give them a ACCDE version of it. You keep the master ACCDB version to make your changes.

OK I guess this was the key point I was missing. Time to research ACCDB vs ACCDE. I think I know the difference. which usually means I just think I do and really don't.

And your point about "overwriting" vs changing the BE directly is noted. I work in a very small office where max users are 5 at any one time. And It would be rare for more than 2 users at a time. I have a tendency to overlook some things as a result.

So in summary, I split the database and put the BE on the server and a copy of the ACCDE goes to each user. I then take a copy of the BE and keep it on my PC and use ACCDB for future changes etc. to the FE. When my change is done, I make a new ACCDE and distribute to the users. Sound right?

 

[isladogs]

Yes ... plus make any design changes to the BE when you are the only user (open exclusive)