Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    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
    Location
    UK
    Posts
    71
    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
    DoEvents
    TblConnection = oTbl.Connect
    TblSrc = oTbl.SourceTableName
    CurrDB.TableDefs.Delete TblName

    CurrDB.TableDefs.Refresh

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

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

    Err_ReattachTables:
    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
    Else
    Resume Exit_ReattachTables
    End If
    Else
    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
  •