should Linked tables be removed if workstation is off the network?

[Garren.Shannon]

Hey all,

I have a transportation ridership database I have been building for months now. I am getting to the end. The last part is a stand-alone version for trip entry. I test to see if I am on the network before running DSNLess connection code. When I am attached to the network, everything works as expected. But when I put the Surface Go in airplane mode (simulating being out of network range) Access crashes as it tries to load/connect the linked tables.

The program will run without the linked tables so I am wondering if I should be deleting the links if the user is not on the network. Below is the DSNLess connection code I run. All user activity reads and writes to local copies of the tables and simply don't need the linked tables. the only time the linked tables come into play is when the user is On-Line and ready to upload records to the master SQL DB.

If the network is not available, I think I should be deleting the linked tables right?

    If PingOk("10.8.0.73") Then
        ' Connect to the central SQL database
        If AttachDSNLessTable("dbo_Drivers", "dbo.Drivers", "mssql2019", "ridership", "", "") And AttachDSNLessTable("dbo_Vehicles", "dbo.Vehicles", "mssql2019", "ridership", "", "") And AttachDSNLessTable("dbo_Trip", "dbo.Trip", "mssql2019", "ridership", "", "") And AttachDSNLessTable("dbo_Trans-Type", "dbo.Trans-Type", "mssql2019", "ridership", "", "") And AttachDSNLessTable("dbo_SchoolYrDates", "dbo.SchoolYrDates", "mssql2019", "ridership", "", "") Then
            MsgBox ("Connected to core database")
        Else
        '// Not okay.
        End If

    ' Turn off annoying warnings and Update driver, vehicle and school year databases from SQL master tables
        DoCmd.SetWarnings (False)
        DoCmd.OpenQuery ("ResetVehiclesQuery")
        DoCmd.OpenQuery ("AppendFromDBO_Vehicles")
        DoCmd.OpenQuery ("ResetDriversQuery")
        DoCmd.OpenQuery ("AppendFromDBO_Drivers")
        DoCmd.OpenQuery ("ResetSchoolYrDatesQuery")
        DoCmd.OpenQuery ("AppendFromDBO_SchoolYrDates")
        'Turn the warnings back on
        DoCmd.SetWarnings (True)
    Else
        MsgBox ("I can't reach the master database at this time but you can still add trips!")
    End If

 

[CJ_London]

depends if they are really required - if you delete them, do you need to recreate them when back on the network? If so, why not just have your code not do anything that relates to the linked tables when not on the network

 

[Garren.Shannon]

Hey CJ... same answer... it depends. I have 5 tables in the local DB. 4 of them are copied/uploaded from the master SQL and only copied when they have changed. Those tables hold data related to new buses or a new bus drivers that the manager added. The 5th table is what needs to be uploaded BACK to the master DB... the details about trips each driver takes.

So I only need to link when the driver is back at the transportation center.

Funny thing is, the ping test I am using has returned varying results. I am recreating the links only if the "IF PingOk then" command returns true. I have tested this and it mostly works but sometimes while off the net, it returns true... which should be impossible. It's running the exact same code each test and while I have the PC in Airplane mode, it should NEVER test true... yet is does.

I am thinking this check for access to the SQL server is not the best way to test.

Public Function PingOk(Ip As String) As Boolean

    PingOk = (0 = CreateObject("Wscript.Shell").Run("%SystemRoot%\system32\ping.exe -n 1 -l 1 -w 1000 " & Ip, 0, True))
    MsgBox (PingOk)
    
End Function

 

[CJ_London]

couple of other methods discussed here

Checking network connection using vba

Is there any way to check NEtwork connection in vba? I am using this command: If Dir("O:\") = "" Then MsgBox "you have network connection" Else MsgBox "No Connection" End If but it doesn...

stackoverflow.com

Excel VBA - Check if connected to internet AND if its via Wifi or Ethernet

I need to alert users before running a macro which takes a lot longer if run on Wifi as opposed to the company LAN. I have found the following code: Checking for internet connection using VBA The

stackoverflow.com

 

[Garren.Shannon]

couple of other methods discussed here

Checking network connection using vba

Is there any way to check NEtwork connection in vba? I am using this command: If Dir("O:\") = "" Then MsgBox "you have network connection" Else MsgBox "No Connection" End If but it doesn...

stackoverflow.com

Excel VBA - Check if connected to internet AND if its via Wifi or Ethernet

I need to alert users before running a macro which takes a lot longer if run on Wifi as opposed to the company LAN. I have found the following code: Checking for internet connection using VBA The

stackoverflow.com

Thanks CJ... I like the concept of checking the NIC status better than trying to ping an IP. I will try that and see if it works better.

 

[Pat Hartman]

You don't actually need to check the network connection. Leave the tables linked permanently. When the user asks to do the sync, check the connection by doing a dCount() on one of the linked tables. You need an On Error GoTo to trap the error. The errors I get are 3024, 3044, 3043. Here is code I use. You will need to modify it to suit your situation. I have a form named formCheckLinks and that is the form that all my applications use as the startup form. You don't want to check the links at startup. You want tp check them when the user asks to do the upload so you would need to put your "catch" code in the first case statement and either open the form or display a message or just automatically link the forms.

Private Sub Form_Open(Cancel As Integer)

   On Error GoTo Form_Open_Error
    
    If DCount("*", "tblJob") > 0 Then
        DoCmd.OpenForm "frmLogin"
    End If    

Form_Open_Exit:
    DoCmd.Close acForm, Me.Name
   Exit Sub

Form_Open_Error:
    Select Case Err.Number
        Case 3024, 3044, 3043
            MsgBox "Tables have moved.  Please relink.", vbOKOnly
            DoCmd.OpenForm "frmReLinkJetOrACETables", , , , , , Me.Name
            Resume Form_Open_Exit
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Open of VBA Document Form_Form1"
    End Select
End Sub

 

[Garren.Shannon]

You don't actually need to check the network connection. Leave the tables linked permanently. When the user asks to do the sync, check the connection by doing a dCount() on one of the linked tables. You need an On Error GoTo to trap the error. The errors I get are 3024, 3044, 3043. Here is code I use. You will need to modify it to suit your situation. I have a form named formCheckLinks and that is the form that all my applications use as the startup form. You don't want to check the links at startup. You want tp check them when the user asks to do the upload so you would need to put your "catch" code in the first case statement and either open the form or display a message or just automatically link the forms.

Private Sub Form_Open(Cancel As Integer)

   On Error GoTo Form_Open_Error
   
    If DCount("*", "tblJob") > 0 Then
        DoCmd.OpenForm "frmLogin"
    End If   

Form_Open_Exit:
    DoCmd.Close acForm, Me.Name
   Exit Sub

Form_Open_Error:
    Select Case Err.Number
        Case 3024, 3044, 3043
            MsgBox "Tables have moved.  Please relink.", vbOKOnly
            DoCmd.OpenForm "frmReLinkJetOrACETables", , , , , , Me.Name
            Resume Form_Open_Exit
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Open of VBA Document Form_Form1"
    End Select
End Sub

Pat... the problem is that when the system is off-net, Access bombs and stalls as it tries to reconnect the SQL tables. Am I missing something here???

 

[Pat Hartman]

My suggestion prevents you from attempting to connect because it tries to access a linked table BEFORE trying to link. It then traps the error and opens a relink form in my case. Do whatever you want in the Case statement that traps the error. My solution assumes that you leave the tables linked at all times. If the driver opens the database and he is connected, the tables will be live. If he is not connected, they will error when you try to read them.

 

[Garren.Shannon]

My suggestion prevents you from attempting to connect because it tries to access a linked table BEFORE trying to link. It then traps the error and opens a relink form in my case. Do whatever you want in the Case statement that traps the error. My solution assumes that you leave the tables linked at all times. If the driver opens the database and he is connected, the tables will be live. If he is not connected, they will error when you try to read them.

Got it Pat... I didn't fully read your message. My apologies on that. And your suggestion makes total sense. I originally looked for a try-catch approach but could not find a method. I see this is the method.

I replaced the ping code with a GoTo and it is working. The test I put in is to try to do a recordset count on a linked table. It takes a while to return back that it can't link. I count almost 18 seconds for it to report it can't access the table. Is there a way to shorten the delay? I am using a record count function and I call the function to see if the table is accessible.

Public Function FindRecordCount(strSQL As String) As Long
' this function pulls the number of records from the database entered as a string. It will work with any record set, db, query,whatever.


Dim db As DAO.Database
Dim rstRecords As DAO.Recordset
    Set db = CurrentDb
    
    'Open record set
    Set rstRecords = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    'test for end of file
    If rstRecords.EOF Then
        FindRecordCount = 0
    Else
        rstRecords.MoveLast
        FindRecordCount = rstRecords.RecordCount
    End If
    'set public variable "UploadCount" eq to function output so it can be read outside the function
    UploadCount = FindRecordCount
    'MsgBox (UploadCount)
    rstRecords.Close
    db.Close
    Set rstRecords = Nothing
    Set db = Nothing

End Function

Private Sub Form_Load()

    On Error GoTo Form_Open_Error
    
    FindRecordCount ("dbo_Trip")
    Me.UploadBtn.Visible = True
    

Form_Open_Continue:
    Me.Requery
    GoTo Form_Load_Exit
    
    
Form_Open_Error:

    MsgBox ("In the error loop")
    OnNet = False
    Me.UploadBtn.Visible = False
    Resume Form_Open_Continue
        
Form_Load_Exit:
    
End Sub

 

[Pat Hartman]

Is there a way to shorten the delay?

Not offhand. The delay is caused because Access makes multiple tries to connect. I am busy now but look for a property that controls the number of retries. I don't know if you can set such property in VBA but you might be able to reduce the try count here before you run the dCount() and then reset it after.

 

[Minty]

For some reason, this seemed to work a little quicker when I was having similar issues and testing a few methods, but I have no idea why.

Function IsODBCConnected(TableName As String) As Boolean
    
    If Not TableExists(TableName) Then Exit Function
    
    Dim rst As DAO.Recordset
    
    On Error GoTo Endhere
    Debug.Print Err.Number
    Set rst = CurrentDb.OpenRecordset(TableName, dbOpenSnapshot)
Endhere:
    IsODBCConnected = (Err.Number <> 3151)
    
End Function