Results 1 to 4 of 4

Thread: Linked Tables

  1. #1
    Join Date
    Dec 2005
    Posts
    121

    Unanswered: Linked Tables

    All,
    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?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Macros are not well suited for performing this kind of job as they don't have any error checking/trapping mechanism (Acc1 to Acc2003) or offer only a very limited one (Acc2007/2010).
    Have a nice day!

  3. #3
    Join Date
    Dec 2005
    Posts
    121
    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.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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):
    Code:
        
        Set dbs = CurrentDb
        Set tdf = dbs.CreateTableDef()
        tdf.Name = TableName
        tdf.SourceTableName = SourceTableName
        tdf.Connect = ConnectionString
        dbs.TableDefs.Append tdf
    If you need to locally define an Identity column, here's a possibility:
    Code:
        strSQL = "ALTER TABLE TableName ADD CONSTRAINT ConstraintName UNIQUE (ColumnName);"
        dbs.Execute strSQL, dbFailOnError
    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:
    Code:
    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
        Next
        Set Servers = Nothing
        Set objSQLDMOApp = Nothing
        Get_Server_List = strList
    
    End Function
    It uses the function Get_NT_Computer which is defined as:
    Code:
    ' 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
    
    End Function
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •