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

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

[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

Well I took a look
-I pulled up the VBA window, then project explorer
-in the VBA window on the left (project explorer) the name of the DB is listed
-there are no main folders eg. class objects, modules, class modules
-I highlight the name of the DB and use F7 but nothing comes up
-If I right click the DB name the option for "check code" is grayed out

Any ideas...

[NeoPa]

If the Project Name doesn't have a little + in a box to the left of it then you don't have any code objects. Try the Macros (though I doubt they could change design).

[kaleolani65]

If the Project Name doesn't have a little + in a box to the left of it then you don't have any code objects. Try the Macros (though I doubt they could change design).

Yeah, there is nothing there...

Now I cant get the DB unlocked, I have had the other users log out and restart but nothing seems to work.

Right before the "lockout" problem when they tried to open a form a dialog box appeared asking for them to "enter a parameter" and it gives the name of a column
(number)

I followed the instructions here: http://support.microsoft.com/kb/303134

I thought someone had changed the name of a field by accident but after I changed it the DB locked me out and said that I couldnt make design changes anymore...

[NeoPa]

I'm sorry, but there's nothing you've told me so far that would explain why this is happening. I know that's a little along the lines of "Hands up all those pupils not here today..." but I can't think of anything that explains this without you're telling me something I need to know, or my looking at the database itself - which is not practical (unless you secretly live next door).
Did you check the Macros?

[kaleolani65]

I'm sorry, but there's nothing you've told me so far that would explain why this is happening. I know that's a little along the lines of "Hands up all those pupils not here today..." but I can't think of anything that explains this without you're telling me something I need to know, or my looking at the database itself - which is not practical (unless you secretly live next door).
Did you check the Macros?

Don't be sorry, I understand it's a bit like me trying to explain why my dog is sick having never seen a dog...

I looked for the macros but they are not even in the window???
I tried to look at the code but there is none, it's probably me doing something wrong.

[NeoPa]

Does your database use linked tables?
How large is your database? Including the linked tables?

[kaleolani65]

Does your database use linked tables?
How large is your database? Including the linked tables?

That's the funny thing
-the DB's tables are not linked
-there are about 15 tables in the DB
-each table is stand-alone

Excuse me if I am not using the right terminology

when I open MS Access,
i open a db (widgetDB),
inside of that DB there are ~15 tables (widget1,2,3,etc.) (when I view them it looks like a spreadsheet wit the primary key on the left)
I look at the forms and it is a matrix of the column names, and the records

[NeoPa]

What is the size of your WidgetDB.Mdb file?

[kaleolani65]

What is the size of your WidgetDB.Mdb file?

That I can tell you as sson as she comes back...

[kaleolani65]

What is the size of your WidgetDB.Mdb file?

Sorry about the delay.
In about an hour I will post the output of Documenter, hopefully someone will be able to see where the change in field names is making the "enter parameter value" box pop up.
This may also give NeoPa a better idea as to the structure of the DB.

[kaleolani65]

Sorry about the delay.
In about an hour I will post the output of Documenter, hopefully someone will be able to see where the change in field names is making the "enter parameter value" box pop up.
This may also give NeoPa a better idea as to the structure of the DB.

See if this helps anyone...


And the DB is about 4.5MB
When we fire up this table it complains and tells me to "enter parameter value" in the count section

Table: Multi-Markets Plans Page: 1
Properties
DateCreated: 1/30/2007 9:44:47 PM DefaultView: Datasheet

GUID: {guid {86649018-7B35- LastUpdated: 2/13/2007 9:12:02 AM

4842-8CF3-

NameMap: Long binary data OrderByOn: True



Orientation: Left-to-Right RecordCount: 10

Updatable: True

Columns



Name Type Size



Count Long Integer 4

AllowZeroLength: False



Attributes: Fixed Size

CollatingOrder: General

ColumnHidden: False



ColumnOrder: Default

ColumnWidth: 690

DataUpdatable: False

DecimalPlaces: Auto



DisplayControl: Text Box

GUID: {guid {364300D6-9E93-4705-B259-A06B33DE35DE}}

OrdinalPosition: 0



Required: False

SourceField: Count

SourceTable: Multi-Markets Plans


Multi-Market Plans Memo -

AllowZeroLength: True

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: 1935

DataUpdatable: False

GUID: {guid {7CA6A057-468C-46F0-B9DF-ABDFE24D19A7}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 1

Required: False

SourceField: Multi-Market Plans

SourceTable: Multi-Markets Plans

UnicodeCompression: True

ARIZONA Memo -
AllowZeroLength: True

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

GUID: {guid {194EDA17-7A32-4493-A490-D39A491D5EF8}}

\\tcma-med.com\public\ITDevelopment\MSS Tracking Wednesday, February 14,

Table: Multi-Markets Plans Page: 2

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 2

Required: False

SourceField: ARIZONA

SourceTable: Multi-Markets Plans

UnicodeCompression: True

FLORIDA Memo -
AllowZeroLength: True

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

GUID: {guid {BF8F0C0E-B23A-40C6-8DF5-1403804819C6}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 3

Required: False

SourceField: FLORIDA

SourceTable: Multi-Markets Plans

UnicodeCompression: True

NEVADA Memo -
AllowZeroLength: True

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

GUID: {guid {B8535537-3051-457C-BF67-367A6369E33E}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 4

Required: False

SourceField: NEVADA

SourceTable: Multi-Markets Plans

UnicodeCompression: True

TEXAS Memo -

AllowZeroLength: True

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

GUID: {guid {F7E268F2-9EB5-4706-8AC6-729DBC8578B9}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 5

Required: False

\\tcma-med.com\public\ITDevelopment\MSS Tracking Wednesday, February 14,

Table: Multi-Markets Plans Page: 3

SourceField: TEXAS

SourceTable: Multi-Markets Plans

UnicodeCompression: True

UTAH Memo -

AllowZeroLength: True

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

GUID: {guid {99F49EDD-CED7-49E2-8DC4-192F6AFAFFCE}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 6

Required: False

SourceField: UTAH

SourceTable: Multi-Markets Plans

UnicodeCompression: True

LOUISIANA Memo -
AllowZeroLength: True

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

DataUpdatable: False

GUID: {guid {9A8E93FC-564F-4CA7-A2D2-7A426BA9EE93}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 7

Required: False

SourceField: LOUISIANA

SourceTable: Multi-Markets Plans

UnicodeCompression: True

User Permissions

admin Delete, Read Permissions, Set Permissions, Change Owner,

Read Definition, Write Definition, Read Data, Insert
Data,

Group Permissions

Admins Delete, Read Permissions, Set Permissions, Change Owner,

Read Definition, Write Definition, Read Data, Insert Data,

Users Delete, Read Permissions, Set Permissions, Change Owner,

Read Definition, Write Definition, Read Data, Insert Data,

[kaleolani65]

Well,
I copied over the DB into another area so I could work on it. I pulled up the form which was having the "enter parameter value" problem in the count column. I looked at it in table view (or the one that looks like a spreadsheet) sorted the values in count in descending order, shut it down, fired it up and no complaint this time? I guess for the time being it's fixed.

When I tried it on the real file it would not allow me to save design changes since i was not the sole user of the DB, how would I find out who I need to log out so that I can get full control?

[NeoPa]

See if this helps anyone...


And the DB is about 4.5MB
When we fire up this table it complains and tells me to "enter parameter value" in the count section

{Loads of MetaData}

Right,
The files too big to e-mail to me (that was the reason for the question).
However, try renaming the field [count] to something else (as Count() is a function and therefore a reserved word). Count will work in most circumstances but clashes are possible.

From this MetaData, is it true that the database has only one object in total - a table called [Multi-Markets Plans]? Or possibly, did you just forget to include any of the other objects?

[NeoPa]

Well,
I copied over the DB into another area so I could work on it. I pulled up the form which was having the "enter parameter value" problem in the count column. I looked at it in table view (or the one that looks like a spreadsheet) sorted the values in count in descending order, shut it down, fired it up and no complaint this time? I guess for the time being it's fixed.

When I tried it on the real file it would not allow me to save design changes since i was not the sole user of the DB, how would I find out who I need to log out so that I can get full control?

As no-one else could be accessing it at the same time then this would be expected.
Having a Form to open implies that there is more to the database than just the simple table listed earlier.
Type out the .LDB file to see if you can see anyone else logged on.

[kaleolani65]

As no-one else could be accessing it at the same time then this would be expected.
Having a Form to open implies that there is more to the database than just the simple table listed earlier.
Type out the .LDB file to see if you can see anyone else logged on.

I played around with it and found that if I sorted the file in datasheet view from lowest to highest the DB stopped complaining about the "enter parameter value"
also when I looked at the DB in form view (I think it was form view) the count column was offset by two clicks so I repositioned through the drop down box and it seems to work.

[NeoPa]

Glad you got it sorted - I can't say I understand why - but who cares eh?