Access warnings relating to "exclusive" mode

[kaleolani65]

I recently started a job at a medical staffing company, a database used to keep information was built by a lady there who has very limited Access experience (even less than me)

They run Access2k3, on WinXP desktops

The files and DB reside on the corporate server.

She complains that in the mornings the first person who opens the DB locks all others out, once they logout and the others log in everyone can begin working.
Through research I figure that the first person is opening the DB in "exclusive" mode therefore locking all others out.

1. How can I fix this "lockout" problem?

Some research says that the DB data should reside on the server, while the "frontend" should reside on the individual workstations.

2. Is this something that can be done after the fact?

Another problem is the caution box...

***Microsoft office access cant save design changes or save to a new database object because another user has the file open. To save your design changes or to save to a new object, you must have exclusive access to the file.***

3. Would the implementation of a DB users and groups schema help things?
For instance, setting having one DB admin and having the rest of the others set up as users.

The research I did online and in this forum helps a little, but most of it is over my head

Thanks in advance for any help...

[ADezii]

I recently started a job at a medical staffing company, a database used to keep information was built by a lady there who has very limited Access experience (even less than me)

They run Access2k3, on WinXP desktops

The files and DB reside on the corporate server.

She complains that in the mornings the first person who opens the DB locks all others out, once they logout and the others log in everyone can begin working.
Through research I figure that the first person is opening the DB in "exclusive" mode therefore locking all others out.

1. How can I fix this "lockout" problem?

Some research says that the DB data should reside on the server, while the "frontend" should reside on the individual workstations.

2. Is this something that can be done after the fact?

Another problem is the caution box...

***Microsoft office access cant save design changes or save to a new database object because another user has the file open. To save your design changes or to save to a new object, you must have exclusive access to the file.***

3. Would the implementation of a DB users and groups schema help things?
For instance, setting having one DB admin and having the rest of the others set up as users.

The research I did online and in this forum helps a little, but most of it is over my head

Thanks in advance for any help...

__1 Tools ==> Options ==> Advanced ==> Default Open mode = Shared
__2 The Data itself (Tables) should reside on the Server, while the Application Objects (Forms, Macros, Reports, Queries, Modules) reside on the Client PCs. These Front End DBs (Clients) are then Linked to the Back End Database residing on the Server. Multiple Users can now access a centralized Data Source that now exists on a Back End Database (Server). The creation of Front and Back End Databases from a single Database can definately de done 'after the fact'.
__3 It is a simple fact of life that certain design changes can only be implemented while a Database is opened in Exclusive Mode (non Multiuser access). To do this, ==> open Microsoft Access ==> File => Open ==> select Database to Open ==> click on little arrow on Open button ==> select Open Exclusive.
__4 The Database will now be Open in Exclusive Mode. Make any necessary Design changes then exit. Proceed as you normally would.
__5 Should you have any other questions, feel free to ask.

[kaleolani65]

Thanks for the prompt reply...
So how confident can I be that if I change the default open method to "shared" that I won't mess up their DB, i'm just worried because I just started there. I know that there are multiple clinics across the country which all login to the DB and they are all on a distributed file system, but i'm pretty sure that all of the data propogating the DB resides only on the server. Could this structure impact the DB adversely if I changed the default open mode to "shared"?

As far as the front end/back end thing, as I understand it the entire DB (forms, and data) reside on the server. If I were to split it up, what exactly would reside on their desktop PC's (front-end)? What would be on the back-end? I read your post but I think I am a bit confused about the exact nature of the wording. The DB is made up of forms, which is where the data resides, so what would be the front-end?

During my research on how to split it up can you think of any pertinent information I should obtain before going through with the split?

If I were to set up a permissions schema for the DB could this possibly take care of the problem of the data sometimes not being written? Or are these two different topics?

Thanks again for the help

[ADezii]

Thanks for the prompt reply...
So how confident can I be that if I change the default open method to "shared" that I won't mess up their DB, i'm just worried because I just started there. I know that there are multiple clinics across the country which all login to the DB and they are all on a distributed file system, but i'm pretty sure that all of the data propogating the DB resides only on the server. Could this structure impact the DB adversely if I changed the default open mode to "shared"?

As far as the front end/back end thing, as I understand it the entire DB (forms, and data) reside on the server. If I were to split it up, what exactly would reside on their desktop PC's (front-end)? What would be on the back-end? I read your post but I think I am a bit confused about the exact nature of the wording. The DB is made up of forms, which is where the data resides, so what would be the front-end?

During my research on how to split it up can you think of any pertinent information I should obtain before going through with the split?

If I were to set up a permissions schema for the DB could this possibly take care of the problem of the data sometimes not being written? Or are these two different topics?

Thanks again for the help

Changing the Open Mode to Shared should not affect the Database adversely, but as common sense would dictate, always backup yourr Data before initiating any changes.

In short, only Tables (data) would reside on the Back End, while Forms, Macros, Modules, Reports, and Queries would reside on the Front Ends.

I do not thank Database Permissions relate to the problem of data periodically not being written. It appears to be some other, unrelated, problem.

[NeoPa]

This sounds to me like the original designer has somehow included a design change into the running of the database.
When this happens the database locks itself exclusively!
This would explain both the exclusive locking as well as the database reporting that changes couldn't be saved to the design of the database.
Look in the code for anything which changes the design of any objects other than TableDefs or QueryDefs.

[kaleolani65]

This sounds to me like the original designer has somehow included a design change into the running of the database.
When this happens the database locks itself exclusively!
This would explain both the exclusive locking as well as the database reporting that changes couldn't be saved to the design of the database.
Look in the code for anything which changes the design of any objects other than TableDefs or QueryDefs.

I am not sure on the exact meaning of "design changes" vs. the actual data which is written into the DB.

Is there a way I could investigate the possibility of design objects being changed while looking through the GUI part of Access?

If so what would I look for specifically,

Through speaking to the lady who built the DB she explained:
-The "DB" is actually a collection of tables, none of which are linked to any others.
-The only thing users do is enter data into the tables.
-The users dont change any of the default values associated with individual rows/columns
-So from what I understand the DB is nothing more than a collection of non-linked "spreadshee ts"
-If she needed to add another table, say for instance, a new state has come online, she would simply build another table.
-Each column consists of info. such as last name, first name, city, state, etc...

I am going to try to change the default open mode from "exclusive" to "shared" once I confirm from my boss that everything is backed up and able to be restored if necessary.

If I can give any more information to help you guys answer my dilemma please ask.

Thanks
Chuck

[NeoPa]

From post #5 :
Look in the code for anything which changes the design of any objects other than TableDefs or QueryDefs.

Does the database have any code (Look in VBA window - Alt-F11)?

What is the current Open Mode (Can you confirm it is explicitly Exclusive)?

[kaleolani65]

From post #5 :
Look in the code for anything which changes the design of any objects other than TableDefs or QueryDefs.

Does the database have any code (Look in VBA window - Alt-F11)?

What is the current Open Mode (Can you confirm it is explicitly Exclusive)?

I have looked and found that the default open mode is "Shared" already, I don't know if it matters but I checked on one of the users workstations logged into the domain as a simple "user" with no admin privileges.

Give me one moment and I will check on the code, just Alt-F11 right?

[kaleolani65]

From post #5 :
Look in the code for anything which changes the design of any objects other than TableDefs or QueryDefs.

Does the database have any code (Look in VBA window - Alt-F11)?

What is the current Open Mode (Can you confirm it is explicitly Exclusive)?

Just checked, I get the VB window that comes up and there is absolutely no code.

[NeoPa]

May I ask what you checked exactly to be sure? Not that you're wrong, necessarily, just that you may have overlooked the code if you're not familiar with the VBA window.