Examples of How to Use QODBC via Visual Basic

Download Link

Query and Update VBS

Click here: http://www.qodbc.com/docs/support/vbexamples.zip to download some VBS files that show several examples of queries and updates. They can be pasted into a Visual Basic 6 program and used. You can strongly type the dimmed variables and change the Create Objects to the new if you reference ado. The VBS will run as is, pasted in.

VB Demo 32 Source

Also, click here: http://www.qodbc.com/docs/support/vbdemosrc.zip for the source to VBDemo32. It mainly came from Microsoft. It uses the Sheridan grid, so you won't be able to run it unless you have that, but you can look at how to do ODBC calls directly. By far, the fastest executing programs can be written with it. It will just be time-consuming to get a library built to use it.

Example Process

Example VBS (Update Specified Value)

'***********************************
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

Dim asFName(1)
Dim asLName(1)
Dim asBAddr1(1)
Dim asBAddr2(1)
Dim asBAddr3(1)
Dim asBCity(1)
Dim asBState(1)
Dim asBZip(1)
Dim asSAddr1(1)
Dim asSAddr2(1)
Dim asSAddr3(1)
Dim asSCity(1)
Dim asSState(1)
Dim asSZip(1)
Dim asPhone1(1)
Dim asPhone2(1)
Dim asEmail(1)
Dim sSQL
Dim lCnt
'***********************************
asFName(1) = "Kim"
asLName(1) = "Garland"
asBAddr1(1) = "123 E Main St"
asBAddr2(1) = "Addr2"
asBAddr3(1) = ""
asBCity(1) = "Mesa"
asBState(1) = "AZ"
asBZip(1) = 85222
asSAddr1(1) = "321 E Main St"
asSAddr2(1) = ""
asSAddr3(1) = "Addr3"
asSCity(1) = "Mesa"
asSState(1) = "AZ"
asSZip(1) = 85215
asPhone1(1) = "111-111-111x"
asPhone2(1) = "111-111-1111"
asEmail(1) = "deanna@global.net"
'***********************************

Set oConnection = CreateObject("ADODB.Connection")
oConnection.Open "DSN=Quickbooks Data;OLE DB Services=-2"

For lCnt = 1 To UBound(asFName)
sSQL = "SELECT * FROM Customer WHERE IsActive = 1 and FirstName = '" & asFName(lCnt) & "' and LastName = '" & asLName(lCnt) & "'"

Set oRecordset = CreateObject("ADODB.Recordset")
oRecordset.CursorType = 2
oRecordset.LockType = 3

oRecordset.CursorLocation = adUseClient
oRecordset.Open sSQL, oConnection

If (Not oRecordset.EOF), then
MsgBox oRecordSet.Fields("ListID")
'oRecordset.Fields("BillAddressAddr1").Value = asBAddr1(lCnt)
'oRecordset.Fields("BillAddressAddr2").Value = asBAddr2(lCnt)
'oRecordset.Fields("BillAddressAddr3").Value = asBAddr3(lCnt)
'oRecordset.Fields("BillAddressCity").Value = asBCity(lCnt)
'oRecordset.Fields("BillAddressState").Value = asBState(lCnt)
'oRecordset.Fields("BillAddressPostalCode").Value = asBZip(lCnt)
'oRecordset.Fields(&qu ot;ShipAddressAddr1").Value = asSAddr1(lCnt)
'oRecordset.Fields("ShipAddressAddr2").Value = asSAddr2(lCnt)
'oRecordset.Fields("ShipAddressAddr3").Value = asSAddr3(lCnt)
'oRecordset.Fields("ShipAddressCity").Value = asSCity(lCnt)
'oRecordset.Fields("ShipAddressState").Value = asSState(lCnt)
'oRecordset.Fields("ShipAddressPostalCode").Value = asSZip(lCnt)
oRecordset.Fields("Phone").Value = asPhone1(lCnt)
'oRecordset.Fields("AltPhone").Value = asPhone2(lCnt)
'oRecordset.Fields("Email").Value = asEmail(lCnt)
oRecordset.Update
End If
Next
'***********************************
oRecordset.Close
Set oRecordset = Nothing

oConnection.Close
Set oConnection = Nothing
'***********************************

Step 1 - I created a new customer record for Kim Garland and closed the Edit Customer pane in QuickBooks:

Step 2 - I copied your script to Notepad, saved the file as Customer.vbs, and then double-clicked it using Windows Explorer. The ListID is displayed in the MsgBox:

Step 3 - I opened the Edit Customer pane and looked at the record in QuickBooks. The phone number has been updated as per: asPhone1(1) = "111-111-111x".

Note: You can't have the Customer record open in QuickBooks while you run the vbs script.