connecting to access data base and write to table
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
开发者_JAVA技巧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!
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;"
精彩评论