Relinking a CSV file using code

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
 
D

Douglas J. Steele

Link through the GUI, and look at the TableDef object's Connect property to
see what it's expecting.
 
G

Guest

Doug -

I created the original table links using the GUI - and I looked at the
connect property to see what it expects - all I changed was the file path
following the ";Database = " and Access rejected it (using '97 BTW)...

The only way I personally have successfully linked to a CSV file is using
the "Make Table -> Linked Table" off the Access Database interface, and the
only way I have successfully changed the link is using the Linked Table
Manager interface. I'm wondering what those two do that I'm missing... Is
there a decompiled version of the Linked Table Manager out there somewhere?

-Amanda
 
D

Douglas J. Steele

Access wouldn't have had ;Database if you were linked to a CSV. It would
have had something like:
Text;DSN=CSVData Link
Specification;FMT=Delimited;HDR=NO;IMEX=2;DATABASE=D:\Documents and
Settings\DJSteele\My Documents\My Office Documents\Office.97\Access.97 (and
the SourceTableName property would be the actual file name)

Note that you can use the TransferText method to create linked tables.
 
G

Guest

Doug -

You're right - It's not ";Database=..." alone... BUT within the whole string
"text;DSN=..." there is a segment containing "...;Database=..." I didn't
clarify that well enough... that section of the string was all I was making
changes to. I'd copied the first half of the string with the other
parameters, and was splicing that together with a new filepath after the
";Database=" marker. ALL that changed from the old connect string to the new
one was the filepath. The Actual FileName won't change (the 5 files I want
to link will ALWAYS have the same nomenclature, regardless of location) Yet
for whatever reason, when I use the TableDef.RefreshLink command to update
the table, I get the error I mentioned earlier. THAT's what's confusing me.

I'm reasonably adept at Access and VB/VBA - my brain's logic says this
should work, everything I've gleaned off of the Acc97 Help says it should
work - and yet it isn't. So something's missing...
 
D

Douglas J. Steele

I'm not 100% certain, as I've never change connections for text files like
this, but I think the problem may be related to the DSN=CSVData Link
Specification part in my example. That name was generated by Access when I
linked the table, and is based on the name of the file being imported. If
you change the file, I think that DSN is now inconsistent.

Take a look at using a Schema.ini file in conjunction with the TransferText
method to link your tables.

http://msdn.microsoft.com/library/en-us/off2000/html/acconAboutSpecsSchemaFiles.asp
http://support.microsoft.com/?id=210001
http://support.microsoft.com/?id=210073
 
G

Guest

Rats! :-(

I had a nice spiffy post all written up with what I found - and Microsoft
lost it... and I don't remember what I wrote!

Doug - the long and the short of it that I've picked up is this - The DSN's
are VERY picky about being associated with a table... Why - I'm not sure...
I'm happy chalking it up to a MS quirk.

I opened a new database and was able to create a custom function that took
variables for the filepath, prefix (anything that comes before ";Database" in
the connect string), the name you want to give the table, and the table/file
name, and created a linked table.

Same for a custom function that relinked an existing table to a new location
(omitting the table/file name variable).

I now need to incorporate these functions (now that I KNOW they work) back
into my existing database relink procedure.

Incidentally, for anyone who has multiple Link Specs in use, you can read
those out of the MSysObjects table (DLookup() for example) so you have the
right connect string prefix associated with the right table at relink.

Doug - THANK YOU for your time and willingness to listen. It's VERY much
appreciated!

-Amanda
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top