Importing CSV file to Access using TransferText in VB 2

[damotwani]

Hi,

I am trying to import a CSV file using TransferText in VB. I am getting this error:

Run Time Error:
Object Required

when I try to execute this code:

Private Sub Next_Click()
Dim cnn As New ADODB.Connection

'Open the Connection

cnn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\V Care\Project\bhav1.mdb;Persist Security Info=False&quot

appAccess.DoCmd.TransferText acImportDelim, "Cmbhav Import Specification", "cmbhav", "C:\cm3JUNbhav.csv", True

End Sub



I have no idea why this is happening. what am I doing wrong? How can I correct this?


Any help will be appreciated.

Thanks,

D

 

[williamu]

Hi,

Don't you have to use the Microsft Text Driver to read a CSV file? By using the Access (Jet) Driver you're implying that the data source is an Access DB. Even the extension used implies this.




William
Software Engineer
ICQ No. 56047340

 

[damotwani]

Hi,

Thank you for responding william.

I am new to VB and am not familiar with what you are talking about. I want to Import the CSV file to a table in Access. So I tried openning a connection to the database and using TransferText.

What should I do to resolve this?

Thanks,

D

 

[williamu]

Hi,

Off the top of my head, something like:

Private objConn As ADODB.Connection

Private Sub LoadDB()
Dim strCSV As String, strSQL as String
strCSV = "c:\path\datafile.csv"
Set objConn = New ADODB.Connection
With objConn
.CursorLocation = adUseClient
.ConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)}; Initial Catalog=" & strCSV & ";"
.Open
End With

strSQL = "SELECT * INTO Table_Name IN 'yourdatabase.mdb' FROM " & strCSV
objConn.Execute (strSQL)
objConn.Close
End Sub

It may not be syntactically correct but the principle is sound.



William
Software Engineer
ICQ No. 56047340

 

[CCLINT]

>By using the Access (Jet) Driver you're implying that the data source is an Access DB

No. The JET Driver is capable of opening many different ISAM data sources, and not necessarily just a JET MDB.
Actually, it seems to work much better than the MS Text driver.

 

[williamu]

CCLINT,

Thanks for keeping me straight here that's sommit else I've learned today.



William
Software Engineer
ICQ No. 56047340

 

[damotwani]

Hi,

Thanks William and CClint for your insights!

I am now able to import a csv file to a table in Access. But the code that William has provided above, only allows the csv file specified by strCSV to be imported to a new table in the Access database.

However I need it to work a little differently.

Basically the user selects the file via the common Windows Open File Dialog Box and this file gets saved in a TEXTBOX. The code above imports the selected csv file (textbox.text) to a table in Access.

But each time (at the end of each day probably) the VB app is run, the user will select a different csv file, which must then be imported into Access. The code provided by William can not be used then since it gives an error saying that the table already exists.

Is there a way to import the csv file into an existing table (so that it replaces the previous contents of the table, instead of appending the new records at the end of the table) ?

If not, is there a way to use the same code above, but add something so that after the csv file has been imported, the records from the table in Access are moved to another table and the first table is deleted so that one doesn't get "table already exists error"?

I hope you can help me with this. If you need clarification about something, please let me know.

William and CClint thanks again for the help. You have my star for it!

D

 

[CCLINT]

Execute a "DROP SomeTable" DDL statement off of the Connection object first, and use an error catcher incase the table doesn't exist.

 

[williamu]

Hi,

You should be able to create a Command Object and issue a DROP TABLE Statement using the Execute method. Then import your data as before.

HTH


William
Software Engineer
ICQ No. 56047340