Relinking a CSV file using code
- From: "Amanda Payton" <AmandaPayton@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 1 Jun 2005 13:57:01 -0700
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
.
- Follow-Ups:
- Re: Relinking a CSV file using code
- From: Douglas J. Steele
- Re: Relinking a CSV file using code
- Prev by Date: Re: Execute Delete Where clause
- Next by Date: Re: How to Rid Go To Code of a Filter
- Previous by thread: Can access DAO in Access 2003
- Next by thread: Re: Relinking a CSV file using code
- Index(es):
Relevant Pages
|
