0

Simple question, how can I take something entered in a textbox and enter it into QuickBooks from Access. I have no problems with connecting Access to QuickBooks but am receiving a syntax error.

This hard coded input works:

sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;" sSQL = "Insert into customer (Name) values ('Testing VB')" Set oConnection = CreateObject("ADODB.Connection") Set oRecordset = CreateObject("ADODB.Recordset") oConnection.Open sConnectString oConnection.Execute (sSQL) sMsg = sMsg & "Record Added!!!" MsgBox sMsg Set oRecordset = Nothing Set oConnection = Nothing End Sub

This is what I am trying to get to work (input from text box):

cust = Forms.form1.customerName sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;" sSQL = "Insert into customer (Name) values" & cust & ")" Set oConnection = CreateObject("ADODB.Connection") Set oRecordset = CreateObject("ADODB.Recordset") oConnection.Open sConnectString oConnection.Execute (sSQL) sMsg = sMsg & "Record Added!" MsgBox sMsg Set oRecordset = Nothing Set oConnection = Nothing End Sub

Update (what about multiple entries at once?

cust = Forms.form1.customerName company = Forms.form1.companyName sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;" sSQL = "Insert into customer (Name), (CompanyName) values ('" & cust & "'), ('" & companyName & "') "
Improve this question
0

1 Answer 1

Reset to default
-1

You're not forming the dynamic SQL correctly. You need to adjust your code as follows sSQL = "Insert into customer (Name) values ('" & cust & "')" making note of the extra bracket to surround the values, and also the text qualifier quotes.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

What if I have say multiple fields?
I updated my post
Then you'd include them in the field list and the value list as normal. Insert into customer (field1, field2, field3) values ('" & field1value & "','" & field2value & "','" & field3value & "') assuming they're all string fields.
Please do not advise how to fix string concatenation used for parameters in an SQL query. It is wrong anyway.