I have quite a few installations where some Qodbc Drivers will not be used for a couple of weeks, then they want to use the computer and the optimize file is out of date and they are already into a transaction when the "dreaded" please wait optimizing screen appears.  These are 150 to 200 meg Enterprise files so a lot changes in a couple of weeks.  I would like to create a set of scripts that will optimize base on a scheduled time, but I can not quite get the syntax correct.  I have not found a complete code snippit that shows the optimize call start to finish.  Here is as far as I have gotton, If you could give me a clue as to the complete call it would be a great help.   I am using ado to connect , I have taken out the optimize statement and run the code as a invoiceline update and all run smooth, so I know the connection is workable but maybe not with the optimize statement.  If that is the case please provide a snippit that will work with VB and I will compile it, I was just looking for a very "portable" way using the re-sync that scripts give.

Thank you very much,

Mike 

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Const adChar = 129
Const adParamOutput = 2
Const adCmdStoredProc = 4
Const adCmdText = 1

Set oConnection = CreateObject("ADODB.Connection")
oConnection.Open "DSN=QbData;OLE DB Services=-2;"

Set oInvoiceLine = CreateObject("ADODB.Recordset")
oInvoiceLine.Open "SELECT * FROM OPENQUERY(QODBC, 'sp_optimizefullsync InvoiceLine')"

'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx

'I found this on one of the posts in this fourm

'Open "SELECT * FROM OPENQUERY(QODBC, 'sp_optimizefullsync InvoiceLine')"

'I am not clear on the "QODBC" in the call, I beleive is it is causing my crash

'I get an error that the connection is closed or invalid in this context

'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

oInvoiceLine.Close

Executing a number of QODBC stored procedures currently within linked SQL Server tables will cause exception or transport errors. In default mode, any query on a table will trigger QODBC to run the optimizer update, so even something silly like this, with no valid value, will do the trick on linked servers:

SELECT * FROM OPENQUERY(QODBC, 'SELECT TxnID FROM InvoiceLine Where "TxnID" =''1234''')

Otherwise you can also schedule full or update optimization overnight using either: QODBCFUL.EXE or QODBCUPD.EXE. Both set the errorlevel variable if errors are detected, and error messages are written into the QODBCLOG.TXT log file. Passing a DSN on the command line to these programs will choose the QuickBooks company file you wish to use.

Note: QODBC "Auto Login" unattended mode needs to be setup for this work, see: How do I setup QODBC to auto-login into QuickBooks and start it if it's not running? 

I have done the 17 steps a few times but I still come back to this error for the data file, the file is working no problem I can conect read and write while the QBW is open, but when i try whith the file closed this is what I get.  I am running exactly how you showed :

qodbcupd dns name

or do i have to do the full the first time? I have an existing OPT file, it is just out of date.

2006-12-13 02:42:20 QODBC Ver:  6.00.00.176 *********************************************************************************************************************
IsAService: False
SQLOptimize_OpenOptimizeDBHandle prepare:
SELECT "type", "tbl_name" FROM "SQLite_MASTER"
file is encrypted or is not a database

I have tried a qodbcful with the file open, and it is working, but I can not get it to autologin.  this dat file is not been upgraded, it was created in QBE 2006, and is still running under 06.

Thank you so much for your time, any other insight will be a great help, I am glad I can just get the optmize to run on demand, now if I can just get the autologon to work.

mike