MS Access inserting data using VBA, Run-time error 3061

Question

I have a simple testing table in my VBA database with the following structure:

Field Name | Data Type
Header1    | Short Text
Header2    | Short Text

I also have a CSV file with this data in:

Header1;Header2
A;1
B;2
C;3

I am trying to write a simple VBA code that would insert new data from the CSV into the testing table, so far i have this:

Sub ImportDataFromCSV()
    Dim db As DAO.Database
    Set db = CurrentDb()
    db.Execute _
    "INSERT INTO Testing (" & _
             "[Header1]" & _
             ",[Header2])" & _
             " SELECT Header1, Header2" & _
             " FROM [Text;FMT=CSVDelimited;HDR=Yes;DATABASE=C:\Users\PC\Desktop\;].[testing#csv];", dbFailOnError
    db.TableDefs.Refresh
    RefreshDatabaseWindow
End Sub

Unfortunately when i run my sub i get the

Run-time error 3061: Too few parameters. Expected 2.

I understand it has something to do with

"INSERT INTO Testing (" & _
             "[Header1]" & _
             ",[Header2])"

am i right?

I thought this would work similarly to MySQL when inserting from data set based on another SELECT, i thought when i link the CSV it would work in similar manner.

i have read some questions here but to no avail.

I also recreated the query in a query editor and it appends 3 new blank rows with no data after asking me to enter parameter value for Header1 and Header2.

What can i change to make it work?

Thank you

Possible duplicate of How to import csv file to Access with VBA code? — Rene

zip zip · Accepted Answer · 2019-11-25T23:42:33

Your code is correct. The problem is that the file is not comma delimited as expected from a csv. So its seen as 1 column. You can change the csv to

Header1,Header2 
A,1 
B,2 
C,3

and it will work.

MS Access inserting data using VBA, Run-time error 3061

1 Answers