BigOrangeMonkey
Programmer
I am generating linked tables via VBA, I then launch an update query to select data from another access database into an SQL database.
When I run the query I get the message "field not updatable", If I then go into the Linked Table Manager and simply refresh the external linked tables the queries will run.
Any Ideas?
Thanks,
Steve.
Here is the code I use to generate the links :
When I run the query I get the message "field not updatable", If I then go into the Linked Table Manager and simply refresh the external linked tables the queries will run.
Any Ideas?
Thanks,
Steve.
Here is the code I use to generate the links :
Code:
Sub CreateLinkedExternalTable(strTargetDB As String, _
strProviderString As String, _
strSourceTbl As String, _
strLinkTblName As String)
Dim catdb As ADOX.Catalog
Dim tblLink As ADOX.Table
Dim strsql As String
Set catdb = New ADOX.Catalog
' Open a Catalog on the database in which to create the link.
catdb.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strTargetDB
Set tblLink = New ADOX.Table
With tblLink
' Name the new Table and set its ParentCatalog property to the
' open Catalog to allow access to the Properties collection.
.Name = strLinkTblName
Set .ParentCatalog = catdb
' Set the properties to create the link.
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Link Provider String") = strProviderString
.Properties("Jet OLEDB:Remote Table Name") = strSourceTbl
.Properties("Jet OLEDB:Cache Link Name/Password") = True
End With
'Append the table to the Tables collection.
catdb.Tables.Append tblLink
Set catdb = Nothing
End Sub