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...

[kaleolani65]

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.

No problem,
I opened up one of the tables, hit Alt-F11

A two paned window came up with the name of the table on the left and a large blank area on the right. The title bar said something about VB.

I made sure the table name was highlighted but did'nt double click it or anything.

And your assumption was correct I have never seen the VBA window before.

I think I should clarify something also, it is my understanding that each table is actually a standalone DB, so in essence she has a large group of unrelated DB's each with only one single table in it.

(isnt that better known as a spreadsheet?)

[NeoPa]

I'll have to get back to this later as I'm off now.
I'll try to post some further instructions on the usage of the VBA window.

[kaleolani65]

I'll have to get back to this later as I'm off now.
I'll try to post some further instructions on the usage of the VBA window.

Thanks, any help would be greatly appreciated...

[kaleolani65]

They just had a manifestation of the problem.

A user at the Arizona office tried to Access the DB, he received an error message something to the effect of:

This file has been placed in a locked state by 'admin' at tcma-corp0013

Once I found out who 0013 was I asked them to quit the MS Access DB program, this in turn allowed the user in Arizona to log in. Once Arizona had logged in, 0013 was then able to start up the app. with no problems.

[NeoPa]

This is exactly what I would expect if the situation described in post #5 were manifested. I have databases in the office where they run quite happily until I make a change to the design of any of the objects (Not TableDefs or QueryDefs). If I try to make a design change it will attempt to change the Open Mode to Exclusive on the fly. If it can it will, otherwise it will refuse my changes. When I am finished, I have to close and re-open the database before my colleagues can use the database.

I'll look at putting something together to explain the code better.

[kaleolani65]

They just had a manifestation of the problem.

A user at the Arizona office tried to Access the DB, he received an error message something to the effect of:

This file has been placed in a locked state by 'admin' at tcma-corp0013

Once I found out who 0013 was I asked them to quit the MS Access DB program, this in turn allowed the user in Arizona to log in. Once Arizona had logged in, 0013 was then able to start up the app. with no problems.

Sorry about the reply for an edit.

I spoked to the lady and the above mentioned lockout happened when:
1. tcma-corp0013 was using x database
2. a phoenix staff member tried to open x database
3. phoenix was subsequently locked out

So, it only happens when 1 person has a DB open and another tries to access it
at the same time, as I said the workaround is for person 1 to quit the app., person 2 opens the DB up, and then person 1 can reopen the same DB.

Correct me if i'm wrong but if a user opens a database and commences to make design changes, the DB locks it down and promotes that user to 'admin' status.

I asked her if maybe somehow users were implementing design changes without knowing, she said that the users could barely navigate to the tables.

If the DB looks like a spreadsheet and all the users do is enter name, address, etc. could they be implementing design changes?

What exactly are design changes? i saw the post earlier about macros, and such. But can anyone think of simplified example of a design change being written into the running of the DB, keeping in mind the structure of this particular DB and it's use.

This problem is limited to one DB (Arizona),

[kaleolani65]

This is exactly what I would expect if the situation described in post #5 were manifested. I have databases in the office where they run quite happily until I make a change to the design of any of the objects (Not TableDefs or QueryDefs). If I try to make a design change it will attempt to change the Open Mode to Exclusive on the fly. If it can it will, otherwise it will refuse my changes. When I am finished, I have to close and re-open the database before my colleagues can use the database.

I'll look at putting something together to explain the code better.

That sounds exactly like what's going on :-)

I am still foggy on the idea of a design change though, i'm not exactly sure what constitutes one.

[NeoPa]

A design change is a change of the design of a Form; Report; Module; Macro.
I assumed it was in the code but that would be unusual - quite advanced.
I have a post half-prepared (I had to stop for supper) explaining how to find all the VBA code. Macros are found in the Macro window.
I can't imagine a user doing this though :confused:
BTW A database contains multiple tables but not multiple databases. I doubt multiple databases are in use (though it's possible).
I'll try to get the other post finished for you soon.

[NeoPa]

If you go into the VBA window (Alt-F11) you will have various windows available to you.
Press Ctrl-R to select the Project Explorer window. This can show up to three main folders :

1. Microsoft Office Access Class Objects
2. Modules
3. Class Modules

You need to open these three (or those that exist anyway). Each object within this structure can be selected and, using F7, the code opened. Let us know if you find any code more than the defaults :

Option Compare Database
Option Explicit

[kaleolani65]

If you go into the VBA window (Alt-F11) you will have various windows available to you.
Press Ctrl-R to select the Project Explorer window. This can show up to three main folders :

1. Microsoft Office Access Class Objects
2. Modules
3. Class Modules

You need to open these three (or those that exist anyway). Each object within this structure can be selected and, using F7, the code opened. Let us know if you find any code more than the defaults :

Option Compare Database
Option Explicit

I will run the check tomorrow morning as soon as I get to work ~8:00a PST
Thanks for the help so far...