Re: Relinking a CSV file using code

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
    ... 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: Relinking a CSV file using code
    ... Access wouldn't have had;Database if you were linked to a CSV. ... I have 5 CSV files that are linked into the FE. ... >>> Dim tdf As TableDef ...
    (microsoft.public.access.modulesdaovba)
  • Re: Standard Method for Importing/Exporting to DB
    ... the database name, server name, and filename+fullpath. ... and also exporting to CSV files. ... everything from DTS packages to Crystal Reports are used. ...
    (microsoft.public.dotnet.languages.vb)
  • RE: Automating Importing of CSV Files with dynamic feilds
    ... Dave Hargis, Microsoft Access MVP ... I need to automate the importing of three CSV files (along with feild names ... the number of feilds in each file will vary/change with each import so I need ...
    (microsoft.public.access.modulesdaovba)
Loading