By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,775 Members | 2,658 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,775 IT Pros & Developers. It's quick & easy.

Dynamic Link to CSV file

100+
P: 365
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
Feb 17 '09 #1
Share this Question
Share on Google+
15 Replies


NeoPa
Expert Mod 15k+
P: 31,417
I can't find how to link a table from VBA (unfortunately). The closest I got was :
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 17 '09 #2

100+
P: 365
ill just add that the CSV file will have a header row.. if that makes any difference
Feb 17 '09 #3

NeoPa
Expert Mod 15k+
P: 31,417
@Dan2kx
Only if we can find a way to invoke the LinkTable process. Then it may ;)
Feb 17 '09 #4

100+
P: 365
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,

Expand|Select|Wrap|Line Numbers
  1. Private Sub LinkTable(psTable As String, psFromPath As String, _
  2.   psToPath As String)
  3.  
  4. Dim cnn As ADODB.Connection
  5. Dim cat As ADOX.Catalog
  6. Dim tbl As ADOX.Table
  7. Dim sShortPath As String
  8.  
  9.     'get short path name of the source database
  10.     sShortPath = Space(255)
  11.     Call GetShortPathName(psFromPath, sShortPath, 255)
  12.     sShortPath = Trim$(sShortPath)
  13.     sShortPath = Left$(sShortPath, Len(sShortPath) - 1)
  14.  
  15.     'connect to the target database
  16.     Set cnn = New ADODB.Connection
  17.     With cnn
  18.         .Provider = "Microsoft.Jet.OLEDB.4.0"
  19.         .Properties("Data Source") = psToPath
  20.         .Open
  21.     End With
  22.  
  23.     Set cat = New ADOX.Catalog
  24.     Set cat.ActiveConnection = cnn
  25.  
  26.     'link table
  27.     Set tbl = New ADOX.Table
  28.     With tbl
  29.          .Name = psTable
  30.          Set .ParentCatalog = cat
  31.          .Properties("Jet OLEDB:Create Link") = True
  32.          .Properties("Jet OLEDB:Link Datasource") = sShortPath
  33.          .Properties("Jet OLEDB:Remote Table Name") = psTable
  34.  
  35.          On Error Resume Next
  36.          cat.Tables.Delete psTable
  37.          On Error GoTo 0
  38.  
  39.          cat.Tables.Append tbl
  40.     End With
  41.     Set tbl = Nothing
  42.  
  43.     'release references
  44.     cnn.Close
  45.     Set cnn = Nothing
  46.  
  47.     Set cat = Nothing
  48.  
  49. End Sub
mean anything to anyone?
Cheers
Dan
Feb 17 '09 #5

NeoPa
Expert Mod 15k+
P: 31,417
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.
Feb 17 '09 #6

100+
P: 365
Just says
Comple Error:

User-defined type not defined

Think you are right doesnt like any of the ADOX items
Feb 17 '09 #7

NeoPa
Expert Mod 15k+
P: 31,417
Have you got a reference set up to Microsoft ADOX (This would be necessary for it to work of course)?
Feb 17 '09 #8

100+
P: 365
nope, how is that done?
Feb 17 '09 #9

NeoPa
Expert Mod 15k+
P: 31,417
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.
Feb 17 '09 #10

100+
P: 365
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
Feb 17 '09 #11

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

Dan
Feb 17 '09 #12

100+
P: 365
Error message says it needs a TableID

OK, back to the drawing board
*sigh*
Feb 17 '09 #13

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

Expand|Select|Wrap|Line Numbers
  1. 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
Feb 18 '09 #14

NeoPa
Expert Mod 15k+
P: 31,417
Sorry Dan. I don't know the code for linking tables.
Feb 18 '09 #15

100+
P: 365
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
Feb 18 '09 #16

Post your reply

Sign in to post your reply or Sign up for a free account.