A quicker way to import CSV text file in ADO??

[DamoOz]

Help

I have a CSV file that I need to import to access db using ADO. This was originally done in VBA by using :

DoCmd.TransferDatabase acImport, "Microsoft Access", MelDatabaseLocation, acTable, InTableName, DestTablename

I need to do the same import in ADO. I have opened the CSV file line by line and used the Addnew method but this is far too slow as the file is large.

There must be some quicker way to import a csv file using ADO.

Please help as I a VERY STUCK

DamoOz

 

[MatsHulten]

I belive it is better to run this import from within the current DB, and not use ADO. I don't know of any way to open a CSV in the way you want in ADO (you may want to try the regular open command, but I suspect that only will return a stream object), but then again someone out there may know of a way.

But as I said, try it from within Access via an automation object.

Good Luck
-Mats

 

[DamoOz]

Thanks Matt

The problem is that I want to move away from calling access and create a VB front end with Access as the back end DB. At the moment when I need to import the CSV file I call access from my VB program and automatically run an access VBA module to do the import. This is what I want to get away from and just do this import in VB code only. I can do this in VBA but I dont know, and can't find, how to do this in ADO.

Do you know of a way to do this in VB code only without calling an Access module. Maybe I should use DAO?????

I appreciate your help.

DamoOz

 

[MatsHulten]

I don't think there is a quicker way than reading it row by row. You could try the save method of a recordset and se what type of file it generates (xml if I'm not mistaken), then try to convert your csv to that format, and use the load method (or open) to populate the recordset from the file.
However, I doubt it would be quicker to convert the file to xml before opening it, than it is loading it row by row.

I don't know very much of DAO, so I can't tell if it would be possible to do a load that way.

Let me know if you find a solution!

Good Luck
-Mats