Multiple Users

[wr66]

I've created an Access database, nothing very sophisticated, based on a simple order-entry system, so there are two main tables, linked by an index number.

It works fine in single user mode, but when a second user tries to add a record, the message "You do not have exclusive access to the database at this time. If you proceed to make changes, you may not be able to save them later."

On a different network, I can't even get that far - the message is on the lines of " Such and such a user has placed the database in a state where it cannot be locked".

Any ideas, you good folk out there?

 

[BNPMike]

Jet uses a locking scheme to control simultaneous updates. I've forgotten how it works but you can often get these kinds of effects (in databases generally) with small numbers of records. If locking occurs at the page level you will lock the page concerned and its index page. If you've only got a few records, it's obviously more likely collisions will occur because all the data is on just one or a few pages.

 

[mkov]

There is a setting in Access that will cause this. In Access goto Tools - Options - Advanced Tab.

I have all of the machines here running access with the following settings:

Default Open Mode - Shared
Default Record Locking - No Locks
Open databases using record-level locking - unchecked

I believe if you have all of the machines running Access setup this way, you won't have any more problems.

 

[psemianonymous]

Are you trying to update (design) forms and reports while other users are logged in? This is specifically disallowed in Access2000+. It looks like your error message.

Solution: split your database into the data and the frontend, and then work on a development frontend. When you need to 'apply' changes, copy over the 'production' frontend.

 

[mkov]

Good point foolio12, I forgot all about that.

 

[wr66]

Thanks for your suggestions guys, but I'm no nearer solving the problem.

I can now emulate the error message - it is the same one you would expect if you attempted to modify a form/report whilst someone else is using the database. But I'm not modifying it! I'm just adding a record! It always comes at the same place - in the subform, on the third visible field. It accepts the first two values, and then BANG!

I've looked in the field properties, and there doesn't seem to be anything different about this field than the previous ones in the subform. So why on earth does it spit its dummy out at this point?

It works a dream in single-user mode, but as soon as anyone else logs in - it's no go.

It is worth putting the tables in one Access database, and have separate local mdb's, using links to the tables in the master mdb? Would it work if I compiled separate run-time bundled versions for the nodes? Or would I be wasting my time?

Your thoughts on this would be most welcome.

 

[wr66]

It occurred to me during the night (is that sad, or what?) that the error message cuts in when accessing the third field of the subform when other users are logged in.

Can this be a co-incidence, but this is the first field on the subform which has its total displayed on the main form.

Perhaps if I were to delete the calculation field from the main form, it might permit data entry?? And if it does, how do I go about restoring the calculation facility?

Also, when adding a subform to a main form, say for order lines to an order header form, what's all that referential integrity all about? Which options should I select for a no-nonsense, reliably-linked subform?

Thanks folks.

 

[psemianonymous]

This is over my head. If you want super-expert advice, I recommend comp.databases.ms-access - you can post via Google Groups. You can ever refer them back to this thread (though I'd include a description at the least). The external link for this thread is

[URL unfurl="true"]http://www.tek-tips.com/gviewthread.cfm/lev2/4/lev3/27/pid/181/qid/765018[/URL]

Sorry I couldn't help directly.


Pete

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[Blorf]

Hi Wr. Can you tell me more about the calculated field?

If you are using a macro or vba code that changes the design of the forms, you will have problems. If the field is just a total of your Sales order lines, then the user would not enter that any way, it would calculate, so I assume I do not fully undersand.

I also have built a fairly complex database for sales order entry, with lots of fun gadgets and tools, so maybe I can help.

ChaZ

Ascii dumb question, get a dumb Ansi