Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003

    Red face Unanswered: Relink SQL Server tables

    I have and Access 2000 front end running a SQL Server back end.
    I periodically call a SP in SQL Server from VBA that rebuilds one of
    my tables. My question is:
    How do I refresh the link in my front end via VBA?
    It's simple to do manually with the linked table manager, but I can't
    find out how to do it with VBA.
    Thanks in advance!!

  2. #2
    Join Date
    Mar 2003
    I know this works in 97, but i have not tested it in 2000 or 2002. I think it should be ok. If not, it's somewhere to start:

    This displays the current table being reattached in the form, but it is not necessary, so can be taken out.

    ' A Function to reattach tables to a database
    ' Returns True on successful completion, else False.
    Function ReattachTables() As Integer
    Dim CurrDB As Database, oTbl As TableDef, ofrmInfo As Form, oAllTables As Collection
    Dim TblName As String, TblConnection As String, TblSrc As String, ret As Integer
    Dim TodaysDate As String

    On Error GoTo Err_ReattachTables

    Set CurrDB = OpenDatabase(currentdb.Name)
    DoCmd.OpenForm "sysfrmReattachInfo"

    For Each oTbl In CurrDB.TableDefs
    If InStr(1, oTbl.Connect, "ODBC") > 0 Then
    TblName = oTbl.Name
    Forms!sysfrmReattachInfo!TableName = TblName
    TblConnection = oTbl.Connect
    TblSrc = oTbl.SourceTableName
    CurrDB.TableDefs.Delete TblName


    Set oTbl = CurrDB.CreateTableDef(TblName)
    oTbl.Connect = TblConnection
    oTbl.SourceTableName = TblSrc
    CurrDB.TableDefs.Append oTbl
    End If
    DoCmd.Close acTable, "sysfrmReattachInfo"

    ret = SetINIFile("", "LastReattached", TodaysDate)
    ReattachTables = True
    DoCmd.Close acForm, "sysfrmReattachInfo"
    Exit Function

    If Err = 3011 Then
    MsgBox "Error: A Table has not refreshed properly - this may be due to it being deleted on the SQL Server or some other problem may have occurred (If this continues, contact your database administrator).", 48, "Phoenix Startup"
    ret = MsgBox("Do you wish to continue ?", 4, "Phoenix Startup")
    If ret = 6 Then
    Resume Next
    Resume Exit_ReattachTables
    End If
    MsgBox "Error: (" + CStr(Err) + ") - " + Error$, 48, "Reattach Tables"
    ReattachTables = False
    Resume Exit_ReattachTables
    End If

    End Function

Posting Permissions

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