| Connecting to QB through VB Script |
| Author |
Message |
|
|
| Posted : 2006-10-07 04:29:52 |
I am attempting to connect to Quickbooks, and I get the following error ..
Error: [QODBC]Server Edition of QODBC is required to use the DCOM Servers.
Code: 80004005
Source: Microsoft OLE DB Provider for ODBC Drivers
I didn't think the Server Edition was required for VB Script.
I believe the connection is set up correctly as the VB Demo program is able to query my QB Data.
This is the script (it just attempts to print out an item list) ...
----------------
Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL
' connect to the qb database, using connection defined on this machine
sConnectString = "DSN=FL Test QB Data;OLE DB Services=-2;"
sSQL = "SELECT ListID,FullName FROM Item"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open sConnectString
oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic
Do While (not oRecordset.EOF)
sMsg2 = oRecordSet.Fields("ListID") & " --- " & oRecordSet.Fields("FullName")
objTextFile.WriteLine(sMsg2)
oRecordset.MoveNext
Loop
oRecordset.Close
Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing
Set objFSO = Nothing
-----------------------
Any help would be greatly appreciated.
Craig |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-10-07 08:53:09 |
Make sure you haven't turned on "Use DCOM Servers"
and you are actully excuting the .vbs file on the desktop. If you are actually trying execute the VB Script via a web page or SQL Server, then "yes" you will need a QODBC Server Edition or use the QODBC Remote Connector. |
|
|
|
|
|
| Posted : 2006-10-07 09:17:12 |
Thanks for your reply.
The "Use DCOM Servers" is unchecked, and I am not running the script thru a web page or anything. (I just double-click on it from Windows Explorer).
The script *is* on a shared network drive (on our server). Would that make a difference? (Quickbooks is running locally). |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-10-07 10:46:42 |
| Yes, although the .vbs file is on a network drive, it's running on your workstation, which means both QODBC and QuickBooks needs to be on your workstation unless you are running Terminal Services. I didn't catch where QODBC is? |
|
|
|
|
|
| Posted : 2006-10-07 12:06:51 |
QuickBooks Enterprise 2006 and QODBC are both installed on my PC (the QB company file is on a Server).
My working directory is a shared drive off the server, but I'm running everything locally.
I'm not using Terminal Services or any other remote access.
The VB Test program that came with the QODBC installation connects and queries just fine.
Any ideas? |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-10-08 00:28:43 |
Try saving this as test.vbs in your C:\Program Files\QODBC Driver for QuickBooks folder and double click on it with QuickBooks running and a company file open:
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Dim oConnection
Dim oRecordset
Dim sMsg
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open "DSN=Quickbooks Data;"
oRecordset.CursorLocation = adUseClient
oRecordset.Open "SELECT * FROM Employee" , oConnection, adOpenStatic, adLockOptimistic
sMsg = "************************" & vbCrLf
sMsg = sMsg & "QODBC Driver Test Result:" & vbCrLf
sMsg = sMsg & "QuickBooks Employee List:" & vbCrLf
Do While (not oRecordset.EOF)
sMsg = sMsg & " " & oRecordSet.Fields("Name") & vbCrLf
oRecordset.MoveNext
Loop
sMsg = sMsg & "************************" & vbCrLf
MsgBox sMsg
oRecordset.Close
oConnection.Close |
|
|
|
|
|
| Posted : 2006-10-10 00:09:38 |
Your VBS Sample worked.
Once I had that, I went back thru and found the issue. I had a DB connection defined as a system DSN that had a slightly different name, and It turns out, it had the "use DCOM servers" checked. My script had the connection string was pointing to the system DSN, and not my user DSN. I didn't realize I had the data source defined twice with slightly different names. Rather boneheaded on my part, but it's something I'll remember now! Got to check all those tabs...
Thanks so much for your help.
Craig |
|
|
|
| |
| |
| Group | : Members |
| Posts | : 4 |
| Joined | : 2006-12-24 |
|
| Profile |
|
| Posted : 2006-12-24 14:57:01 |
i found the following script here for vb scrips, can this be converted to bva for access so i can create invoices and purchase orders. if so how.
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Dim oConnection
Dim oRecordset
Dim sMsg
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open "DSN=Quickbooks Data;"
oRecordset.CursorLocation = adUseClient
oRecordset.Open "SELECT * FROM Employee" , oConnection, adOpenStatic, adLockOptimistic
sMsg = "************************" & vbCrLf
sMsg = sMsg & "QODBC Driver Test Result:" & vbCrLf
sMsg = sMsg & "QuickBooks Employee List:" & vbCrLf
Do While (not oRecordset.EOF)
sMsg = sMsg & " " & oRecordSet.Fields("Name") & vbCrLf
oRecordset.MoveNext
Loop
sMsg = sMsg & "************************" & vbCrLf
MsgBox sMsg
oRecordset.Close
oConnection.Close |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-12-25 16:32:54 |
|
|
|
