Hello,
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
"CurrentDB.Relations.Append rel"
"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.
Code:
Thanks for any help.
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
"CurrentDB.Relations.Append rel"
"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.
Code:
Code:
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:
'"Excel 8.0;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=connectString;TABLE=Sheet1$"
'Get the value entered into the text box
txtXls1Location.SetFocus
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"
rel.Fields.Append fld
'Change the connect property and source worksheet in the copy
tdfNew.Connect = connectString
tdfNew.SourceTableName = sourceTblName
'Delete original table and relation, relation first
CurrentDb.Relations.Delete "Spreadsheet1Spreadsheet2"
DoCmd.DeleteObject acTable, "Spreadsheet1"
'Create the new table
CurrentDb.TableDefs.Append tdfNew
'Add table relations
CurrentDb.Relations.Append rel '******ERROR OCCURS HERE*****
'Refresh!
CurrentDb.TableDefs.Refresh
End Sub
Thanks for any help.