|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| MS Access Update query error when exporting to QB |
| Author |
Message |
| Jeff |
| |
| Group | : Members |
| Posts | : 4 |
| Joined | : 2007-04-23 |
|
| Profile |
|
| Posted : 2007-04-24 13:17:13 |
I'm attempting to export a few CHECK transaction lines from MS Access into QB using an update query as shown below. The connection is working fine and I can manually add records to QB tables from within Access.
However, it doesn't seem to recognize the QB fields as it asks for manual input of each paramenter. Even after inputting those, just to test the query, I get the error messsage:
ODBC—Update on a linked table “Check” failed.
[QODBC] not supported (#10003)
This is the query which is simply trying to write the date, account, payee, amount, check # and memo to the CHECK table.
UPDATE [Check] SET [Check].TxnDate = [QB Advances - Check Table]!ChequeDate, [Check].AccountRefFullName = "TD Chequing", [Check].PayeeEntityRefFullName = [QB Advances - Check Table]!CompanyName, [Check].Amount = [QB Advances - Check Table]!SumOfAdvanceAmount, [Check].[Memo] = [QB Advances - Check Table]!ChequeNotes, [Check].RefNumber = [QB Advances - Check Table]!ChequeNumber;
Any idea as to what I'm doing wrong ???
Thanks,
Jeff |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-04-24 21:20:11 |
|
|
|
| Jeff |
| |
| Group | : Members |
| Posts | : 4 |
| Joined | : 2007-04-23 |
|
| Profile |
|
| Posted : 2007-04-25 11:23:31 |
I looked at the page suggested but it doesn't seem to indicate how I get info from an Acces Update Query into the quickbooks tables. I'm not proficient at SQL but it appears to me that the code given requires the user to input the values fields manually and that the code is intended to be placed in the QB Demo VB rather than an Access Update Query.
I am operating under the assumption that for me to use QODBC to export data from Access into QB I link the required QB tables to Access so they are visible and presumably writeable from within Access using Access Update Queries. I should need to use the QODBC VB Demo at all. Is this correct?
Also, it says at the outset that the CHECK table will not take inserts then the final line of code provided is written to the CHECK table...
Its also not clear to me when the FQSaveToCache line requires a 0 and when it requires a 1.
However, using the script given I tried to set up an Update Query within Access that would write the results from a different Access Query into the CheckExpenseLine Table. Two problems occur.
1) Even though the Update Query references a valid Query in the "Update To: " row, when I run the query it asks for the parameters of every field being updated. Its as if the Query that supplies the data to be written isn't being seen.
2) Even if I input the field parameters (just to test the update) I get the error "ODBC insert on a linked table CheckExpenseLine failed." I tried going directly to the QB Table and inserting the data manually and I get the same error. I thought I followed the setup instructions properly but would this inability to write to new records in the linked QB tables fom within Access be caused by some set up error on my part?
Any thoughts would be appreciated. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-04-25 12:28:33 |
FQSaveToCache is used to cache multiple line transactions, but if everything is a one line transaction you don't need to use it. The Check table is a header table and cannot be inserted into unless there are cached CheckItemLine or CheckExpenseLine inserts.
All three of these tables cannot be UPDATEd, they can only be INSERTed. Most MS Access programmers use the DoCmd.RunSQL command to execute SQL inserts to import transactions into QuickBooks like this example for an Invoice:
DoCmd.RunSQL "INSERT INTO QB_InvoiceLine ( CustomerRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, " & _
"InvoiceLineQuantity, InvoiceLineRate, InvoiceLineClassRefListID, InvoiceLineTaxCodeRefListID, " & _
"CustomFieldVFBatchNo, FQSaveToCache )" & _
"VALUES ( 'Me.CustomerRefListID', 'Me.RefNumber', 'Me.InvoiceLineItemRefListID', 'Me.InvoiceLineDesc', 'Me.InvoiceLineQuantity', " & _
"'Me.InvoiceLineRate', 'Me.InvoiceLineClassRefListID', 'Me.InvoiceLineTaxCodeRefListID', 'Me.CustomFieldVFBatchNo', 0)" |
|
|
|
| Jeff |
| |
| Group | : Members |
| Posts | : 4 |
| Joined | : 2007-04-23 |
|
| Profile |
|
| Posted : 2007-04-25 13:07:59 |
As suggested, I tried the query below. It seemed to run ok but it said "You are about to append 0 rows" and then did precisely that. Is that because I've only written the query for one side of the check or do you see a different problem?
Thanks
Jeff
INSERT INTO CheckExpenseLine ( TxnDate, PayeeEntityRefFullName, Amount, [Memo], RefNumber, AccountRefFullName, FQSaveToCache, ExchangeRate )
SELECT [QB Advances - Check Table].ChequeDate, [QB Advances - Check Table].CompanyName, [QB Advances - Check Table].SumOfAdvanceAmount, [QB Advances - Check Table].ChequeNotes, [QB Advances - Check Table].ChequeNumber, "Bank" AS Expr2, "FQSaveToCache" AS Expr1, "ExchangeRate" AS Expr3
FROM [QB Advances - Check Table]
WHERE ((("Bank")="TD Chequing") AND (("FQSaveToCache")=0) AND (("ExchangeRate")=1));
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-04-25 16:22:58 |
|
|
|
| Jeff |
| |
| Group | : Members |
| Posts | : 4 |
| Joined | : 2007-04-23 |
|
| Profile |
|
| Posted : 2007-04-25 22:26:40 |
Suspending the query messages is good to know. However, until I get my INSERT query working properly they're actually useful to me.
So my prime problem is that the query didn't work, ie. 0 rows were appended. I've no idea why that would happen. If the code is fine then I was wondering if maybe it was because I was writing only the credit side of the cheque transaction and maybe QB requires both the debit and credits to be written at the same time. Or is there another possible explanation ?
Thanks,
Jeff |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-04-26 10:07:55 |
|
|
|
|