Multi User database

My access database was multi-user but recently, after moving it to another server it became single user.  Is this a workgroup issue or something?

I've checked the options menu to make sure it is shared.
Who is Participating?
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
Well then you defintly locking on pages.  The workaround I mentioned is to pad the records enough so that only one record can fit on a page (Access records cannot span pages).  Since a page in A97 is 2048 bytes, you need to ensure that the page is larger than 1024 bytes.

This is done by calculating the current record size and then adding enough text fields filled with something to pad the record out.

I can go into more detail if you wish.
Hi keypusher,

What is exactly the problem?

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Make sure that all users have full read/write/delete priviledges for the directory where the MDB is stored.

If Access cannot create a LDB file, it will open in exclusive mode.

Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

keypusherAuthor Commented:
I get a message something like: You can not save at this time it is being edited by another user.  There is a .ldb file and I checked the read/write privileges.
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
OK that's something else again.  You didn't say which version of Access this is, but until A2000, Access locked on pages and not records.

 This means that a single user can cause more then one record to be locked and hence, the dialog your getting.

There is a workaround for this, but before I go further, what version are we talking about?

keypusherAuthor Commented:
Access '97
keypusherAuthor Commented:
I little bit more detail would be helpful.
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
What you'll need to do is first figure out the existing record size.  OLE and memo fields are stored seperately from the rest of the record, and a 16 bytes pointer is put in wil the rest of the record unless they use 32 bytes or less.  

So for a record you have:
7 bytes overhead
1 byte overhead for each defined field
size for fields as you would expect (4 bytes for integer and single, 8 for long and double, etc).

Text fields however are special as they only store what's in the record.  In other words if I define a text field with 50 as the maximum length, and put a "A" in the field, only 1 character of storage of space is used.  So for text fields, assume one byte.

Total up all your existing fields and figure out the pad length (1024- record size).  Let's say you need 734 bytes of padding.

  734/255 (maximum text fields) = 2.878

 in this case, you'd round that off to 3 text fields of 255 characters each. Open the table in design view, add 3 text fields and set the deafult values to:

="xxxxxxxxxxxxxxxxxxxx.....xxxxxxxxxxx"  (255 "x")

  You must do this.  If you don't put something in the field, the space won't get used.

  Remember to run an update query on existing records as the default value only applies to new records.


You have many open questions:

To assist you in your cleanup, I'm providing the following guidelines:

1.  Stay active in your questions and provide feedback whenever possible. Likewise, when feedback has not been provided by the experts, commenting again makes them receive an email notification, and they may provide you with further information. Experts have no other method of searching for questions in which they have commented, except manually.

2.  Award points by hitting the Accept Comment As Answer button located above and to the left of that expert's comment.

3.  When grading, be sure to read:
to ensure that you understand the grading system here at EE. If you grade less than an A, you must explain why.

4.  Questions that were not helpful to you should be PAQ'd (stored in the database for their valuable content?even if not valuable to you) or deleted. To PAQ or delete a question, you must first post your intent in that question to make the experts aware. Then, if no experts object after three full days, you can post a zero-point question at community support to request deletion or PAQ. Please include the link(s) to the question(s).
At that point, a moderator can refund your points and PAQ or delete the question for you. The delete button does not work.

5.  If you fail to respond to this cleanup request, I must report you to the Community Support Administrator for further action.

Our intent is to get the questions cleaned up, and not to embarrass or shame anyone. If you have any questions or need further assistance at all, feel free to ask me in this question or post a zero-point question at CS. We are very happy to help you in this task!

community support moderator

for keypusher

It's time to clean up this TA, so I will leave a recommendation in Community Support that this question is:
 - Answered by: JDettman  
Please leave any comments here within the
next seven days.


Per recommendation, force-accepted by
CS Moderator
All Courses

From novice to tech pro — start learning today.