Convert application to multi user environment

[Carol W.]

We wrote an application several years ago that is being used in a single user environment - Win 7 Professional, Access 2010. It's working well and now the user wants to expand its use to a multi-user environment. Presumably, there would be two computers both accessing the same mdb files, potentially at the same time. I say files (plural) because the application consists of a data.mdb file and a code.mdb file.

We've done this kind of thing many years ago by setting up a common drive letter (R:). R: was set up by mapping the letter R to C:\xxxxxx, the folder in which the mdb files reside. On machine 1, R: was mapped as above. On machine 2, R: was mapped to the folder C:\xxxxxx on machine 1. The machines were obviously networked.

I guess what I'm asking is if this is the best way to accomplish the goal. What other issues should we be looking for - e.g. record locking etc.?

Any guidance would be appreciated. Thanks in advance.

[HansV]

It's best to give each user an individual copy of the frontend database (code.mdb), each linked to the one instance of the backend database (data.mdb).

Access is inherently multi-user, although in a limited way. Officially, it supports up to 256 simultaneous users, but in practice, that many users would cause performance to be very slow. Up to, say, 10 or 20 simultaneous users should be OK.

I'd use record-level locking. This can be set at the query and form level, and the default option can be set in File > Options, Client Settings:

S0781.png

[Carol W.]

Thank you Hans.

I'm assuming that you recommend separate copies of the frontend database for performance reasons. Yes?

The only downside I see to that approach is that code changes need to be made to multiple mdb files.

[HansV]

Yes, it improves performance, and it also reduces the risk of the frontend becoming corrupt (and if one of the copies becomes corrupt, the other users aren't effected).

And yes, updating the frontend is a bit more work - you'll have to distribute it to all users. If you need to update the frontend frequently and/or have many users, you might consider using a utility such as Auto FE Updater from Access MVP Tony Toews.

[Carol W.]

Follow up question: Do all versions of Access have to be the same in a multi user environment or is it OK to "mix and match"? My feeling is that they need to be the same but others disagree.

Thanks.

[HansV]

A mixed environment should work, as long as you don't use features that the older versions don't support. Performance may be a bit slower in a mixed environment.

[Carol W.]

Hans,

The application was written in Access 2003 and later modified in Access 2010.

The data.mdb file will be resident on a machine running Access 2010. The client machines (each of which will have its own code.mdb file) will either be running Access 2010 or Access 2013. This should be OK, with the caveat that performance may be impacted.

Do you agree?

Thanks.

[HansV]

Agreed.

(It doesn't matter which version of Access is installed on the PC with the data.mdb file; Access doesn't actually run there)

[Carol W.]

Hans,

Actually, the machine containing the data.mdb file will also have a code.mdb file and will act as a client to itself.

OK?

[HansV]

OK, as long as you do indeed have a separate frontend.

[Carol W.]

Yes, there's a separate front end.

Thanks.

[Carol W.]

I'm about to install the code.mdb file on a second machine.

Question: Do I need to set the record locking options you recommend on each query and form or will setting it in the Options be sufficient? Also, I assume I should set the options on all copies of the front end. Please confirm.

Thanks

[HansV]

The setting in Options > Advanced is the default setting. A newly created query will use that setting unless you change the setting in that query. Queries created before you set the default in Options > Advanced will not have been affected, so it's worth the effort to check all queries. And yes, it should be done in all copies of the frontend. It might be easiest to roll out a new version of the frontend with the correct settings to all users.

[Carol W.]

Everything you say makes sense.

Thanks for the quick reply.

[Carol W.]

I deployed the front end to two additional machines (in addition to the original "server' machine). One runs Win 7 and the other runs Win 8.1.

I encountered no issues with the Win 7 machine, loaded the front end and linked the tables to the "server" machine. On the Win 8.1 machine, however, I encountered a compile issue. Unfortunately, I was in a hurry and didn't take a screenshot of the exact error message but it pointed to a Private Declare Function statement. I Googled the message and found that I had to include a ptrsafe parameter in the Declare statement. This worked but now I have two versions of the front end, one for the two Win 7 machines and another for the Win 8.1 machine.

Attached is a screenshot of the code on the Win 8.1 machine. You can see that I commented out the original statement (as well as a few other things I tried).

I would like to only have one version of the front end code. How best can I accomplish this?

Thanks in advance.

[HansV]

Could you post the code itself instead of a screenshot? Thanks in advance.

[Carol W.]

How is this?

Do you need anything else? I don't always have access to this machine but I do right now.

Code: Select all

Option Compare Database
Public CutOutsAvailable As Long
Public UnOiledToysAvailable As Long
Public OiledToysAvailable As Long
Public TotalToysDistributed As Long
Public mlngclientid As Long

'Public Declare Function ShellExecute _
'  Lib "shell32.dll" Alias "ShellExecuteA" ( _
'  ByVal hwnd As Long, _
'  ByVal lpOperation As String, _
'  ByVal lpFile As String, _
'  ByVal lpParameters As String, _
' ByVal lpDirectory As String, _
'  ByVal nShowCmd As Long) As Long
  
'If VBA7 Then
    Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
        (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
'Else
'    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
'        (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
'End If


Public Const SW_SHOWNORMAL = 1
Public Const SW_SHOWMAXIMIZED = 3

[HansV]

Try this - it uses conditional compilation:

Code: Select all

#If VBA7 Then
    Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
        (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#Else
    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
        (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If

[Carol W.]

This conditional compilation code works on the 8.1 machine although I do get a red compile error (is it a compile error?). See attached screenshot.

Now I need to try it on one of the Win 7 machines. Will get back to you after I do.

Thanks.

[HansV]

It should still work.