Change connection string of linked table

[RichardMGreen]

Hi all

I want to change the filepath of a linked table in VBA.
So far I've got this:-

Sub test()
    Dim tbl As TableDef
    For Each tbl In TableDefs
        If Len(tbl.Connect) > 0 Then
            
    
End Sub

As far as I can make out, checking the Connect property tells me if it's a linked table, but I'm not sure how to change just the path part.

Can someone point me in the right direction please?

::edit::
I also found this code which will change the entire string:-
td.Connect = "MS Access;Database=C:\MyBackEnd.mdb;"
td.RefreshLink
(this is changing a table linked to another Access file but I'm linking to CSV files)
but do I need to do a refreshlink for each table individually or can I do them all at once at the end of the loop?

 

[RichardMGreen]

OK, I'm making a bit of progress.
I've got the connection string for one of the files which is:-
Text;DSN=ASMT Link Specification2;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=850;DATABASE=H:\Data\Databases\extract

and it's this bit:-
H:\Data\Databases\extract

I want to change.

It looks like the rest of it stays the same apart from the DSN section.

How can update just the filepath within the string?