Yes, you can do this.
However, you can't use the ODBC provider to do this.
And you can't use the oleDB provider to do this.
(not even with GetOleDbSchemaTable)
However, the good part? You do NOT have to create a COM object of the whole access application
(and if you did, then even forms and startup code of the Access application would run - you REALLY do not want to do this).
So, you actually have to use the DAO object.
However, the re-link code is actually very simple.
It will look like this:
Imports Microsoft.Office.Interop.Access
' relink tables
Dim dbE As New Dao.DBEngine
Dim db As Dao.Database
dim strNewPath as string = "c:\Test\Rides_Be.Accdb"
db = dbE.OpenDatabase("c:Test\Test44.accDB")
' above is access database with the table links
For Each td As Dao.TableDef In db.TableDefs
If Len(td.Connect) > 0 Then
If Strings.Left(td.Connect, 9) = ";DATABASE" Then
td.Connect = ";DATABASE=" & strNewPath
td.RefreshLink()
End If
End If
Next
db.Close()
So, it is quite easy. You could also use native ADO, but I think think with the above office inter-op reference, it is a whole lot easier. In fact, the above code reads almost line for line what VBA code would read as!!
I just tested above - and while testing, I would up making this form:
All it does is just open + display the tables, their current link, and then on the bottom you select the target database you want to links to be - but really , the above code is rather simple.
I'll post a link to a skydrive zip of the above form + code, but the above code snip is all you really need here.
First up, you DO HAVE to set a reference to DAO, and we used the Office interop one, this one:
You could add the Access reference (without the .dao in above, but you need at least this one, and since we ONLY using the dao referance, then I did not bother to reference the Access full object.
Now, quite sure vb uses "late" binding for this. However, I do suggest if you can't use office 14 (Access 2010, then try a later verison such as 15, or 16 (Access 2013, or 2016).
I have the above sample as a zip file on MS skydrive, and a direct link to the zip file is this:
s!Avrwal_LV4qxhps0fwLUhwssksSPfw
Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada