Knowledgebase: Microsoft Products
[QODBC-Desktop] Examples of How to Use QODBC via Visual Basic
Posted by brad waddell on 12 March 2009 05:21 PM

Examples of How to Use QODBC via Visual Basic

Download Link

Query and Update VBS

Click here: 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 news if you reference ado. The VBS will run, as is, pasted in.

VB Demo 32 Source

Also click here: for the source to VBDemo32. It came mostly from Microsoft. It uses the Sheridan grid so you won't be able to use 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) = ""

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)
End If
Set oRecordset = Nothing

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 and saved the file as Customer.vbs and then double clicked on it using Windows Explorer. The ListID is displayed in the MsgBox:

Step 3 - I then 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.

(146 vote(s))
Not helpful

Comments (0)
Post a new comment
Full Name:
CAPTCHA Verification 
Please enter the text you see in the image into the textbox below (we use this to prevent automated submissions).