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.

[kaleolani65]

Set the open mode of the backend to shared.

I checked the BE and the FE and both have their default open mode set to shared. I found it through tool-->options-->advanced tab

What do you think?
I know it is something I am missing I just can't see it...

[MMcCarthy]

I checked the BE and the FE and both have their default open mode set to shared. I found it through tool-->options-->advanced tab

What do you think?
I know it is something I am missing I just can't see it...

Check the Record locking is set to Edited Record.

If so it sounds like it might be a network issue.

Mary

[kaleolani65]

Check the Record locking is set to Edited Record.

If so it sounds like it might be a network issue.

Mary

Mary, I set the default record locking to 'edited record' it was 'no locks'
There is also a checked box entitled 'open databases using record-level locking'

I will give it a shot now after changing the default record lock

Thanks,
Chuck

[Denburt]

I have seen similar situations networks can be fun. You stated that you are an access noob but I don't know if you have any programming experience. I have some code for distributing copies and making updates, but you will need to tailor it a little for your purposes.

What I do is set up my Main menu with a version number I use two tables one linked to the backend and one local table in the front end if I update the servers copy with a new version number my clients will see a message with in about 5 min (using the timer on the main menu). I never close the main menu I always hide/unhide it so the timer is always there. When the message pops up they click yes or no and is yes then they get the newer version (code I use creates a VBS script for copying and reopening the new version for you) all completely automated.

I also added a feature so that a new user can click on the front end located on the server and they get a message requesting them to click ok so it will copy to there local hard drive and open from there.

It also adds a shortcut to the desktop btw.

If you feel up to the task let me know and I will be glad to help.

[kaleolani65]

I have seen similar situations networks can be fun. You stated that you are an access noob but I don't know if you have any programming experience. I have some code for distributing copies and making updates, but you will need to tailor it a little for your purposes.

What I do is set up my Main menu with a version number I use two tables one linked to the backend and one local table in the front end if I update the servers copy with a new version number my clients will see a message with in about 5 min (using the timer on the main menu). I never close the main menu I always hide/unhide it so the timer is always there. When the message pops up they click yes or no and is yes then they get the newer version (code I use creates a VBS script for copying and reopening the new version for you) all completely automated.

I also added a feature so that a new user can click on the front end located on the server and they get a message requesting them to click ok so it will copy to there local hard drive and open from there.

It also adds a shortcut to the desktop btw.

If you feel up to the task let me know and I will be glad to help.

Do you mean you just have them grab a copy of the FE from the server everytime they attempt to open the DB, could this fix this "records opening uneditable" problem for me?

I changed the default open modes but no joy...

[kaleolani65]

Check the Record locking is set to Edited Record.

If so it sounds like it might be a network issue.

Mary

Mary what I am doing is:
I changed the default record locking to shared
on another workstation I navigate to the share where the MDE is
I drag a copy onto their desktop
when I open it does not allow me to enter text...

Ideas???

I am fairly sure it is not the network, the problem probably lies between the keyboard and the chair...

[Denburt]

Do you mean you just have them grab a copy of the FE from the server everytime they attempt to open the DB, could this fix this "records opening uneditable" problem for me?

That would probably be an easy solution for you just let them copy the front end to their hard drive and then they will have a copy.

I was simply stating that I use VBA to automate the copying process. I got tired of people calling me and saying they copied it but lost it. :)

[MMcCarthy]

Mary what I am doing is:
I changed the default record locking to shared
on another workstation I navigate to the share where the MDE is
I drag a copy onto their desktop
when I open it does not allow me to enter text...

Ideas???

I am fairly sure it is not the network, the problem probably lies between the keyboard and the chair...

Just our of curiousity don't drag a copy just create a shortcut and see if you have the same problem.

Mary

[kaleolani65]

Just our of curiousity don't drag a copy just create a shortcut and see if you have the same problem.

Mary

I made a shortcut and stuck it on the desktop, when I clicked it I got a dialog box saying that 'DistributedFE.mde is a read only file you will not be able to edit blah blah blah'

[MMcCarthy]

I made a shortcut and stuck it on the desktop, when I clicked it I got a dialog box saying that 'DistributedFE.mde is a read only file you will not be able to edit blah blah blah'

OK copy it down and right click to get the file properties and see if the file is set to read only.

You did say you could open and edit the file directly on the network didn't you?

[kaleolani65]

OK copy it down and right click to get the file properties and see if the file is set to read only.

You did say you could open and edit the file directly on the network didn't you?

This is where I stand,

-When I open up the .mde from someone else's workstation I cannot edit anything, it gives me the 'read only' dialog box

-If I open up the .mde from anywhere there is a 'record locking information' file created in the folder (is this a .ldb?)

When I placed the shortcut and/or the .mde on their desktop the properties window check box for read-only is NOT checked

I have a feeling that I may have messed something up when I converted the DB

[MMcCarthy]

This is where I stand,

-When I open up the .mde from someone else's workstation I cannot edit anything, it gives me the 'read only' dialog box

-If I open up the .mde from anywhere there is a 'record locking information' file created in the folder (is this a .ldb?)

When I placed the shortcut and/or the .mde on their desktop the properties window check box for read-only is NOT checked

I have a feeling that I may have messed something up when I converted the DB

I don't really understand this. If the .ldb file is open in a multi user database this shouldn't stop anyone else accessing the file as long as the Default Open Mode is set to shared.

The only thing I can think of is that this setting is on Access rather than the database. Are we dealing with different installations of Access.

Open Access on the machine you are copying to and see if the setting is Shared or Exclusive.

[kaleolani65]

I don't really understand this. If the .ldb file is open in a multi user database this shouldn't stop anyone else accessing the file as long as the Default Open Mode is set to shared.

The only thing I can think of is that this setting is on Access rather than the database. Are we dealing with different installations of Access.

Open Access on the machine you are copying to and see if the setting is Shared or Exclusive.

Everything is set to shared, I don't know anymore this is so frustrating...Arrrgh

[MMcCarthy]

Everything is set to shared, I don't know anymore this is so frustrating...Arrrgh

I'm afraid I'm out of ideas. It still sounds like a network issue to me.

Mary

[kaleolani65]

I'm afraid I'm out of ideas. It still sounds like a network issue to me.

Mary

Thanks Mary I think i'm going to step back from this one for a day or two...

[Denburt]

I have the same issue here with our network, I also know of several other IT people that have run into it (asked me for help and all I could do was shrug). I am sure it is something to do with the network setup. The only option I know of is to place a copy of the Front end on each persons machine.

[MMcCarthy]

I have the same issue here with our network, I also know of several other IT people that have run into it (asked me for help and all I could do was shrug). I am sure it is something to do with the network setup. The only option I know of is to place a copy of the Front end on each persons machine.

One of our other posters with a similar problem found uninstalling IE7 solved the issue. Don't know if this applies to you.

Mary

[MMcCarthy]

One of our other posters with a similar problem found uninstalling IE7 solved the issue. Don't know if this applies to you.

Mary

Upon further investigation the other poster found this.

http://windowsxp.mvps.org/networkfile.htm

It seems IE7 considers an mde to be an executable file.

[blyxx86]

I've dealt with something similar to this myself, and I was able to fix it by making sure that the Windows Permissions on the "Backend.mdb" and the folder that the backend is stored in has giving read & write access to all the network users you'd be allowing on (if you using a domain anyways). If you are not on a domain, you may just need to add access to certain groups, like Network Users, or something similar.

I got tired of adding new people EVERY day to my primary database and just gave Read & Write permission to "Everyone" on both the backend.mdb and the folder in which it is stored (if you do not allow permission on the folder the user will not be able to create the .ldb file).

I'm sure someone can help you out to set up the permissions. I am currently running out of the building (sadly it's not on fire...tonight). However this link should provide a bit of an overview, and you sound like you can manage your way around the network. http://extension.oregonstate.edu/eso...soffcampus.php