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.

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.

I've downloaded vbexamples.zip and tried customer.vbs and I am getting the following error:

c:\>cscript.exe customer.vbs
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

c:\customer.vbs(73, 3) Microsoft OLE DB Provider for ODBC Drivers: Query-based u
pdate failed because the row to update could not be found.

In the script I changed asFName and asLName to match a customer in my database.  Any suggestions would greatly be appreciated.

I don't understand what you mean about Deanna Ecker?  I changed up the script to include an existing customer in my QuickBooks database.

Please explain.

Perhaps you didn't see my first reply.  I changed those values to a customer in MY QuickBooks file.......Here is the script I am trying to run:

'***********************************
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.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("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
'***********************************

As you can see I changed the name to match one in my database.....any suggestions?

Try changing the SELECT statement to:

 sSQL = "SELECT * FROM Customer WHERE FirstName = '" & asFName(lCnt) & "' and LastName = '" & asLName(lCnt) & "'"

Thanks for the reply.  I've changed the SQL statement as per your suggestion but still produces the same error.  As you will notice I have a msgbox that shows me the ListID of the customer that is selected and it shows me the correct ListID.  Using both sql statements, with and without the isactive, I get the correct ListID.

Still for some reason it is not updating the record.

Ok, for the record there's nothing wrong with your script, it runs without any problems.

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".

Check that you are running a Read/Write version of QODBC in the About tab in the QODBC Setup Screen. You also can't have the Customer record open in QuickBooks while you run the vbs script.

I am running the Server Edition eval.  I have 5 days left of the eval.  I have QuickBooks totally closed when I run the script.  I am still getting the same error.  Are there any other settings in the QODBC setup screen that I need to have set?  Thank you again.

Yes, I've run it both ways.  With QuickBooks open with a company file and with QuickBooks closed.  Both give me the same error.  Just as a side note, when I ran the script with QuickBooks closed I got the message box with the ListID.....

I am running QuickBooks 2004 Pro.  Are there any setting in QuickBooks that would not allow me to write to the database?

Please go to the QODBC Setup Screen, press the "Messages" tab page and click on the "Review QODBC Messages" log, scroll down to the end and tell me what the real error says.

Make sure your connection is not Read-Only Access:

and try running the following update statement using VB Demo:

Update Customer Set Phone = '111-111-111x'  WHERE FirstName = 'Kim' and LastName = 'Garland'

This is the only thing in the messages file:

2007-08-21 23:53:55 QODBC Ver:  7.00.00.214 *********************************************************************************************************************
IsAService: False
Begin Session Failed. S0000 00000 [QODBC] QB Begin Session Failed. Error = 80040414, A modal dialog box is showing in the QuickBooks user interface. Your application cannot access QuickBooks until the user dismisses the dialog box.
Begin Session errors can be caused when running ASP or other system service and not setting the "Advanced" button option of either "Use DCOM Servers" or "Remote Connector"

Under Advanced, read only access in NOT checked.  I ran the query from VB Demo and it updated the customer record !  Now I'm thouroughly confused......

I even uninstalled QODBC and reinstalled it, but no luck.

I get the same error when I launch customer.vbs from windows explorer.  I only have 2 days left on the trial, any other suggestions.  Thank you

All of the examples in this forum I see use the VB Demo.  I need to be able to script the sql inserts.  The other examples you suggested trying are just querying the database, NOT writing to it.  I can pull information out as seen below.  I just can't write from a script.  What gives?  Can this be done or not?

This is getting rather frustrating.  If this can't be done I will have to look for another product.  Please advise.

Yes, it can be done and I've shown it working above. There are hundreds of users out there running script with QODBC each and every day. Unfortunately you will not say the exact error you get when the VBS script is run through Windows Explorer.

You can try changing the script to:

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

and you can also try changing the recordset cursors type to forward only and to use the server instead of the client :

adOpenForwardOnly
adLockOptimistic
adUseServer

Then what appears to be the problem?  Is it a permissions issue?  I saw somewhere that each application using QODBC has to register with QuickBooks??  I can pull information out using a script, I just can't write.  Everything you've had me check is correct.

I am running QuickBooks 2004 pro multi-user.  I have it open to the company file I'm trying to access and write to.  I'm sorry, but are the the forum admin?  Developer?  There has to be an explanation as to why this isn't working for me.  I'm not doing anything out of the ordinary right?

Thank you again.  Where are you located?  Do you have a telephone number?

Here is the exact error I get when running the script from Windows Explorer:

What do you mean use the server instead of the client?  I changed the script as per your suggestions below and this is what happened this time - when I launched the script I got a pop up window that said busy optimizing please wait.  Then this error came up:

c:\customer.vbs(77, 3) Microsoft OLE DB Provider for ODBC Drivers: [QODBC] Expec
ted lexical element not found: SET

Line 77 is the update statement:  oRecordset.Update

this is all that is in the message file:

2007-08-21 23:53:55 QODBC Ver:  7.00.00.214 *********************************************************************************************************************
IsAService: False
Begin Session Failed. S0000 00000 [QODBC] QB Begin Session Failed. Error = 80040414, A modal dialog box is showing in the QuickBooks user interface. Your application cannot access QuickBooks until the user dismisses the dialog box.
Begin Session errors can be caused when running ASP or other system service and not setting the "Advanced" button option of either "Use DCOM Servers" or "Remote Connector"

When I run customer.vbs, nothing is added to the Review QODBC Messages file.......

Ok now I've forced the new customer into the optimized table try:

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

Still same original error message......Why is this so complicated.  If there are 100's of other customers running sql queries from a script using cscript why is this not working? 

What is the difference between the desktop read/write editiion and the server edition?

Tom,

Thanks for you patience and help.  I finally got it working using this combination of variables:

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

oRecordset.CursorType = 2
oRecordset.LockType = 3
oRecordset.CursorLocation = adUseClient

Finally !!  I have a few more questions though:

1. How can I extend my trial version?  I want to succesfully create an invoice before purchasing.

2. What is the difference between the desktop and the server edition other than the hefty price tag?

3. Can I get an example of how to create an invoice from within Excel using VBA.  I see some example SQL statements for creating invoices, but can I see that in a vbscript.  Also, when creating invoices do you have to supply an invoice number or will it automatically use the next invoice number available?

In my Excel spreadsheet I have the following data:

Customer Name, Line Item Name, Date and Quantity of Line Item Transaction.  Each customer will have a new invoice created with multiple lines.

Any help would greatly be appreciated.

See: How does the 30 day evaluation work? How can I evaluate the Desktop Edition? 

VBE programming within MS Excel, otherwise known as the Visual Basic Integrated Design Environment (VBIDE), and the object library provided with Office XP and Office 2007 is currently beyond the scope of this forum. Suggest you look for a good Excel and VBA book. Most of the time, using MS Access is a better way to go as you can always export results from Access to Excel (or import form Excel) if that is what you need to do. 

Aside from the Excel issue, how do I assign an invoice number and will Quickbooks automatically assign and increment if in invoice number is not supplied?

If you want to specify the Invoice number, you insert with a RefNumber value otherwise if no RefNumber is specified, QuickBooks will automatically assign the next Invoice Number for you.

For further information see:
How can I create multiple lines in the Invoice, Purchase, Journal and other tables? 
How do I create a new Customer, invoice them and mark the invoice as paid? 
How do I add a Inventory Item using QODBC? 
How do I insert data into ItemNonInventory? 
How do I insert data into ItemService?  

Note: QuickBooks Invoice Numbers are alpha-numeric, just make sure that if you use a alpha prefix, that you always end it with numbers.