Update tables linked in Access using VB.NET

Don Glover 511 Reputation points
2021-10-16T22:11:13.52+00:00

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.

VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
1,832 questions
Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
729 questions
No comments
{count} votes

Accepted answer
  1. Albert Kallal 3,451 Reputation points
    2021-10-17T05:02:37.883+00:00

    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

    No comments

2 additional answers

Sort by: Most helpful
  1. Don Glover 511 Reputation points
    2021-10-17T15:09:43.367+00:00

    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.

    No comments

  2. Don Glover 511 Reputation points
    2021-10-17T15:22:51.2+00:00

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