Update tables linked in Access using VB.NET

Question

I have an Access DB that has linked tables to other access DBs and links to Excel spreadsheets.
I would like to scan the links, using vb.net, and validate that they are pointing to the current location of the files.
If not I want to change the link location.

Is this possible?
Is there a particular key word search I should be using to locate info on this so far my search have not turned up anything useful for this.

Answer 1

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

Answer 2

Thanks this is most of what I needed. The code snippet does not return linked excel sheets, so I will have to dig into the DAO object see what can be done for those.

Answer 3

Ahh, just need to use a different comparison string...