I have created a module in access 2007 that will update linked tables, but I wanted to run this module from vb6. I have tried this code from Microsoft, but it didnt work.
Sub AccessTest1()
Dim A As Object
Set A = CreateObject("Access.Application")
A.Visible = False
A.OpenCurrentDatabase (App.Path & "/DataBase/acc.accdb")
A.DoCmd.RunMacro "RefreshLinks"
End Sub
What I am aiming to do, is to allow my program to update all linked tables to new links, in case the program has been used on other computer
In case you want to take a look at the module program, here it is:
Sub CreateLinkedJetTable()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New ADOX.Catalog
' Open the catalog.
cat.ActiveConnection = CurrentProject.Connection
Set tbl = New ADOX.Table
' Create the new table.
tbl.Name = "Companies"
Set tbl.ParentCatalog = cat
' Set the properties to create the link.
tbl.Properties("Jet OLEDB:Link Datasource") = CurrentProject.Path & "/db3.mdb"
tbl.Properties("Jet OLEDB:Remote Table Name") = "Companies"
tbl.Properties("Jet OLEDB:Create Link") = True
' To link a table with a database password set the Link Provider String
' tbl.Properties("Jet OLEDB:Link Provider String") = "MS Access;PWD=Admin;"
' Append the table to the tables collection.
cat.Tables.Append tbl
Set cat = Nothing
End Sub
Sub RefreshLinks()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New ADOX.Catalog
' Open the catalog.
cat.ActiveConnection = CurrentProject.Connection
Set tbl = New ADOX.Table
For Each tbl In cat.Tables
' Verify that the table is a linked table.
If tbl.Type = "LINK" Then
tbl.Properties("Jet OLEDB:Link Datasource") = CurrentProject.Path & "/db3.mdb"
' To refresh a linked table with a database password set the Link Provider String
'tbl.Properties("Jet OLEDB:Link Provider String") = "MS Access;PWD=Admin;"
End If
Next
End Sub
