How do I append a relation between, two linked tables, to a database in VBA?
I can do this fine manually (using the relations table) but I get the following error at the line
"Run-time error 3057: Operation not supported on linked tables"
I have two tables that are linked to excel spreadsheets. The location of these spreadsheets may change and rather that have
the user go in and manually change the connection, I would rather have them change it using a form.
In the code below I get the spreadsheet location from a textbox, create a new table (as the connect property of the original
is read only), delete the original relation and table and append the new table. The relation append will not work though. I'm
using Access '07.
Private Sub btnLinkXls1_Click()
Dim tblSpreadsheet1 As Object
Dim connectString As String
Dim sourceTblName As String
Dim tdfNew As TableDef
Dim rel As relation
Dim fld As Field
'Note - The connection string is in the following format:
'Get the value entered into the text box
connectString = txtXls1Location.Text
'Build the connection string
connectString = "Excel 8.0;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" + connectString + ";TABLE=Sheet1$"
'Create new table as property is read-only and cannot be modified
'Get original SourceTableName
sourceTblName = CurrentDb.TableDefs("Spreadsheet1").SourceTableName
'Copy existing table and relation
Set tdfNew = CurrentDb.CreateTableDef("Spreadsheet1")
Set rel = CurrentDb.CreateRelation("Spreadsheet1Spreadsheet2", "Spreadsheet1", "Spreadsheet2")
Set fld = rel.CreateField("orderID")
fld.ForeignName = "order number"
'Change the connect property and source worksheet in the copy
tdfNew.Connect = connectString
tdfNew.SourceTableName = sourceTblName
'Delete original table and relation, relation first
DoCmd.DeleteObject acTable, "Spreadsheet1"
'Create the new table
'Add table relations
CurrentDb.Relations.Append rel '******ERROR OCCURS HERE*****
Thanks for any help.