Change the source of a link table

[k0r54]

Hi,

I have a folder with CSV files in. I have a table ("tableA") which links to one of the CSV files, to be more persice oct_05.csv.

Now how can i get vba to when i run the function change the link of tableA to nov_05.

I know how to work out the month and ect.. the only thing holding me back is how to change the actuall link in vba

Any ideas?

Thanks
k0r54

 

[RuralGuy]

A favor please, go into the immediate window (^G) and type the following:
Debug.Print CurrentDb.TableDefs("tableA").Connect
Debug.Print CurrentDb.TableDefs("tableA").SourceTableName
and post back what it prints out.

 

[k0r54]

A favor please, go into the immediate window (^G) and type the following:
Debug.Print CurrentDb.TableDefs("tableA").Connect
Debug.Print CurrentDb.TableDefs("tableA").SourceTableName
and post back what it prints out.

formated_monthly_cleansed_oct_05.csv

Thanks
k0r54

 

[allan57]

RuralGuy, the following example shows how to change a linked table connection.

Dim dbs As Database
Dim tdf As TableDef

On Error Resume Next

Set dbs = CurrentDb
dbs.TableDefs.Refresh

For Each tdf In dbs.TableDefs
With tdf
If Len(.Connect) > 0 Then
If .Connect = ";DATABASE=c:\formated_monthly_cleansed_oct_05.csv" Then
.Connect =";DATABASE=c:\formated_monthly_cleansed_nov_05.csv"
.RefreshLink
End If
End If
End With
Next

Set tdf = Nothing
dbs.Close
Set dbs = Nothing

Hope this points you in the right direction

Allan

 

[k0r54]

Sorry to be a pain but.... is some comments possible so i understand it :s

Thanks for all your help
k0r54

 

[k0r54]

Hi all,

Ok i have modified the code a little and come up with this: -

Set dbs = CurrentDb
dbs.TableDefs.Refresh

For Each tdf In dbs.TableDefs
  With tdf
    If Len(.connect) > 0 Then
      If .Name = "tableA" Then
        .connect = "Text;DSN=formated_monthly_cleansed;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=437;DATABASE=C:\Documents and Settings\k0r54\My Documents\Below 90\Reports\Pre_formated_monthly_cleansed_data\formated_monthly_cleansed_oct_05.csv"
        .RefreshLink
      End If
    End If
  End With
Next

Set tdf = Nothing
dbs.Close
Set dbs = Nothing

It works and i can msgbox inside the tableA if and it is fine but...
I cannot seem to change the link. I get errors when i try anything. If i removed the beginin bit (up until ;DATABASE) it says it is not recognised. If i leave it in. It says there the file cannot be found and it is there.

Thanks
k0r54

 

[k0r54]

what i have just noticed that is very weird, even when i change the link through the link manager. It does not change ?????