Access Database in a Wireless Environment

[D.E.N.N.I.S.]

Looking for suggestions on how to correct an issue I've been running into while trying incorporate a automated work assignment tool. The database is pretty simple, I use a series of recordset updates (see code below) to randomly assign users to workloads based on age and proximity of the users. The users run their front-end database stored locally on their laptops which links to the back-end file which stores all of the data tables.

The problem I am facing is sometimes users will get a "#NAME?" in the data field which I believe is indicative of a lost connection. Similarly, sometimes a users data is not recorded in the database despite then running the macro which updates the data. When I run the database from a desktop computer everything appears to be working fine. I run into issues when multiple users are using the database using laptops.

Our wireless connectivity is not great and the laptops are located on vehicles which are basically zooming all around a football field size warehouse. I'm thinking our spotty connection issues are the main culprit. Any tips on what I can do to help minimize the impact of a bad wireless connection via settings or code?

Main Code

Option Compare Database

Private Sub Form_Load()
        
        Call AvailableForWork
        MsgBox "Available for Work"
        Call Step3 'Step 3: Run optimization calculation on next workload selection
        Call Step4 'Prevent duplicate workload assignment
        Form.Requery
        
End Sub

Private Sub btnCompleteWorkload_Click()
        
        Call Step1 'Step 1: Update complete time of completed workload
        Call Step2 'Step 2: Update last zone of stockkeeper and set priority in work queue by last completed workload
        Call Step3 'Step 3: Run optimization calculation on next workload selection
        Call Step4 'Step 4: Prevent duplicate workload assignment
        
End Sub

Private Sub Form_Close()
    
    Call NotAvailableForWork
    Call NoWorkQueueCompletion
    Call WorkloadDropped
    
End Sub

Private Sub Form_Current() 'Completed Workload Counter
    Text17 = DCount("[ID]", "qryStockkeeperCounter")
End Sub

Sub Step1() 'Update complete time of completed workload
    
        On Error GoTo ErrorHandler
        sql1 = "SELECT * FROM tblOpenWorkloads WHERE CompleteTime IS NULL AND Stockkeeper = fOSUserName() AND Assigned = True"
        Set rs1 = CurrentDb.OpenRecordset(sql1)
        With rs1
            If Not .BOF And Not .EOF Then
                .MoveLast
                .MoveFirst
                    If .Updatable Then
                        .Edit
                        ![CompleteTime] = Now()
                        ![Completed] = True
                        .Update
                    Else
                        MsgBox "Error"
                    End If
            End If
        .Close
        End With

ExitSub:
    Set rs = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Error: Step 1"
    Resume ExitSub

End Sub

Sub Step2() 'Step 2: Update last zone of stockkeeper and set priority in work queue by last completed workload
        
        On Error GoTo ErrorHandler
        sql1 = "SELECT * FROM tblActiveQueue WHERE Stockkeeper = fOSUserName() AND TimeOut Is Null"
        sql2 = "SELECT TOP 1 tblOpenWorkloads.Zone FROM tblOpenWorkloads WHERE Stockkeeper = fOSUserName() ORDER BY CompleteTime ASC"
        Set rs1 = CurrentDb.OpenRecordset(sql1)
        Set rs2 = CurrentDb.OpenRecordset(sql2)
        With rs1
            If Not .BOF And Not .EOF Then
                .MoveLast
                .MoveFirst
                If .Updatable Then
                    .Edit
                    ![LastWorkload] = Now()
                    ![LastZone] = rs2!Zone
                    .Update
                End If
            End If
        .Close
        End With
        
ExitSub:
    Set rs = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Error: Step 2"
    Resume ExitSub

End Sub

Sub Step3() 'Step 3: Run optimization calculation on next workload selection or add to no work queue
        On Error GoTo ErrorHandler
        Form.Requery
        sql2 = "SELECT * FROM tblOPENWorkloads WHERE CompleteTime IS NULL AND Assigned = False"
        sql3 = "SELECT TOP 1 tblOpenWorkloads.Zone FROM tblOpenWorkloads WHERE Stockkeeper = fOSUserName() ORDER BY CompleteTime ASC"
        sql4 = "SELECT TOP 1 * FROM qryNoWorkQueue WHERE TimeOut Is Null ORDER BY TimeIn ASC"
        Set rs2 = CurrentDb.OpenRecordset(sql2)
        Set rs3 = CurrentDb.OpenRecordset(sql3)
        Set rs4 = CurrentDb.OpenRecordset(sql4)
        
        If rs2.RecordCount > 0 And rs4.RecordCount = 0 Then
            With rs2
                If Not .BOF And Not .EOF Then
                    .MoveLast
                    .MoveFirst
                    While (Not .EOF)
                        If .Updatable Then
                            .Edit
                                ![LastZone] = rs3!Zone
                                ![Stockkeeper] = fOSUserName()
                                ![AssignmentTime] = Now()
                            .Update
                            .MoveNext
                         End If
                    Wend
                End If
            .Close
            End With
        Else
            Call NoWorkQueueAddition
            MsgBox "No Workloads Available"
        End If
        
ExitSub:
    Set rs = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Error: Step 3"
    Resume ExitSub
End Sub
Sub Step4()
        
        sql4 = "SELECT TOP 1 * FROM tblOPENWorkloads WHERE CompleteTime IS NULL AND Stockkeeper = fOSUserName() AND Assigned = False"
        Set rs4 = CurrentDb.OpenRecordset(sql4)
            With rs4
                If Not .BOF And Not .EOF Then
                .MoveLast
                .MoveFirst
                    If .Updatable Then
                        .Edit
                        ![Assigned] = True
                        .Update
                        .MoveNext
                    End If
                End If
            .Close
            End With
            
ExitSub:
    Set rs = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Error: Step 4"
    Resume ExitSub
End Sub
Sub AvailableForWork()
   On Error GoTo ErrorHandler
        
        'Stockkeeper logged as available for work
        sql1 = "tblActiveQueue"
        Set rs = CurrentDb.OpenRecordset(sql1)
        With rs
            .AddNew
            .Fields!Stockkeeper = fOSUserName()
            .Fields![TimeIn] = Now()
            .Update
            .Close
        End With
        
ExitSub:
    Set rs = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Error: Available For Work"
    Resume ExitSub
End Sub

Sub NotAvailableForWork()
    On Error GoTo ErrorHandler
        
        'Stockkeeper logged as unavailable for work
        sql2 = "SELECT * FROM tblActiveQueue WHERE Stockkeeper = fOSUserName() AND TimeOut Is Null"
        Set rs = CurrentDb.OpenRecordset(sql2)
        With rs
            If Not .BOF And Not .EOF Then
                .MoveLast
                .MoveFirst
                If .Updatable Then
                    .Edit
                    ![TimeOut] = Now()
                    .Update
                End If
            End If
        .Close
        End With

ExitSub:
    Set rs = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Error: Not Available for Work"
    Resume ExitSub
End Sub

Sub NoWorkQueueAddition()

    On Error GoTo ErrorHandler

    sql5 = "tblNoWorkQueue"
    sql6 = "SELECT TOP 1 tblOpenWorkloads.Zone FROM tblOpenWorkloads WHERE Stockkeeper = fOSUserName() ORDER BY CompleteTime DESC"
    sql7 = "SELECT * FROM qryNoWorkQueue WHERE Stockkeeper = fOSUserName() and TimeOut IS Null"
    Set rs5 = CurrentDb.OpenRecordset(sql5)
    Set rs6 = CurrentDb.OpenRecordset(sql6)
    Set rs7 = CurrentDb.OpenRecordset(sql7)
    
    If rs7.RecordCount = 0 Then
        With rs5
            .AddNew
                .Fields!Stockkeeper = fOSUserName()
                .Fields!TimeIn = Now()
                .Fields!LastZone = rs6!Zone
            .Update
            .Close
        End With
    End If
    
ExitSub:
    Set rs = Nothing
    Exit Sub

ErrorHandler:
    MsgBox "Error: No Work Queue Completion"
    Resume ExitSub

End Sub

Sub NoWorkQueueCompletion()
        
        On Error GoTo ErrorHandler
        sql2 = "SELECT * FROM tblNoWorkQueue WHERE Stockkeeper = fOSUserName() AND TimeOut Is Null"
        Set rs = CurrentDb.OpenRecordset(sql2)
        
        With rs
            If Not .BOF And Not .EOF Then
                .MoveLast
                .MoveFirst
                If .Updatable Then
                    .Edit
                    ![TimeOut] = Now()
                    .Update
                End If
            End If
        .Close
        End With
ExitSub:
    Set rs = Nothing
    Exit Sub

ErrorHandler:
    MsgBox "Error: No Work Queue Completion"
    Resume ExitSub
        
End Sub

Sub NoWorkQueueRequery()
        On Error GoTo ErrorHandler
        sql2 = "SELECT * FROM tblOPENWorkloads WHERE CompleteTime IS NULL AND Assigned = False"
        sql3 = "SELECT TOP 1 tblOpenWorkloads.Zone FROM tblOpenWorkloads WHERE Stockkeeper = fOSUserName() ORDER BY CompleteTime ASC"
        sql4 = "SELECT TOP 1 * FROM qryNoWorkQueue WHERE TimeOut Is Null ORDER BY TimeIn ASC"
        Set rs2 = CurrentDb.OpenRecordset(sql2)
        Set rs3 = CurrentDb.OpenRecordset(sql3)
        Set rs4 = CurrentDb.OpenRecordset(sql4)
        
        If rs2.RecordCount > 0 And rs4!Stockkeeper = fOSUserName() Then
            With rs2
                If Not .BOF And Not .EOF Then
                    .MoveLast
                    .MoveFirst
                    While (Not .EOF)
                        If .Updatable Then
                            .Edit
                                ![LastZone] = rs3!Zone
                                ![Stockkeeper] = fOSUserName()
                                ![AssignmentTime] = Now()
                            .Update
                            .MoveNext
                         End If
                    Wend
                End If
            .Close
            End With
            Call NoWorkQueueCompletion
            Form.Requery
            Call Step4
            MsgBox "New Workload Assigned"
        Else
        End If
        
ExitSub:
    Set rs = Nothing
    Exit Sub
ErrorHandler:
    Resume ExitSub
End Sub


Private Sub Form_Timer()
    Call NoWorkQueueRequery
End Sub


Sub TimerReset()
    Me.txtTimer.Value = Me.txtTimer.DefaultValue
End Sub

Sub WorkloadDropped()
        On Error GoTo ErrorHandler
        sql9 = "SELECT TOP 1 * FROM tblOpenWorkloads Where Stockkeeper = fOSUserName() AND CompleteTime Is Null AND Assigned = True"
        Set rs9 = CurrentDb.OpenRecordset(sql9)
            With rs9
                If Not .BOF And Not .EOF Then
                .MoveLast
                .MoveFirst
                    If .Updatable Then
                        .Edit
                        ![Assigned] = False
                        .Update
                        .MoveNext
                    End If
                End If
            .Close
            End With
            
ExitSub:
    Set rs = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Error: Workload Dropped"
    Resume ExitSub

End Sub

 

[bob fitz]

AFAIK Access should not be used on a wireless network. No ands, ifs or buts

 

[jdraw]

Just to say I agree with Bob. Posts I have seen say --if you are going to have a wireless network, then consider opting for SQL Server Express (free). Access has all sorts of issues with wireless networks.

That's second hand info, but many experienced people/developers have made this recommendation.

Good luck.

 

[CJ_London]

I concur that access is not intended to work across wireless networks and using something like SQL server (express) is an option. But also consider

a) using citrix or terminal server

b) look at using transaction processing (google begintrans and committrans) you should at least be able to capture that something hasn't updated and take the appropriate action to try again.

c) Also take a look at this link for checking network connection

http://stackoverflow.com/questions/24452368/checking-network-connection-using-vba

 

[stopher]

I agree with the other posts although I thought I'd mention I created a field based ordering system by taking a client server approach (I didn't have a server d/b I could use). The client access databases would receive information (customer and product updates) by downloading xml files from the server. The client had all the functionality to create orders then send the orders back as xml files. There was a bit of checking to ensure the files had transferred. The "server" was also an Access database. It continually checked for incoming xml files and processed accordingly. It was a pretty simple setup but it worked because the worst that would happen if the file failed to send and the code would check and resend. The sales team happily updated in the nearest McDonalds.

The above works because the "users" don't need to edit data directly. They only need snippets of updates.

By the same token you could expand so that the client sends its status as an xml. In this way you can build up a simple com between server an client.

 

[The_Doc_Man]

I'm going to amplify Stopher's comments. Your problem with a wireless network is that Access cannot verify integrity of its data if it has a make-n-break connection scenario. You are dealing with SMB data protocols, which require a lot of handshaking to verify each packet, including sequence numbers and checksums and such as that.

If you take Stopher's approach, you inherently have to use some other method of getting data to the right place, as e.g. FTP or SFTP client/server network technology where you can have a script EXTERNAL TO ACCESS to do the transaction-file transfer and, if it doesn't work, retransmit! TCP/IP utilities like FTP and SFTP can do the necessary checking and even can be configured to not time out or go bonkers over a brief network drop.

Access, on the other hand, is actively seeking data integrity and will "blow up" if it can't verify that integrity. I would look seriously into having two FTP or SFTP "daemons" running on your computers - one on remote side, one on the central system - and then when you want to send data, trigger a script that does a command-line sequence to send a file to your partner. Then your partner can do some File System Object operations to look for a new file, import it, and DELETE or RENAME or MOVE that file once it is processed. And you can send transactions the other way if you wish, provided you let your partner tell you its address so that your central site can send its answer to the correct site.

For what it is worth, it isn't only Access that has this kind of problem. If you were working with bloody ORACLE, you STILL would have issues with network interruptions. There is no O/S in the world that will totally ignore network outages - at least, none that I have worked with at the Navy.

 

[D.E.N.N.I.S.]

Thank you for your comments, I have a lot to digest. I'll post an update this week when I get a chance to investigate the options further.