updating references with VBA (1 Viewer)

[Banista]

hello everyone,
I'm trying to write a VBA module that changes referenced tables to different DBs in different folders, and takes the data for those references from a table.

The main code is this part:

Do Until RS_References.EOF
For i = 0 To db.TableDefs.Count - 1
    If db.TableDefs(i).Name = RS_References!Name And RS_References!Create_Reference = True Then
       db.TableDefs(i).Connect = ";DATABASE=" & RS_References!New_Country_DB
       db.TableDefs(i).SourceTableName = RS_References!New_Country_Table
       db.TableDefs(i).RefreshLink
    End If
Next i

The .connect line works great, and exactly how I want it to. However, I seem to be unable to change the SourceTableName, as that line gives me Error 3268: Property cannot be changed if it is part of a list (roughly translated).

Therefore, I can update the .connect line and connect to new DBs by deleting the SourceTableName line, but the new source table needs to have the same name as the old one.

Any ideas how I can update a TableDef.SourceTableName property in such a manner?

thanks in advance
Bani

 

[Guus2005]

Not changing references but refreshing table links.

You can drop the table altogether and recreate it using a different sourcetablename
from the Access helpfiles:

Sub ConnectOutput(dbsTemp As Database, _
   strTable As String, strConnect As String, _
   strSourceTable As String)

   Dim tdfLinked As TableDef
   Dim rstLinked As Recordset
   Dim intTemp As Integer

   ' Create a new TableDef, set its Connect and
   ' SourceTableName properties based on the passed
   ' arguments, and append it to the TableDefs collection.
   Set tdfLinked = dbsTemp.CreateTableDef(strTable)

   tdfLinked.Connect = strConnect
   tdfLinked.SourceTableName = strSourceTable
   dbsTemp.TableDefs.Append tdfLinked

   Set rstLinked = dbsTemp.OpenRecordset(strTable)

   Debug.Print "Data from linked table:"

   ' Display the first three records of the linked table.
   intTemp = 1
   With rstLinked
      Do While Not .EOF And intTemp <= 3
         Debug.Print , .Fields(0), .Fields(1)
         intTemp = intTemp + 1
         .MoveNext
      Loop
      If Not .EOF Then Debug.Print , "[additional records]"
      .Close
   End With

   ' Delete the linked table because this is a demonstration.
   dbsTemp.TableDefs.Delete strTable

End Sub

 

[Banista]

dirty, but it works, thanks a lot