Setting up QODBC to work with ADO
QODBC can be accessed from ADO. ADO can be
used in Visual Basic (VB), Access, VBScripts, ASP, C++, VB.NET, C#
and any other language that supports COM components.
Connection String
The most difficult part of using ADO with
QODBC is the connection string. The simplest form includes a reference
to a DSN. A system DSN called "QuickBooks Data" is automatically
created when QODBC is installed. "QuickBooks Data" will be used in all
examples but can be substituted with any different DSN name you
create.
Normally ADO pools connections. QODBC does not support connection
pooling. It is recommended to tell ADO to not do connection pooling on
any QODBC connection. This is done with OLE DB Services=-2.
Other options available on the connection string:
Example of simple DSN:
sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;"
Example of a DSNless connection string:
sConnectString = "Driver={QODBC Driver for QuickBooks};DFQ=C:\Program
Files\QODBC Driver for QuickBooks\sample04.qbw;OpenMode=M;OLE DB
Services=-2;"
Example using current ADO syntax:
sConnectString = "Provider=MSDASQL.1;Persist Security Info=False;Data
Source=QuickBooks Data;OLE DB Services=-2;"
Query Data VBS Example
'*****************************************
Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL
sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;"
sSQL = "SELECT Name FROM Employee"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open sConnectString
oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic
sMsg = "**********************" & Chr(10)
Do While (not oRecordset.EOF)
sMsg = sMsg & oRecordSet.Fields("Name") & Chr(10)
oRecordset.MoveNext
Loop
sMsg = sMsg & "**********************" & Chr(10)
MsgBox sMsg
oRecordset.Close
Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing
'*****************************************
Visual Basic 6 Example
Requires project reference to Microsoft ActiveX Data Objects 2.x Library
'*****************************************
Dim oConnection As ADODB.Connection
Dim oRecordset As ADODB.Recordset
Dim sMsg As String
Dim sConnectString As String
Dim sSQL As String
sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;"
sSQL = "SELECT Name FROM Employee"
Set oConnection = New ADODB.Connection
Set oRecordset = New ADODB.Recordset
oConnection.Open sConnectString
oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic
sMsg = "**********************" & Chr(10)
Do While (Not oRecordset.EOF)
sMsg = sMsg & oRecordset.Fields("Name") & Chr(10)
oRecordset.MoveNext
Loop
sMsg = sMsg & "**********************" & Chr(10)
MsgBox sMsg
oRecordset.Close
Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing
'*****************************************
Announcement List
To be alerted to news about QODBC, product releases, beta
test cycles, new features and promotions subscribe to QODBC News Asia
Pacific.
