Thanks for the reply. It does appear that the semicolon is not part of the Lexicon error. I reviewed the code and in testing I removed the semicolon already from the code. I’ve restored the code using the following SQL String:
SELECT InvoiceLine.* FROM InvoiceLine WHERE TimeModified < {d'2001-01-01}
And the message I am getting from QODBC Messages is :
2007-09-28 13:34:19 QODBC Ver: 5.00.00.104 *********************************************************************************************************************
IsAService: False
Begin Session Failed. S0000 00000 [QODBC] QB Begin Session Failed. Error = 80040416, If QuickBooks is not running, a call to the "BeginSession" method must include the name of the QuickBooks company data file.
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"
Quick books is running and this is the same connection string that I was running early in my code to test the tables and make sure these items could be posted.
The part of the code:
Set rst = CurrentDb.OpenRecordset( _
"SELECT * FROM " & varFormattedTable & _
"WHERE [Select] = True;", dbOpenDynaset)
Is where I open a recordset from my local tables to sort through what is going to be sent to QuickBooks. There is a field called “select” (allowing the user to not send some items to QuickBooks if they don’t want to yet). The next section is where I am getting all the problems
If rst.RecordCount > 0 Then
rs.Open _
"SELECT " & varQBTable & ".* " & _
"FROM " & varQBTable & " " & _
"WHERE TimeModified < {d'2001-01-01}", cn, adOpenDynamic
rst.MoveFirst
Do Until rst.EOF
rs.AddNew
Basically at this point I just want a blank recordset (called rs) that is linked to the desired QuickBooks table so that I can then add new records. I am trying for an empty recordset so it can connect quickly and not have to sort through all the records (performance issues). I have removed the WHERE statement and gotten the connection working, but it took a long time to load the table to memory. Also when it got to the AddNew it gave an error that that is not available due to the client. No messages from the QODBC message to indicate why.
I could either use assistance getting the filtered recordset and then adding the record, or if there is another way to add a record that would work as well. Note this function loads four different tables. I build a table for the invoices, deposits, payments, and credits and then post each up. So INSERT SQL statements are not something I can build readily. Currently I run through each field using code. It works when I use MSSQL and just crashes when I try and run the code through QODBC.
Do Until rst.EOF
rs.AddNew
For Each fld In rst.Fields
Select Case fld.Name
Case "Select", "SentToQB", varIDField
Case Else
rs.Fields(fld.Name) = fld.Value
End Select
Next fld
rs.Update
rst.Edit
rst!SentToQB = True
rst.Update
rst.MoveNext
Loop
(Note: rst is the local database and rs is the recordset connecting to QB.)
| 
