Lost network connection handling

[agust]

I have a backend database in a shared folder in one computer in a LAN and some frontend files in several computers. If the LAN connection is lost (e.g. caused by unplugged cables), then the database will lose the connection as well and there is a message of disk or network error.

How should I handle this error? If the physical problem were solved and the LAN connection were restored, can I restore the database connection by using VBA without closing the frontend files?

 

[WayneRyan]

agust,

Don't know exactly what you mean here.

You can use a timer event to periodically either:
(1) Reference a linked table to see if the back-end server is alive
(2) Use an ODBC call (with a very short time-out) to retrieve something

You can quite easily trap any errors with the above methods.

The following are a couple of code fragments to establish an ODBC
connection and refresh Access table links.

'
' Establish an ODBC connection
'
  Set DbConnection = Nothing
  strConnection = "Driver={SQL Server};" & _
                  "Server=" & Me.txtServer & ";" & _
                  "Database=Master;TrustedConnection=Yes;"

  Set DbConnection = New ADODB.Connection
  With DbConnection
    .CursorLocation = adUseClient
    .Mode = adModeReadWrite
    .Properties("Prompt") = adUseClient
    Call .Open(strConnection)
    End With
'
' Refresh all table links
'
For Each tdf In CurrentDb.TableDefs
   If Len(tdf.Connect) > 0 Then
      tdf.Connect = ";DATABASE=" & Me.txtFilterDatabase
      tdf.RefreshLink
   End If
   Next tdf

hth,
Wayne

 

[agust]

Actually I don't use ODBC. I set Record Source of every forms to the linked tables. So, if the network connection is disconnected for a few seconds, then all fields will show Errors indication. Even if the connection is re-connected, it seems that the database can not re-establish the connection and showing the "Disk or Network error". The user needs to close the front-end and re-open it.