I have a program that has some linked tables as well as internal. On startup, certain macros run using information from the linked table to update stored information.
But due to issues beyond my control, sometimes either the server is down, or worst case, the external linked table becomes corrupt. Since the macro can't run the update the program freezes. It doesn't have to though, it could still run and operate even though this linked table is bad or unavailable.
My question is, can I have the macro test to make sure the linked table is still valid and continue if so, or skip the update if the table is down. Any thoughts?
I only brought up the macro route as that is how the program is currently set up. I could launch any of the intial updates thru VB code based on some type of conditional check of the status of the tables upon start up. Just not sure how the code would go to validate if the table was either corrupt, or unavailable before running the updates.
One of the safest way I know (there probably are many others) consist in recreating (or refreshing) the linked tables at startup. You can have a local table storing the necessary info to recreate the links to the server tables. Here's the core of a function that creates liked tables (the variables TableName (string), SourceTableName (string), ConnectionString (string) are provided/defined somewhere else):
Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef()
tdf.Name = TableName
tdf.SourceTableName = SourceTableName
tdf.Connect = ConnectionString
If you need to locally define an Identity column, here's a possibility:
Again with TableName (string), ConstraintName (string), ColumnName (string) and dbs (DAO.Database) defined elsewhere.
If you want to check first whether the server is available or not, here's a function that returns a list of available SQL servers on a network. It could be easily adapted to check for the existence of a named server:
Public Function Get_Server_List() As String
Dim objSQLDMOApp As Object
Dim Servers As Variant
Dim strServer As String
Dim strList As String
Dim i As Integer
Set objSQLDMOApp = CreateObject("SQLDMO.Application")
Set Servers = objSQLDMOApp.ListAvailableSQLServers
For i = 1 To Servers.Count
strServer = Servers.Item(i)
If strServer = "(local)" Then strServer = Get_NT_Computer
If Len(strList) > 0 Then strList = strList & ";"
strList = strList & strServer
Set Servers = Nothing
Set objSQLDMOApp = Nothing
Get_Server_List = strList
It uses the function Get_NT_Computer which is defined as:
' In Declaration section of a module:
Public Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Public Function Get_NT_Computer() As String
Dim strComputerName As String
Dim lngComputerNameSize As Long
strComputerName = String$(255, 0)
lngComputerNameSize = Len(strComputerName)
GetComputerName strComputerName, lngComputerNameSize
strComputerName = Left$(strComputerName, lngComputerNameSize)
Get_NT_Computer = strComputerName