connecting to access data base and write to table

Question

I have an excel invoice sheet and I want to write the information from the invoice to a table in an Access file. My following code is:

Private Sub Button66_Click()
Dim con As New ADODB.Connection
Dim connectionString As String

Dim sql As String

connectionString = "DBQ=c:\Users\Public\Public Desktop\InvoiceRecords.mdb; Driver={Microsoft Access Driver (*.mdb)};"

con.Open connectionString

sql = "insert into Invoices (Customer, Address) values(G6, G7)"

con.Execute sql

MsgBox "Values entered", vbInformation

con.Close

Set con = Nothing

End Sub

However, when I run it I get a runtime-error '-2147217904 (80040e10)'; Too few parametersentered. I'm not what sure what this is. Any ideas or suggestions? Thanks a bunch!

Answer 1

I think the problem is you're trying to get at the values of cells G6 and G7 in your INSERT query. You need to concatenate them into your insert query instead.

sql = "insert into Invoices (Customer, Address) " & _
    "values('" & Range("G6") & "', '" & Range("G7") & "')"

Building your sql commands this way makes you vulnerable to SQL injection. A better alternative is to use a parameterized query.

Dim cmdInsert As New ADODB.Command
With cmdInsert
    .Prepared = True
    .CommandType = adCmdText
    .CommandText = "INSERT INTO Invoices (Customer, Address) VALUES (?, ?)"
    Set .ActiveConnection = con
End With

cmdInsert.Parameters(0).Value = Range("G6").Value
cmdInsert.Parameters(1).Value = Range("G7").Value
cmdInsert.Execute

You should also use the Jet driver to connect, instead of the ODBC driver. Use this connection string instead.

"Provider=Microsoft.Jet.OLEDB.4.0;Data source=c:\Users\Public\Public Desktop\InvoiceRecords.mdb;"