I have a 30 day eval and would like to verify my application before I purchase.
We are a restaurant that inputs our daily receipts into Excel to calculate proper values that we then cut and paste manually into an 18 line QBOE Journal. I would like to automate this process using QODBC if possible. My plan is to use Access 2000 and "link" to the spreadsheet and to QBOE (with QODBC) and use VBA for automation.
I have successfully linked to QBOE (the keys for every session are a pain, but I might be able to live with that). I can successfully run your sample ADO code for Journal insert:
'This creates one JournalEntry with 2 credit lines and 2 debit lines. Note the FQSaveToCache field, set to True except on the last line.
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Dim oConnection
Dim oRecordsetCredit
Dim oRecordsetDebit
Dim sLastVendor
Dim dTotalApplie
Dim dAmountDue
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordsetCredit = CreateObject("ADODB.Recordset")
Set oRecordsetDebit = CreateObject("ADODB.Recordset")
Set oRecordsetJournal = CreateObject("ADODB.Recordset")
oConnection.Open "DSN=Quickbooks Data;;OLE DB Services=-2;"
oRecordsetCredit.CursorLocation = adUseClient
oRecordsetCredit.Open "SELECT * FROM JournalEntryCreditLine WHERE TxnId = 'X'", oConnection, adOpenStatic, adLockOptimistic
oRecordsetDebit.CursorLocation = adUseClient
oRecordsetDebit.Open "SELECT * FROM JournalEntryDebitLine WHERE TxnId = 'X'", oConnection, adOpenStatic, adLockOptimistic
oRecordsetJournal.CursorLocation = adUseClient
oRecordsetJournal.Open "SELECT * FROM JournalEntry WHERE TxnId = 'X'", oConnection, adOpenStatic, adLockOptimistic
oRecordsetCredit.AddNew
oRecordsetCredit.Fields("JournalCreditLineAccountRefListID").Value = "114"
oRecordsetCredit.Fields("JournalCreditLineAmount").Value = 1.11
oRecordsetCredit.Fields("JournalCreditLineMemo").Value = "Test Memo 1"
oRecordsetCredit.Fields("FQSaveToCache").Value = True
oRecordsetCredit.Update
oRecordsetCredit.AddNew
oRecordsetCredit.Fields("JournalCreditLineAccountRefListID").Value = "114"
oRecordsetCredit.Fields("JournalCreditLineAmount").Value = 2.22
oRecordsetCredit.Fields("JournalCreditLineMemo").Value = "Test Memo 2"
oRecordsetCredit.Fields("FQSaveToCache").Value = True
oRecordsetCredit.Update
oRecordsetDebit.AddNew
oRecordsetDebit.Fields("JournalDebitLineAccountRefListID").Value = "114"
oRecordsetDebit.Fields("JournalDebitLineAmount").Value = 1.11
oRecordsetDebit.Fields("JournalDebitLineMemo").Value = "Test Memo 1"
oRecordsetDebit.Fields("FQSaveToCache").Value = True
oRecordsetDebit.Update
oRecordsetDebit.AddNew
oRecordsetDebit.Fields("JournalDebitLineAccountRefListID").Value = "114"
oRecordsetDebit.Fields("JournalDebitLineAmount").Value = 2.22
oRecordsetDebit.Fields("JournalDebitLineMemo").Value = "Test Memo 2"
oRecordsetDebit.Fields("FQSaveToCache").Value = True
oRecordsetDebit.Update
oRecordsetJournal.AddNew
oRecordsetJournal.Fields("RefNumber").Value = "1"
oRecordsetJournal.Fields("TxnDate").Value = "10/1/2007"
oRecordsetJournal.Update
oRecordsetCredit.Close
oRecordsetDebit.Close
oRecordsetJournal.Close
oConnection.Close
Although this codes takes 5 minutes to complete!!! If I create a couple of Access queries to do the inserts .... The Inserts take only a few seconds:
append1:
INSERT INTO JournalEntryCreditLine ( JournalCreditLineAccountRefListID, JournalCreditLineAmount, JournalCreditLineMemo, FQSaveToCache )
VALUES ('114', 1.11, 'Test Memo 1', 1);
append2:
INSERT INTO JournalEntryDebitLine ( TxnDate, JournalDebitLineAccountRefListID, JournalDebitLineAmount, JournalDebitLineMemo, FQSaveToCache )
VALUES ('9/29/2007', '114', 1.11, 'Test Memo 1', 0);
QUESTIONS:
1) Why is there such a difference in processing time between the two methods above?
2) Is the query method a better way to go and if so, is there a way to suppress the Access append warnings?
3) It seemed like the only way I could get the above code to work is to add the TxnDate; does the newer SDK now require this?
4) I was under the impression that if a RefNumber was not included, the next RefNumber would be inserted, however the queries above leave the RefNumber blank. Is the a way to insert the next RefNumber?
Thank you for your help!