Avoid errors with multiple users in a database

[bruceblack]

Hi everyone! I have a general problem i desperately need help with. Thanks in advance!

So, i have this problem in multiple databases i made. So i would like to keep this topic kind of open to get the basics right.

I have update queries. They all work fine on their own.
Example: CurrentDb.Execute "delete_drivers", dbFailOnError
I use this one on opening the database.

My problem is when another user opens the database, it gives me:
Undefined function "date" in expression

In another example, i have a delete query in a Autoexe macro.
This also works fine, but for the other users that start the database, they get errors.

Question:What is the best way to approach this when you deal with multiple users?

Im trying to avoid rehauling all these databases, so a sufficient answer would be more than fine . Thanks!!

 

[Tieval]

You could use an if statement for this and check if the laccdb file exists, that way it would only do it when the first user opened it.

 

[Minty]

This sounds like a not ideal design - deleting things in a multi-user environment isn't normally the correct way to do things.

However I suspect you are sharing a single copy of the database on a network share, this will lead to corruption and a multitude of other issues.
You need to split the database into a user frontend (FE)and a data backend(BE). Each user should have a local individual copy of the FE.

Have a good read here http://www.fmsinc.com/microsoftaccess/databasesplitter/

 

[arnelgp]

Check first if the table is in use before taking some action on it:

Public Function IsTableOpen(strName As String) As Boolean
IsTableOpen = SysCmd(acSysCmdGetObjectState, acTable, strName)
End Function

 

[bruceblack]

Hi guys!

Well, this was an interesting read all together . Thanks for all the swift replies.

I found a solution all together and it seems to work flawlessly...but i would like your opinion if i did the right thing.

Solution: In Access, theres an option to split the database into 2 files. A front and back-end.

Go to: database tools > Access database > Split database

This seems to work flawlessly...?

 

[Minty]

Yes - I believe the inbuilt tools work well for this..

Now just make sure each user get their own copy of the FE database.

 

[The_Doc_Man]

After the fact, your "success" story tells me that you were running into file locks AND that your DB was therefore having locking conflicts. It would take a rather fussy analysis of the DB files in question to determine what actually broke due to lock conflicts, but that is pretty much the most likely explanation.

Which is why you WANT to have split DBs when sharing. The back-end can be shared because it's just data tables. The front-end that contains all the other stuff is where structural conflicts can easily occur. By giving everyone their own copy of the front end file, you make those locks "private" i.e. nobody but that user sees the locks for opening the private copy of the DB.

 

[bruceblack]

Aah i understand. Good point!

It's working fine for now. Hopefully it wont conflict anymore.
Because we have a terrible network configuration. Everybody is working on a thin client (a terminal) and has the same network drive. It's a terrible mess which gave me enough problems in the past. So im used to designing databases with those work arounds .

It's still running perfect. If i make major changes with better solutions, ill come back to this thread. Thanks EVERYONE!

 

[The_Doc_Man]

As long as everyone has some private storage capabilities to keep separate copies of those front-end files, you are probably OK.

But now I have to ask if you are implying that everyone shares the same copy of Access? That is how I would interpret "thin client" in a network context.

 

[Pat Hartman]

Although it is preferable to run the FE from the client's C: drive, it is possible to run the FE from a server. However, it is still necessary for each user to have his own personal copy of the FE. You can do this on the server by creating a private directory for each user and copying the FE to that folder.

If you need help with creating a batch file to do this, post back.

The next thing you need to work on is whatever you are doing that is causing records to be added and deleted all the time. That not only causes database bloat, it can cause conflicts between multiple concurrent users.

In practice Make Tables and append/delete for temporary data are way overused. The vast majority of the time, a query with parameters will solve the problem without the clutter. Queries and tables are interchangeable for most things according to Access. That's why you can't name a table objectX and create a query with the same name but Access doesn't care if you have a report and a form and a macro with the same name.

 

[bruceblack]

Indeed TheDocMan, they all (6 instances) use the same copy.
Also nobody has personal space or anything like that.

The file just sits in a shared folder. But, it works alright.
Like i said, its not the first or only database im running.
The changes are instant. For example if another user changes something in a continuous form, you will see it on the other users almost instantly without touching the keyboard.

We're using Access 2010. You all seem a little surprised, but i dont know any better

 

[Mark_]

From a development stand point, you will also see a giant benefit of having a split database; your "Work" copy isn't the same one that end users are using. As each has a copy of the front end you can set up an update system so as new front ends are created they can be rolled out as each user access their copy.

 

[The_Doc_Man]

The file just sits in a shared folder. But, it works alright.

It works alright until the day it doesn't. Access DOES NOT LIKE a shared FE configuration. This is NOT a long-term viable solution. (Hate to sound harsh but emphasis is appropriate.)

If you look on this forum regarding corruption caused by unsplit databases and databases where the FE is shared, you will find data loss, code loss, and damaged forms as a result. The ONE AND ONLY way that you can share a front-end is to allocate time slots to each user so they don't step on each other. Having non-private files is INVITING trouble on heavy card stock with gold-leaf lettering.

 

[bruceblack]

Thanks Doc. Thats a lesson i'm going to apply one way or another i think then.
Tomorrow i will talk to IT and see if we can assign different files to user.

Question: When i split the database, can i have a copy of the FE with different file names?

Like:
DataBaseFE1
DataBaseFE2
DataBaseFE3

or something like that, sharing the same BE?

 

[Pat Hartman]

That just makes more work for you. The most efficient method is to Create separate folders. Each user would have a shortcut that copies the "master copy" of the FE to HIS personal shared folder. Therefore, all you have to do to distribute a new copy is to replace the master copy and send out an email to the users to shut down and reopen. Or, if the change isn't urgent, don't bother with the email. The users will get the update the next time the run the shortcut. If everyone uses a uniquely named version in the same folder, you will have trouble updating them when you need to release a new copy.

Here's the code for my Citrix version of the install batch file since it is closer to what you'll need to build. Show this to the IT people and have them help you with it. If push comes to shove and you can't work this out so that they all link to a single bat file that figures out their profile name, then you can create a separate batch file for each user that is hardcoded with the path.

md %USERPROFILE%\DwgLog
del %USERPROFILE%\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" %USERPROFILE%\DwgLog
%USERPROFILE%\DwgLog\DrawingLog.accdb

 

[Mark_]

Thanks Doc. Thats a lesson i'm going to apply one way or another i think then.
Tomorrow i will talk to IT and see if we can assign different files to user.

Question: When i split the database, can i have a copy of the FE with different file names?

Like:
DataBaseFE1
DataBaseFE2
DataBaseFE3

or something like that, sharing the same BE?

It is possible, but as Pat has pointed out not a good option.

The ONLY time I would say having something like this is if you really have separate "Modules" that all talk to the same back end. Each would be for a different role or purpose but would still be copied to the users machine/directory.

If your goal is to have "BobsFE" and "JoansFE", this would be a massive headache you'd rather not indulge in. If your goal is "Sales Reporting" and "Sales Entry", this is a useful technique.

 

[The_Doc_Man]

Concur with Pat's opinion. Make the names all the same but arrange for each user to have a different copy, perhaps a unique folder in a shared sub-folder. The uniqueness counts more than the actual location.

 

[bruceblack]

Hi Pat. Many thanks!
Great solution. I'll be checking it out today.

Cheers!