Relink SQL tables in Access-CodePudding

I have the following code that is supposed to unlink all Access 2003 tables that are listed in one table and link SQL tables, but it gives error: 3265 Item not found in this collection. The table is not empty, I don't know what is the problem.

Sub LinkODBC()
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim connString As String
Dim rs As DAO.Recordset

Set db = CurrentDb()
connString = "ODBC;Driver={ODBC Driver 17 for SQL Server};Server=192.168.0.4;Database=testdbAllTables;Trusted_Connection=Yes;UID=sa;PWD=tv$akP4O30HM1TO2!9lI2z6c"
Set rs = db.OpenRecordset("SELECT Name FROM SysTrafficLinkTbls")
Do While Not rs.EOF
    ' Deletes Access table if exists
    db.TableDefs.Delete rs!Name
    ' Link table
    Set td = CurrentDb.CreateTableDef(rs!Name, dbAttachSavePWD, "dbo." & rs!Name, connString)
    db.TableDefs.Append td
    rs.MoveNext
Loop
End Sub

CodePudding user response：

Consider this segment of your code:

' Deletes Access table if exists
db.TableDefs.Delete rs!Name

The comment says to delete the table if it exists. However your code will attempt to delete a table whether or not it exists. And when the table doesn't exist, attempting to delete it triggers that 3265 error: "Item not found in this collection."

There are two approaches you can use to avoid that error.

Check whether the table exists and only attempt the delete when it does exist.
Revise the code to ignore the 3265 error.

The sample code you included in your previous question uses the first approach. It looks through the TableDefs collection to see if the table is present, and only calls db.TableDefs.Delete when the table is found.

Here's an example of the second approach.

On Error GoTo LinkODBC_Err

Set db = CurrentDb()
connString = "ODBC;Driver={ODBC Driver 17 for SQL Server};Server=192.168.0.4;Database=testdbAllTables;Trusted_Connection=Yes;UID=sa;PWD=tv$akP4O30HM1TO2!9lI2z6c"
Set rs = db.OpenRecordset("SELECT [Name] FROM SysTrafficLinkTbls")
Do While Not rs.EOF
    ' Delete Access table
    db.TableDefs.Delete rs!Name
    ' Link table
    Set td = CurrentDb.CreateTableDef(rs!Name, dbAttachSavePWD, "dbo." & rs!Name, connString)
    db.TableDefs.Append td
    rs.MoveNext
Loop

LinkODBC_Exit:
    Exit Sub

LinkODBC_Err:
    Select Case Err.Number
    Case 3265 'Item not found in this collection
        Resume Next ' ignore the error and continue on
    Case Else
        ' for any other errors, notify the user and exit
        MsgBox Err.Number & ": " & Err.Description
        Resume LinkODBC_Exit
    End Select
End Sub

CodePudding user response：

There are a few ways to do this. Here's one option.

DoCmd.TransferDatabase _
  acImport, _
  "ODBC Database", _
  "ODBC;Driver={SQL Server};Server=Fos;Database=Hermes;Trusted_Connection=Yes", _
  acTable, _
  "sourceTable", _
  "targetTable"