I’ve got a small tracking spreadsheet that myself and 1-2 other members of my team at work will be using over the next couple of months.
I’ve got 23 fields used and just under 1500 records.
I’d like to convert it to Access, place it on a network drive (Windows Server-hosted, if it matters) and have all 3 of us track our activities on it.
8 years back I was able to do this just fine at another site, but now when I try to have multiple users reading and writing I get:
“Microsoft Office Access can’t 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.”
I’ve gone online and researched this, so I’ve changed some settings in Access per the suggestions seen on various sites:
Access -> Options -> Advanced
. Default Open Mode = Shared
. Default Record Locking = Edited Record
. Open Databases Using Record-Level Locking = Checked Yes
I’m still getting the message pasted above…
Am I missing something here?
I’ve seen directions about making sure to “Not open in Explorer, but just open the file using Access” so that Windows will obey Access’ “Open Shared” setting, but I just tried that and I’ve still got the problem.
I realize I could just make separate MDB files with, for instance, 500 records each, but I’d rather avoid the associated administrative headaches.
This behavior is by design. It means that two people can’t be making changes to the structure of the database (editing queries, forms, reports, table layout) at the same time, which could obviously cause problems. It doesn’t refer to changing the data itself.
The best practice is to split the database in two. One file to hold just the tables (called the back-end), and a second to hold the queries, forms, reports, and code (the front-end). The front end contains only linked tables pointing to the back-end. There is a wizard in Access that will do that for you.
This way you can give everyone their own copy of the front-end, and any structure changes they make affect only their copy of the front-end. They will have exclusive access to it and can do what they want. All data changes go to the back-end, which everyone has access to.
This does raise the problem of version control. I keep a “master” copy of the front-end. If anyone makes a change you think should be retained, you can put it in a copy of the master, test it, and then give everyone a new copy of the master to work with.
Personally I think the answer is to avoid Access like the plague and use Excel until you are using a database with many hundreds of thousands of records. Excel is a sweetheart while Access is a pain in the ass.
I will note that I was, in fact, just opening my form “Unity” in standard “Open” view, and not in Design View.
I just tried to add the number “1” to the NOTES field on Record 1 and hit save.
No changes to any table or form designs were occurring.
Mr. Slant - double check the users accesss rights to the folder on the server. They MUST be able to create files, not just modify. The reason is that MS Access creates an [my db name].ldb whenever a user opens the db (except when they specify read-only).
I just right-clicked on the associated folder.
I went to Effective Permissions and queried for my user account.
I note that I have check boxes for… pretty much all possible effective permissions including Full Control and all others.
BTW - I’ve always used “No locks” because in older versions of Access the locks would tie up most of the table if not all. No locks means that two users can start to edit the same record, but the one that saves last will get an error, or maybe a choice of how to handle the conflict.
When you outgrow Excel that’s when you switch to FileMaker. Not Access, FileMaker.
When you outgrow FileMaker (i.e., you’ve got more than a couple hundred concurrent users or more than a couple hundred million records per table) that’s when you go Oracle or SQL.
Tabular data stored in Excel can get messed up really quite easily - there’s nothing to bind the horizontal rows together - all too easily, a user can sort a subset of the columns, or insert data into some of them, shifting just those columns’ data down, out of sync with the rest of their records.
Excel is great for performing analysis on lumps of data that arrive all in one go, but it’s lousy as a database for record sets that are subject to many small appends.
I have 3 PCs and 3 monitors on my desk that I’ve been using for testing; I am the only human that’s ever touched this DB thus far, and I am acutely aware of which PC has her open at what time.
Good looking out, though!