Dynamic Link to CSV file

[Dan2kx]

Good morn all,

i want to be able to link periodically to a CSV file so that i can extract some data, i imagine that i want to establish a link, query the data and then delete the link once completed, how would i do this from VB?

a direct query would surfice if that can be done?

any ideas on the initial code?

Dan

[NeoPa]

I can't find how to link a table from VBA (unfortunately). The closest I got was :

Call DoCmd.RunCommand(acCmdLinkTables)

I'm pretty sure this is not an adequate answer, and I could find any further explanations within the Help system.

I'll continue to monitor as I'm interested in seeing an answer to this one.

[Dan2kx]

ill just add that the CSV file will have a header row.. if that makes any difference

[NeoPa]

@Dan2kx
Only if we can find a way to invoke the LinkTable process. Then it may ;)

[Dan2kx]

Hello, i found this on the net, couldnt get it to run past the second Dim, i editted my paths in, this is the original,

Private Sub LinkTable(psTable As String, psFromPath As String, _
  psToPath As String)
 
Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim sShortPath As String
 
    'get short path name of the source database
    sShortPath = Space(255)
    Call GetShortPathName(psFromPath, sShortPath, 255)
    sShortPath = Trim$(sShortPath)
    sShortPath = Left$(sShortPath, Len(sShortPath) - 1)
 
    'connect to the target database
    Set cnn = New ADODB.Connection
    With cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Data Source") = psToPath
        .Open
    End With
 
    Set cat = New ADOX.Catalog
    Set cat.ActiveConnection = cnn
 
    'link table
    Set tbl = New ADOX.Table
    With tbl
         .Name = psTable
         Set .ParentCatalog = cat
         .Properties("Jet OLEDB:Create Link") = True
         .Properties("Jet OLEDB:Link Datasource") = sShortPath
         .Properties("Jet OLEDB:Remote Table Name") = psTable
 
         On Error Resume Next
         cat.Tables.Delete psTable
         On Error GoTo 0
 
         cat.Tables.Append tbl
    End With
    Set tbl = Nothing
 
    'release references
    cnn.Close
    Set cnn = Nothing
 
    Set cat = Nothing
 
End Sub

mean anything to anyone?
Cheers
Dan

[NeoPa]

So, it failed on line #5. What was the error message?

PS. I suspect it's related to the ADOX, but lets get the info to work with first.

[Dan2kx]

Just says
Comple Error:

User-defined type not defined

Think you are right doesnt like any of the ADOX items

[NeoPa]

Have you got a reference set up to Microsoft ADOX (This would be necessary for it to work of course)?

[Dan2kx]

nope, how is that done?

[NeoPa]

Open up your project in Access, then take the following steps :

1. Alt-F11 to open and switch to the VBA window.
2. Select Tools / References... to see and select the references.
3. Review the ticked ones at the top.
4. If Microsoft ADOX is not there then scroll down the list until you find it (in my 2003 installation it is Microsoft ADO Ext. 2.8 for DDL and Security).
5. Click in the box to select it.

Now try recompiling the code and see where you get.

[Dan2kx]

OK got it, sort of, line 39 says table already exists, i think it must be creaing in the source DB (which cant be right can it?) and line 36 deleted my table, thankfully its not the live DB, phew

Dan

[Dan2kx]

My bad, had the other wrong path in line 19, it seems to work, not tried a CSV yet.....

Dan

[Dan2kx]

Error message says it needs a TableID

OK, back to the drawing board
*sigh*

[Dan2kx]

OK, i give up, i found an alternative using make table SQL to use in VB, i can easily delete this table after processing:

SELECT * INTO " & Input1 & " FROM [Text;DATABASE=H:\Bin\;HDR=Yes]." & Input1 & ".csv;

would still be interested if anyone can resolve my OP, but for now i have my problem "solved".

Thanks to all,
Dan

[NeoPa]

Sorry Dan. I don't know the code for linking tables.

[Dan2kx]

its ok NeoPa, you have helped me enough already, the code i found on the net (and posted) does successfully links tables from another DB, which may present a use for me in the future, and anyone else on the forum of course, it just doesnt work for the CSV/TXT file problemo.

Thanks again

Dan