How would you maintain your database in a network environment?

[HockeyFan]

My Access database is on a folder on one of the network drives. Here's the problem. I have made an .mde out of my database so people can't get into the code or mess things up.
However, when my user requests a change with the database, I have to use the .dbf file from my hard drive, and I loose all of the data input they had done while using the .mde.
If you are in a network environment, please let me know how you handle this situation. Thanks in advance.

 

[pbrown77]

I split the DB into a BE and FE. With the BE on the server and the FE on each persons computer or local drive. Using batch files via e-mail so that only those who receive the e-mail can open the batch file which will automatically copy the FE from the server to their computer. Then when an update is needed, I simply update the FE on the server.

Here is where the options are great. You can either send them another e-mail with batch file to update their FE, or as I do use an updater database.

 

[HockeyFan]

That's very interesting how you seperate the db into a front end and back end. Is this something you just did on your own, or is there documentation or a post somewhere that I can follow to try this as well?

 

[pbrown77]

As for the initial split, go to tools - Database Utilities - Database splitter.

It is advisable not to have any "spaces" in the name of the front end. Not even "-" or "_". That is if you are going to use an updater DB.

Personally, I set up a folders on the network
1) "BEs" for all the back ends
2) "FEs" for all the front ends
Under FEs
A folder "Updates" which I kept the mdb file of the fe that I update. Once the file has been updated, I change it into a mde file directly under "FEs". This way the file the users copy as their FE is a mde file. (I simply overwrite the existing mde file each time I update the FE under "Updates")

 

[HockeyFan]

I looked up 'help' in access, and read about splitting a database. the following statement is from the help file.

"Split an existing Access database between its data and its objects. This procedure splits a Microsoft Access database into two files: one that contains the tables and one that contains the queries, forms, reports, macros, modules, and shortcuts to data access pages. This way, users who need to access the data can customize their own forms, reports, pages, and other objects while maintaining a single source of data on the network."

Is this what you did? Split the tables and the other stuff? Your description sounded a little bit different than this.

 

[pbrown77]

Yes. The tables stay on the network so that if you need to update any forms the users do not lose the data they have already entered.

However, since I change the fe to a mde file the users can not change the design, only use what is given.

The difference is the distribution method. Since I do not want to have to go to each persons PC to save the FE to their computer (for use it is a local network drive) I use a batch file to do the work for me.

break down:
1)Split the DB
A) BE on Server
B) FE (Mdb) file in a file location only I know about
C) FE (Mde) file in a location that I can copy over
2)Via batch file
A) Send users a copy of the mde file
B) OPTION - also send a updater DB (separate DB) that will check the version on their PC vs the mde version on the network. If there is a different version on the network it will automatically copy the newer version onto their PC
C) If option is used, include a shortcut to the updater DB (this will ultimately open their FE) after checking versions.

This way, I update the mdb then via tools save it as a mde file that overwrites the mde file on the network. Then as the users opens the database the version is checked and if need be, the newer version is copied then opened. This ensures the users are always using the most up-to-date version. (As everyone knows, forms and reports are always changed/added) and this way, I can work on them in peace and once completed everyone can have them.

 

[HockeyFan]

Ok, sounds like i'll be busy for a while. thanks for the advise. I appreciate you help.

 

[pbrown77]

If need be, just give a post or reply.

Good luck!