Ensuring Data Conservation in Multi-User Environment

[Robjenj]

Current Situation:-
• Busy but impoverished veterinary wildlife hospital with 30 – 40 new patients each day (more in the summer), and 1 pro bono inexperienced programmer (that’s me!).
• Software (in Access 2010) is 100% proven working on individual computers in a linked file system currently containing >30k records.
• Central server with a .accdb data record file (the “DB” file).
• 8 Client computers with 11 individual .accde Client files linked to central server data record file.
• 4 of these 8 computers (“R1”, “R2”, “V1”, and “V2”) in constant use. The other 4 computers are in occasional use.
• “R1” and “R2” are at reception, each with one Client File instance, and are used to admit and discharge patients.
• There are 5 monitors (‘Screens’) and two computers (“V1” and “V2”) in the surgery
• “V1” runs 3 instances of the Client file, (“Client File”, “Client File Copy 2”, and “Client File Copy 3”). Client File Copy 2 is assigned to show the ‘Waiting List’ (on Screen 2) and Client File Copy 3 is assigned to show the ‘Procedures List’ (on Screen 3) and the 3rd instance, using Screen 1, is used by the vets to update patient records.
• “V2” runs 2 instances of the Client file, (“Client File Copy 4” and “Client File Copy 5”). Client File Copy 4 is assigned to show the ‘Foster – Release List’ (on Screen 4) and either the ‘In-House List’ or a patient record (for updating) on Screen 5.
• Each of the Waiting List, Procedures List, Foster-Release List, and In-house List forms are automatically timer-updated every 5 minutes.
• To avoid data loss the DB file and the Client files have Default Record Locking set at “Edited Record”.
However:-
We still have the occasional (but embarrassing) loss of patient data which appears to be caused programmatically.
It appears that a potential cause of the data loss could be clashes between the Client file automated timer-updates of the five principal screens (Forms) and the vet and vet-nurse manual updates of individual patient records on any of the 7 Client file instances. To overcome this it has been proposed to remove the program automated timer-updates on the five forms, and cause updates to them all instead only upon closure of the manually updated Patient Record on any of the Client files.
However:-
Because closure of the Patient Record form on say “V2.Client File Copy 5” could not cause a refresh of the Procedures List run by “V1.Client File Copy 3”, etc, in order to achieve this it appears that we would have to remove the individual .accde Client files from the V1, V2, R1, and R2 machines and install instead 7 links to a server-located Client file so that closure of a Patient Record on the server Client file could cause the updating of the queries running the five principal screens.
We do not want to slow the responsiveness of the system or to overload the server.
Your comments and advice would be greatly valued.

 

[smig]

Can you explain what you mean by saying 'loss of patient data" ?

 

[Steve R.]

Based on my limited experience, do NOT use MS Access as a back-end. When I first experimented with a multi-user set-up, we had an MS Access back-end. Data integrity and performance was poor. I would suggest doing a forum search on the use of MS Access as a back-end as a second opinion.

I would encourage the use a free open source database as your back-end (MYSQL, Maria, Firebird, or HSQLDB. Another suggestion SQL Server Express.

Since we had an IT Department we had to use MS SQL Server. In the absence of an IT department, use one of the recommendations above.

 

[Robjenj]

Many thanks indeed SteveR, and Smig! Greatly appreciated.
The data loss is actually complete record loss. An illustration is a vet entering copious notes on a koala's Patient Record one afternoon, then closing the Patient Record form and proceeding to write up another patient's record. The Client files are shut down overnight. In the morning the DB could not be opened, so a C&R was carried out, after which the DB was again usable. The vet tried to look for the koala's Patient Record, but it had gone. The MSysCompactError table gave no help whatsoever other than to show that a record had been deleted programatically.
Re the back end, we have proposed to move it to SQL Server Express, but have not yet had the opportunity to do so. I would prefer to sort out the record loss problem first if we are able to do so.

 

[Steve R.]

Re the back end, we have proposed to move it to SQL Server Express, but have not yet had the opportunity to do so. I would prefer to sort out the record loss problem first if we are able to do so.

My guess, the record loss problem results from using MS Access as a back-end and won't be solved until the database is moved to SQL Server Express. Verify, by making a copy of the database onto SQL Server and then testing the back-end with both approaches.

 

[smig]

I guess the problem caused by the way the db is closed.
Why the db cant be opened in the morning ? What cant be opened in the morning the BE or the the FE ?
Do you have a single FE or each user use his oun copy ?

 

[Steve R.]

I guess the problem caused by the way the db is closed.

I don't know. I would encourage you to do an internet/forum search concerning MS Access data loss in a multiuser environment. Once our project (back-end) was moved to MS SQL Server, the issue of data loss was solved.

Why the db cant be opened in the morning ? What cant be opened in the morning the BE or the the FE ?

Again, I do not have an answer in terms of an MS Access back-end. I would suggest that one of the SQL databases (previously identified above) be loaded on one PC (as a dedicated back-end) and be left running 24/7.

Do you have a single FE or each user use his oun copy ?

Each person gets their own copy of the MS Access front-end, which runs on their PC.

 

[spikepl]

And my theory, with exactly as much evidence as the theories presented so far, is that Saturn in opposition to Jupiter exercises bad influence on your data

Seriously, things hardly ever happen by themselves. You said you have evidence that "record had been deleted programatically." Now that is a clue to pursue. What in your code deletes records and when? You could consider logs, error traps... mz-tools allow you to insert error code everywhere . .yuou could use that o insert logs for each subroutine call too. I agree with you that hoping that the error will go away by itself, when migrating to MS SQL, is not an entirely kosher error handling technique

I just recalled that I have an order system, where I do not rely on Access record locking, becasue I never managed to present a clear and unambiguous user interface solution. So in code I myself actively prevent multiple useres operating on the same order. In my system, the user table has a column LockedOrder, and there I store the OrderID the given user is working on. When a user attempts to open an order, if that order is in the table he'll be able to open the order read-only, with message of who is working on the order. You also have to have some clearing mechanism to wipe this table in case of a system crash.

 

[jdraw]

Do the copious notes go into memo fields? They can be a ??treat?? sometimes.
How exactly does the record get closed, and move on to the next patient?
What do you mean

The Client files are shut down overnight.

If a user left a form open, and the machine was closed down/shut off, very likely the current record would not be saved and would/could cause a problem.

Readers really need to know more about your database structure; split or not; separate front ends on each user PC....

There are many successful databases with multi users that work well. I wouldn't jump to SQL server etc as a first step.

You might consider transaction processing before that; but some serious debugging is called for.

 

[smig]

I don't know. I would encourage you to do an internet/forum search concerning MS Access data loss in a multiuser environment. Once our project (back-end) was moved to MS SQL Server, the issue of data loss was solved.

Again, I do not have an answer in terms of an MS Access back-end. I would suggest that one of the SQL databases (previously identified above) be loaded on one PC (as a dedicated back-end) and be left running 24/7.

Each person gets their own copy of the MS Access front-end, which runs on their PC.

Steve,
I asked the OP these questions.
I dont need the answers

 

[Robjenj]

Thank you all VERY much for all your help. A few replies:-
Smig:-
• 8 Client computers with 11 individual .accde Client files linked to central server data record file.
• FE cannot be opened without C&R.
SteveR:-
• ” I would encourage you to do an internet/forum search concerning MS Access data loss in a multiuser environment.” Thanks, but I thought that was exactly what I was doing!
Spikepl:-
• There is only one code that deletes records. This operates on demand as an entirely separate feature. It has no links to any other code and operates through a series of isolated button clicks enabling selection of the record, confirmation of selection, then deletion. This deletes records cleanly.
• Sometimes if there has been a programmatic deletion the patient record table can be opened and it then shows a series of “#Deleted” notes on all affected columns in that table.
• I like your “LockedOrder” system, and had tried to institute a similar system, until I realised that any of the vets, vet nurses, and occasionally receptionists had to be able to operate on any machine or machine-instance in order to optimise hospital efficiency, without log-off, log-on “red tape”. So we require the responsible vet to sign off against his vet-notes, but having a user ID necessary in order to open a specific form was found impractical. Now if I could find a way to identify the machine-instance, as opposed to a specific user, THAT would be very useful!
Jdraw:-
• The copious notes DO go into memo fields. Veterinary notes can get seriously extensive, especially if an animal is presented several times! The Patient Record gets closed by a button that checks satisfactory responses to several fields before closing. If responses are unsatisfactory the code will not close the record, but moves the cursor to the problem field.
• Each machine-instance is individually closed by means of a “Close Database” button. You can’t get back to the ‘switchboard’ form to operate this button without closing any other form that may be open, UNLESS by using the top right hand corner ‘x’ button and crashing it. This seldom happens, and has never caused the record-deletion problem.
• By “The Client files are shut down overnight” I mean that all client machines are shut down overnight.
• “Readers really need to know more about your database structure; split or not; separate front ends on each user PC....” Please see my original posting “Central server with a .accdb data record file (the “DB” file). 8 Client computers with 11 individual .accde Client files linked to central server data record file.” Etc.
• “You might consider transaction processing . . “ I don’t understand! Please explain!

 

[Steve R.]

SteveR:-
• ” I would encourage you to do an internet/forum search concerning MS Access data loss in a multiuser environment.” Thanks, but I thought that was exactly what I was doing!

In part, but there other places to also look. I entered the following into a Google search: "MS access database loses data over a LAN" One hit: "10 ways to prevent Access database corruption" Here's what they wrote and I believe may apply equally well to a LAN:

#7: Be careful with WAN connections

A WAN connection that covers any connection from a local system to a server via the Internet can cause trouble. Reading the database may be slow but acceptable. However, writing to the database is error prone and can cause corruption. When bottlenecked traffic interrupts data transfer, Access times out, believing the connection has been lost. This behavior usually leaves the backend database in a corrupted state.

----------------------------------------------------------------------------------

"Proper way to program a Microsoft Access Back-end Database in a Multiuser Environment" In this post, those posting were much more positive about using MS Access (as both a front-end and back-end) over a LAN. You will need to read all the posts. Of course, I will reprint the post below, since it is an example that reinforces the solution that I have been suggesting.

The corruption problems stopped when we started using SQL Server Express as our backend database. I think that speaks for itself. – Robert Harvey

There is a much more detailed post somewhere on this forum that covers the issue of data corruption on a LAN. I have not been able to re-locate it. The solution, for me, as previously mentioned was to move the MS Access back-end to MS SQL Server. Any of the other databases previously referenced should work and they are available for free.

 

[smig]

I also use my oun locking system.
Its also better using it when you use routines to edit records.
Many times I prefer unbound forms which require you to use an OK button to save the record.

You can use API to get the machine name or the windows" user name.

Closing the machine or the data server prior to closing the form can cause a data losss.
If you bound a form to a table, query or recordset the record is changed when you move to other record or properly close the form.

 

[Robjenj]

Thanks again guys! It really is wonderful to be able to tap into good support before the head-bashing cracks the skull or the wall falls down!
Thanks for the ref to Tech Republic. I am a member there, but had not thought of searching there. At the wildlife hospital we run a hard-wired system back to the server, so I don't think we have that sort of problem, but I note well Point 3 ("Beware of memo fields"). I think I shall give that priority, but also get moved over to SQL Server Express as soon as possible.
Smig, how would I use API to get the machine name?

 

[essaytee]

... snip ...

Smig, how would I use API to get the machine name?

I'm not Smig but here is what I use. Add a new module and declare the api's, as follows. (These are 32 bit references - haven't got 64 bit references handy) - It's the first two declares that are important, in your case, (I've included the Login name as well).

Option Compare Database
Option Explicit
 
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    
Private Declare Function apiGetComputerName Lib "kernel32" Alias _
    "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

 
Private Declare Function apiShellExecute 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
    
'***App Window Constants***
Public Const WIN_NORMAL = 1         'Open Normal
Public Const WIN_MAX = 3            'Open Maximized
Public Const WIN_MIN = 2            'Open Minimized

'***Error Codes***
Private Const ERROR_SUCCESS = 32&
Private Const ERROR_NO_ASSOC = 31&
Private Const ERROR_OUT_OF_MEM = 0&
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&

Add the following wrapper functions to the same module.

Function GetOSMachineName() As String
    
    On Error GoTo Err_GetOSMachineName
    
    'Returns the computername
    Dim lngLen As Long, lngX As Long
    Dim strCompName As String
        lngLen = 16
        strCompName = String$(lngLen, 0)
        lngX = apiGetComputerName(strCompName, lngLen)
        If lngX <> 0 Then
            GetOSMachineName = Left$(strCompName, lngLen)
        Else
            GetOSMachineName = ""
        End If

Exit_GetOSMachineName:
    Exit Function
Err_GetOSMachineName:
    MsgBox "Could not retrieve the Machine Name of this computer"
    Resume Exit_GetOSMachineName
    
End Function

Function GetOSUserName() As String
    On Error GoTo Err_GetOSUserName
       
    ' Returns the network login name
    Dim lngLen As Long, lngX As Long
    Dim strUserName As String
     
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If lngX <> 0 Then
        GetOSUserName = Left$(strUserName, lngLen - 1)
    Else
        GetOSUserName = ""
    End If
    
Exit_GetOSUserName:
    Exit Function
    
Err_GetOSUserName:
    MsgBox "Could not retrieve the logged on user, User Name"
    Resume Exit_GetOSUserName
    
End Function

Hope this helps.

Steve.

ps. I'm not as dismissive of Access as a multi-user system as others are but then as per another poster I also only use unbound forms to conduct edits. The user must click SAVE or CANCEL to commit or not commit the changes, period.

 

[Steve R.]

I'm not as dismissive of Access as a multi-user system as others are but then as per another poster I also only use unbound forms to conduct edits. The user must click SAVE or CANCEL to commit or not commit the changes, period.

Access makes for an excellent front-end.

I have also used unbound forms that require a manual save. Furthermore, pressing "save" button executes a variety of data verification checks. The data on the form cannot be saved till the "bad" data is fixed. That has kept the creation of incomplete records to a minimum.

Good luck with your project.

 

[Robjenj]

Thanks, essaytee! Greatly appreciate your help!

 

[Robjenj]

Thanks, SteveR! All forms on our system are bound, because during the construction of the system from scratch I had not appreciated that there were advantages in using unbound forms. I shall now investigate the advantages of unbound forms.
You guys have given me a lot of thought-food, which I shall now try to turn into action!!

 

[gemma-the-husky]

I have not read everything in the whole thread, but I have to say I am concerned about some of the advice you are getting.

eg. SteveR keeps advising against the use of Access for the back end, and against bound forms.

I have to say that this is ridiculous. A wholly access system with bound forms will perform perfectly (and I mean 100% perfectly) adequately on this system.

There are very few reasons not to use bound forms, and using unbound forms as a general case is unlikely to be efficient.

Data security is a different issue, as access is not as secure a system as some other backends. But as far as performance goes, there is absolutely no reason not to design a fully access system.

if you are losing data, or getting inconsistent updates, then this is most likely to be a problem with the system design and implementation.

Database development is not trivial. If you are a business, and can afford it, I would recommend getting some paid for advice, I think.

 

[spikepl]

I agree with Dave.

How are unbound forms supposed to solve the problem? Unbound forms are for people who know exactly why they should have them, or for people who have never fully understood how the mechanics of Access function, so they find it necessary to build themselves all the facililties otherwise provided by Access (talk about babies and bathwater...)

I'd suggest (like others before me) that you debug your problem thoroughly. I have difficulty seeing how the changes proposed so far should fix the problem. Perhaps simply because no one knows what the actual cause is.