Split the DB, how do I deploy the "Front-end"?

[kaleolani65]

I have been having an ongoing problem with a DB at work concerning people getting "locked out".

Access warnings relating to "exclusive" mode

The research I have done and the replies received suggest I split the DB (front-end/back-end).

A working copy of the DB was split and I can see how the DB (back-end) is being updated.

Being such an Access noob (who did not actually build the DB)
I don't know what people mean exactly when they say to give each person a copy of the forms on their desktops. Actually, I know what they mean I just don't know how to do it.

Question, if each person has a copy of the DB on their workstations does the back-end only get updated when they hit save?

Is the back-end only really current when all people have logged out of that DB?

Is there any way for someone to get real time views of the front-end (at a particular workstation) from the back-end?

Thanks for the help

Chuck

[MMcCarthy]

Chuck

Everyone gets a copy of the frontend only. There should only be one backend on a server which all users have access to. The data should be updated in realtime. In the backend go to Tools - options - Advanced and make sure that the Default Open Mode is set to Shared and Default Record Locking is set to Edited record. This means that users won't be able to edit the same record at the same time as this could cause a problem.

Mary

[kaleolani65]

Chuck

Everyone gets a copy of the frontend only. There should only be one backend on a server which all users have access to. The data should be updated in realtime. In the backend go to Tools - options - Advanced and make sure that the Default Open Mode is set to Shared and Default Record Locking is set to Edited record. This means that users won't be able to edit the same record at the same time as this could cause a problem.

Mary

Thanks for the response Mary, I will definitely change the default open mode

1.If the DB lies on shared drive what exactly do I give to them (front-end wise)

2.When you say everyone gets a copy does that mean I have to put a local copy onto their actual workstations?

3.If I put a local copy onto their workstations how does the DB know to update to the back-end.

Bear with me if I am asking noob questions, I don't know much about access and having just started here I know even less about their file system structure.

I do know that they are running a distributed file system, the forms contained within the DB are accessed through a shared drive on the server.

This whole problem arose because people are getting "locked out" of the DB, in the mornings if the first person opens the DB then noone else can until he/she logs out. Also they are getting all kinds of "exclusive" "locked by computerXX" errors.

Also, by the way it looks the DB is actually a collection of forms which they enter info into, but I can't really see the difference between the DB and a spreadsheet, they don't run queries, there is no code associated with any of the forms, and the forms are'nt even linked???

Anyways, enough ranting. Any help on those above questions would be greatly appreciated...

Chuck

[MMcCarthy]

Sorry for the delay in replying, I was tied up all day in Admin duties. Too many hats. ;)

Thanks for the response Mary, I will definitely change the default open mode

1.If the DB lies on shared drive what exactly do I give to them (front-end wise)

Think of it this way. You start out with one database foo.mdb. Back it up before doing anything. Now copy all the tables into a new database and call it foo_BE.mdb and save it in its final resting place on the shared drive. Then delete all the tables from foo.mdb and go to file - get external data - Link files and a file open window will open. Select the foo_BE.mdb file from it's position on the shared drive and a wizard will open. This will allow you to select all the tables and link them into foo.mdb. You now have a backend and a frontend. Now backup the frontend file foo.mdb (this is very important as the file we are about to create can't be edited).

Now open foo.mdb and make sure all your startup options are set to how you want them. Be sure to Uncheck the Show database window option.
Go to Tools - options and click on the advanced tab. Set the Default open mode to shared and the Default Record locking to edited record. This will resolve the errors you are describing.

Once you are happy go to Tools - Database Utilities - make an mde. This will create a foo.mde file which will be uneditable. This is what you issue to users.

2.When you say everyone gets a copy does that mean I have to put a local copy onto their actual workstations?

You don't have to you can just have one copy on the shared drive.

3.If I put a local copy onto their workstations how does the DB know to update to the back-end.

Because you created a link to the backend on a specific path so as long as foo_BE.mdb doesn't move the frontend will be able to find it.

Also, by the way it looks the DB is actually a collection of forms which they enter info into, but I can't really see the difference between the DB and a spreadsheet, they don't run queries, there is no code associated with any of the forms, and the forms are'nt even linked???

LOL - honestly can't comment without seeing it but I've seen some interesting designs in my time.

Mary

[kaleolani65]

Sorry for the delay in replying, I was tied up all day in Admin duties. Too many hats. ;)



Think of it this way. You start out with one database foo.mdb. Back it up before doing anything. Now copy all the tables into a new database and call it foo_BE.mdb and save it in its final resting place on the shared drive. Then delete all the tables from foo.mdb and go to file - get external data - Link files and a file open window will open. Select the foo_BE.mdb file from it's position on the shared drive and a wizard will open. This will allow you to select all the tables and link them into foo.mdb. You now have a backend and a frontend. Now backup the frontend file foo.mdb (this is very important as the file we are about to create can't be edited).

Now open foo.mdb and make sure all your startup options are set to how you want them. Be sure to Uncheck the Show database window option.
Go to Tools - options and click on the advanced tab. Set the Default open mode to shared and the Default Record locking to edited record. This will resolve the errors you are describing.

Once you are happy go to Tools - Database Utilities - make an mde. This will create a foo.mde file which will be uneditable. This is what you issue to users.



You don't have to you can just have one copy on the shared drive.



Because you created a link to the backend on a specific path so as long as foo_BE.mdb doesn't move the frontend will be able to find it.



LOL - honestly can't comment without seeing it but I've seen some interesting designs in my time.

Mary

Mary,

I think I understand what you are saying but in this case does'nt the front-end have to be editable so that the users can input info into the DB?

The DB is edited by a few users here and accessed by others around the southwest only for viewing.

I could make the uneditable version for those users who only need read access to the forms, but how do I set up the editable side.

[MMcCarthy]

Mary,

I think I understand what you are saying but in this case does'nt the front-end have to be editable so that the users can input info into the DB?

The DB is edited by a few users here and accessed by others around the southwest only for viewing.

I could make the uneditable version for those users who only need read access to the forms, but how do I set up the editable side.

When I say the database will be uneditable I mean the structure of forms, reports, etc. can't be changed. It would affect users ability to use the forms to enter data, etc.

[kaleolani65]

When I say the database will be uneditable I mean the structure of forms, reports, etc. can't be changed. It would affect users ability to use the forms to enter data, etc.

I'm assuming you meant "It would'nt affect users abilities to enter data" in your reply.

If that's the case I will get to work on it like you instructed and cross my fingers.

[MMcCarthy]

I'm assuming you meant "It would'nt affect users abilities to enter data" in your reply.

If that's the case I will get to work on it like you instructed and cross my fingers.

LOL - Sorry about that. You are right of course. I'll have to start reading my own posts.

Mary

[kaleolani65]

Sorry for the delay in replying, I was tied up all day in Admin duties. Too many hats. ;)



Think of it this way. You start out with one database foo.mdb. Back it up before doing anything. Now copy all the tables into a new database and call it foo_BE.mdb and save it in its final resting place on the shared drive. Then delete all the tables from foo.mdb and go to file - get external data - Link files and a file open window will open. Select the foo_BE.mdb file from it's position on the shared drive and a wizard will open. This will allow you to select all the tables and link them into foo.mdb. You now have a backend and a frontend. Now backup the frontend file foo.mdb (this is very important as the file we are about to create can't be edited).

Now open foo.mdb and make sure all your startup options are set to how you want them. Be sure to Uncheck the Show database window option.
Go to Tools - options and click on the advanced tab. Set the Default open mode to shared and the Default Record locking to edited record. This will resolve the errors you are describing.

Once you are happy go to Tools - Database Utilities - make an mde. This will create a foo.mde file which will be uneditable. This is what you issue to users.



You don't have to you can just have one copy on the shared drive.



Because you created a link to the backend on a specific path so as long as foo_BE.mdb doesn't move the frontend will be able to find it.



LOL - honestly can't comment without seeing it but I've seen some interesting designs in my time.

Mary

I made an .mde and set the default open settings to shared.
when I try to open it up on another computer it opens read only and I cannot add any data to it?
Any ideas???

[MMcCarthy]

I made an .mde and set the default open settings to shared.
when I try to open it up on another computer it opens read only and I cannot add any data to it?
Any ideas???

Set the open mode of the backend to shared.