question

DonGlover-0631 avatar image
0 Votes"
DonGlover-0631 asked AlbertKallal-4360 commented

Update tables linked in Access using VB.NET

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.

dotnet-visual-basicoffice-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertKallal-4360 avatar image
0 Votes"
AlbertKallal-4360 answered AlbertKallal-4360 edited

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:

140968-image.png

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:

140960-image.png

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



image.png (20.8 KiB)
image.png (7.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DonGlover-0631 avatar image
0 Votes"
DonGlover-0631 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DonGlover-0631 avatar image
0 Votes"
DonGlover-0631 answered AlbertKallal-4360 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes, you can test/check if the connection starts with the word Excel. They typical look like this:

Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\test3\thot.xlsx

So, you again, can link as per above. So, that example I posted could display say Excel in the list, and you could change just the file name. I would probably grab a existing and known working link, since there are a few other options such as "use header from Excel (HDR=YES) and a few more options.

You can get a listing of all files and the type of link using GetoleDBSchema method, and thus would not need a dao reference, but that method does not give a means to update the table links. Both ADDO and DAO can also get that information, but DAO code looks to be the most simple.

0 Votes 0 ·