More info on Database Splitting

[ekta]

Hi All:

I have been reading a lot of forums on Database splitting. I get the idea but I still have few questions in my mind. I am doing this for the first time so bear with me if I ask silly questions.

1. After splitting the database into FE and BE I will place BE on the server and copy of FE on individual user machine. When I do that how will I link the FE to the BE? Does the wizard do that automatically?

2. After some time lets say I have to make some changes to the form. How am I going to update it? Do I need to keep a copy of the FE on my machine as well. I make the changes to FE on my machine and then update it on every machine by copying it on their machine again???

3. Lets say 2 users are editing the same record at the same time.
There are about 5-6 users who will be using this database but still it can happen. How will this work? Who's information gets updated?

4. What are the other issues that I need to keep in my mind?

Thanx in advance
Ekta

 

[ekta]

Hi Pat:

Thanx for replying. This will definitely help me.
I have a few more questions

1. After placing BE on server and linking it to the FE on my local machine, do I simply copy the FE on my machine and then place it on the users machine. Will I need to re-link the FE on users machine to the BE?

2. Could you please explain a little more on "production" directory and source control product. You said that for making changes I need to copy the current version to my local machine. Does that mean that I just copy the FE or also the BE. Or I just copy current FE and then link it to BE on server to make changes. This is a little unclear to me.

3. I simply want to lock the record if one user is editing a record and the second user tries to edit the same record at the same time.

4. I wil remeber that.

Thanx again for your valuable advice
Ekta

 

[Autoeng]

Hi ekta:

1) Yes, basically. The easiest way to distribute a FE is to write a batch file in Notepad to copy the FE to the users machine every time they open the db. That way they always have the latest copy of the FE. Here's an example

md "c:\my documents" (I do this as I have Win NT and Win2K on users machine and directory structure is different)
copy x:\networkdirectory\mydb.mdb "c:\my documents\*.*" (copies the FE to the C:\my documents directory)
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "x:\networkdirectory\mydb.mdb" /WRKGRP "x:\networkdirectory\mysecurity.mdw" (opens the secured db with the correct workgroup)
You can send this batch file to your users via email and they copy to desktop. Every time they open the db they use this batch file.

2) There are programs available which allow you to track the version of a file that you are using and to check out the file for editing. Instead I do the following; I have the shortcut on my desktop like the users so I can check if changes I have made will function correctly for the users and I also have a copy of the FE in another directory (my working copy). I use a shortcut to open the working copy which is linked to the BE on the server, and make my changes to the FE. I have a third copy of the db on my machine which is in a directory structured by revisions I have made. I copy the updated FE to it's directory in the revision direcotry and to the server, replacing the old revision there. Now when a user opens the db they get the latest copy, I have a working copy and all previous revisions on my machine. No need to update the links as you have the same BE link because you are working from you machine to the BE on the network, same as everyone else. Now, to make changes to the BE you will either need to request everyone get out and stay out until you are done(make a backup first) or use VBA to kick them out and keep them out (my choice). There is a good example of this on www.rogersaccesslibrary.com under logusersout.

3) Set your record locking under Tools-Options-Advanced-Default Record Locking to Edited Record. This will keep a user from opening a record that another user already has open.

 

[ekta]

Hi Autoeng:

This certainly cleared a lot of my doubts.
1. I am not sure if I fully understood the batch file procedure. Althouh, you did a good job of explaining. It's probably me. Is there no other way of doing it?Can't I manually copy the FE on every users machine. I know it'll be more work but I can do it. I don't want to do anything that I dont fully understand.

2. You said

"I copy the updated FE to it's directory in the revision direcotry and to the server, replacing the old revision there."

Don't the updated FE go on the users local machine or you have both FE and BE on the server.

One last question. After the FE and BE are setup what is the right way of backing up the database?

Thanx so much for your advise.
Ekta

 

[Autoeng]

ekta:

You will quickly tire of having to update the FE to each users machine, use the batch file. A batch file executes DOS commands. Open Notepad and copy my commands into it. Change the commands to reflect your information and save the file. Rename the file ending to .bat.

I load both the FE and BE on the server as the user downloads the FE everytime they use the db.

Backing up the db? Your choice here. In my case I have my working copy, the previous revisons and the server (user) copy of the FE and I run a scheduled task every night to backup the BE.

 

[ekta]

Thanx Autoeng. I will use the batch file.
Is it better to put FE on the server or on users local machine. I read that the database gets slow if you place the FE on the server because all your queries and other stuff takes time to get through the server.

I think now I am ready to split my database.

Thanx again

 

[Autoeng]

The FE will be on the server only to provide a common place for everyone to download it from when the execute the batch file. Then it will be on the users machine.

 

[Autoeng]

I won't argue with Pat as she knows a heck of a lot more than I do but the reason I chose to download the FE everytime a user opens the db is that it's not a big file in my case, I don't have any table up keep and I didn't have to automate the download process. I say you should go with whatever method your comfortable with and causes you the least amount of headache.

 

[thart21]

Batch file not working

I have created the .bat file in notepad and placed both the FE and BE on my shared drive. I emailed the .bat file to a co-worker who could open the database menu, but, when she tried to open a form, got the message that it was on my drive, not the shared drive. Does anyone have any ideas as to why this did not work? Could my IT department possibly have some type of block on sharing databases?

Here is what I have in my .bat file:

md "c:\my documents" copy v:\Project\Materials\PurchSQE\Training\MaterialsTraining.mdb "c:\my documents\*.*"

"c:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "v:\Project\Materials\PurchSQE\Training\MaterialsTraining.mdb"
/WRKGRP "v:\Project\Materials\PurchSQE\Training\Secured.mdw"

Thanks in advance for your help,

Toni

 

[Autoeng]

You need to set up your table links so that they point to the BE on the server, not on your harddrive. This way the FE alwas points to the correct location of the BE (server). When you ope the Linked Table Manager in the FE you will see that it is pointing to your harddrive and not the server BE. The easiest way to do this is open the db that resides on your pc, reset the BE links to the server location, copy the FE from your pc to overwrite the one on the server.

 

[thart21]

.bat file

Autoeng,

Thanks a million! I was just missing this step:

"copy the FE from your pc to overwrite the one on the server"

Now it works! Don't know what I would do without everyone on these forums-probably be looking for a job!


Thanks again,

Toni

 

[Autoeng]

You and me both. Glad I could help.