G
Guest
I have a FE/BE database setup, with several tables linked between the two.
In addition, I have 5 CSV files that are linked into the FE.
I am trying to create a relink sequence to cover cases where the database is
moved from one location to another. I can relink the tables between the
databases just fine with:
Function RefreshLinks(strFileName As String) As Boolean
Dim dbs As Database
Dim tdf As TableDef
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & strFileName
On Error Resume Next
Err = 0
tdf.RefreshLink
If Err <> 0 then
RefreshLinks = False
Exit Function
End If
End If
Next tdf
RefreshLinks = True
End Function
HOWEVER - This format doesn't work for CSV files.
I figured out that I can add a Select Case statement after the
Len(tdf.connect) > 0, to check the name of the table, (or some other
attribute to see if the table is linked to a CSV or MDB file), and then carry
out different actions for each case. What I need to know is what the strings
should look like.
I've tried:
Set tdf = db.TableDef("TempCSV")
tdf.connect = "Text;database=C:\Filepath"
tdf.RefreshLink
and I've tried:
Set tdf = db.TableDef("TempCSV")
tdf.connect = "Text;DSN=My Link Specification;FMT=Delimited;HDR=NO;IMEX=2;
tdf.RefreshLink
Both times I get "Runtime Error '3001' Invalid Arguments" when I get to the
RefreshLink line.
I'm OK with deleting the existing tables and creating a new table linked to
the file in the correct location - but I get the same errors when I try:
Set tdf = db.CreateTableDef("TempCSV")
tdf.connect = "Text;Database=C:\FilePath"
tdf.SourceTableName = "TempCSV.csv"
db.TableDefs.Append tdf
at the append line.
Anyone run into this problem before? Any idea what I should be using? What
does the Linked Table Manager do with the strings, since I can relink these
CSV files just fine using that?
I plan on locking the main menubar down before deployment, and providing
limited, customized menubars, so my end user will NOT have access to the
Linked Table manager.
Thanks!
Amanda
In addition, I have 5 CSV files that are linked into the FE.
I am trying to create a relink sequence to cover cases where the database is
moved from one location to another. I can relink the tables between the
databases just fine with:
Function RefreshLinks(strFileName As String) As Boolean
Dim dbs As Database
Dim tdf As TableDef
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & strFileName
On Error Resume Next
Err = 0
tdf.RefreshLink
If Err <> 0 then
RefreshLinks = False
Exit Function
End If
End If
Next tdf
RefreshLinks = True
End Function
HOWEVER - This format doesn't work for CSV files.
I figured out that I can add a Select Case statement after the
Len(tdf.connect) > 0, to check the name of the table, (or some other
attribute to see if the table is linked to a CSV or MDB file), and then carry
out different actions for each case. What I need to know is what the strings
should look like.
I've tried:
Set tdf = db.TableDef("TempCSV")
tdf.connect = "Text;database=C:\Filepath"
tdf.RefreshLink
and I've tried:
Set tdf = db.TableDef("TempCSV")
tdf.connect = "Text;DSN=My Link Specification;FMT=Delimited;HDR=NO;IMEX=2;
tdf.RefreshLink
Both times I get "Runtime Error '3001' Invalid Arguments" when I get to the
RefreshLink line.
I'm OK with deleting the existing tables and creating a new table linked to
the file in the correct location - but I get the same errors when I try:
Set tdf = db.CreateTableDef("TempCSV")
tdf.connect = "Text;Database=C:\FilePath"
tdf.SourceTableName = "TempCSV.csv"
db.TableDefs.Append tdf
at the append line.
Anyone run into this problem before? Any idea what I should be using? What
does the Linked Table Manager do with the strings, since I can relink these
CSV files just fine using that?
I plan on locking the main menubar down before deployment, and providing
limited, customized menubars, so my end user will NOT have access to the
Linked Table manager.
Thanks!
Amanda