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 & "') "
Cindy Meister
  • 25,071
  • 21
  • 34
  • 43

1 Answers1

-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.

GavinP
  • 677
  • 1
  • 5
  • 18
  • What if I have say multiple fields? –  Jan 11 '19 at 16:39
  • I updated my post –  Jan 11 '19 at 16:39
  • 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. – GavinP Jan 11 '19 at 16:51
  • 2
    Please do not advise how to fix string concatenation used for parameters in an SQL query. [It is wrong anyway](https://stackoverflow.com/q/332365/11683). – GSerg Jan 11 '19 at 16:53