Having trouble logging in or locating tickets ?


 

Knowledgebase: Microsoft Products
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: 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 CreateObjects to news if you reference ado. The VBSs 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 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) = "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 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 therecord 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))
This article was helpful
This article was not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please enter the text you see in the image into the textbox below. This is required to prevent automated registrations and form submissions.

Help Desk Software by Kayako Resolve