Re: Relinking a CSV file using code

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Amanda Payton" <AmandaPayton@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:666A3471-ADC4-4215-9A73-23B09860F2C5@xxxxxxxxxxxxxxxx
> 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
>
> "Douglas J. Steele" wrote:
>
>> Link through the GUI, and look at the TableDef object's Connect property
>> to
>> see what it's expecting.
>>
>>
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>>
>> "Amanda Payton" <AmandaPayton@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:E3E645DC-C326-4989-9B41-1D51653941C6@xxxxxxxxxxxxxxxx
>> >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
>>
>>
>>


.

Relevant Pages

  • Relinking a CSV file using code
    ... I have a FE/BE database setup, with several tables linked between the two. ... I have 5 CSV files that are linked into the FE. ... Dim tdf As TableDef ... Set tdf = db.TableDef ...
    (microsoft.public.access.modulesdaovba)
  • Re: Relinking a CSV file using code
    ... Doug Steele, Microsoft Access MVP ... >I have a FE/BE database setup, with several tables linked between the two. ... I have 5 CSV files that are linked into the FE. ... > Dim tdf As TableDef ...
    (microsoft.public.access.modulesdaovba)
  • Re: Relinking a CSV file using code
    ... there a decompiled version of the Linked Table Manager out there somewhere? ... >>I have a FE/BE database setup, with several tables linked between the two. ... I have 5 CSV files that are linked into the FE. ... >> Dim tdf As TableDef ...
    (microsoft.public.access.modulesdaovba)
  • Re: How do I import DOS files into Excel?
    ... The old database is running on a machine. ... to try the CSV thing. ... data from the old program into a common format, ... John Nurick [Microsoft Access MVP] ...
    (microsoft.public.access.externaldata)
  • Re: Synchronize Treo650 palm cellphone pda with flat ASCII csv text file
    ... To sync, you need at least 3 more items in your text csv database: ... An exact mapping from text csv field to Palm address record field. ...
    (comp.sys.palmtops.pilot)