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 with QODBC (Slow Performance) |
Author |
Message |
|
Posted : 2006-09-19 07:09:15 |
I have an MS Access Database linked to QuickBooks DSN (all on one 2.8Ghz/2.5GB-RAM PC). I am trying to accomplish 2 things and experiencing very slow performance on both of them. Can anyone assist if there is a better way to achieve this that does not include executing this on a smaller population? Additionally, would UNOPTIMIZED, NOSYNC, VERIFIED benefit me at all here?
(1) I have product information stored in Access that I want to periodically push to Quickbooks. When I do this I want to update the Description, Price, and Active fields in Quickbooks based on a join of FullName. This is ~5000 records. To update the Descriptions I tried…
UPDATE t_SKU INNER JOIN ItemNonInventory ON (t_SKU.SKU = ItemNonInventory.FullName) AND (t_SKU.ListID = ItemNonInventory.ListID) SET ItemNonInventory.SalesOrPurchaseDesc = [t_SKU]![DescriptionInt];
(1a) This is very slow, any advice to speed it up?
(1b) Additionally, when trying to do the same type of update for Price/and ActiveStatus, I attempted to only update those records that had differences (via another query), which only seemed to extend the time/problem. I’m guess it’s simplest just to update them all?
(1c) Finally, I’d like to update all three (price/activestatus/description) records at once, best way to do so?
(2) I also like to pull salesorders back into access to run some business-scheduling reports we’ve built. Because of slow performance when querying my QODBC linked-tables, I’ve found it’s easiest (and completely fine) just to periodically replicate the Quickbooks file into my own local table. To do so, I’ve done the following which takes a couple minutes (for ~2000k records). Is this appropriate? Hoping it could be much faster.
Where T_QBK_Sales... is my local copy. (FAST)
DoCmd.RunSQL "DELETE T_QBK_Customer.* FROM T_QBK_Customer", -1
(NOT FAST)
DoCmd.RunSQL "INSERT INTO T_QBK_SALESORDERLINE SELECT SALESORDERLINE.* FROM SALESORDERLINE;"
Thank you, any suggestions are very appreciated. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-09-19 08:49:20 |
(1) Simply update all the fields in the one operation
SET ItemNonInventory.SalesOrPurchaseDesc = [t_SKU]![DescriptionInt], ItemNonInventory.IsActive = [t_SKU]![IsActive], ItemNonInventory.SalesOrPurchasePrice = [t_SKU]![SalesOrPurchasePrice];
and create a flag in your local table that indicates what records need to be updated instead of all of them. Or maybe just update QuickBooks when you make the change in the local table in the first place.
(2) Normally we select only the fields we are interested in with a time modified filter, so we don't bring in the same salesorders over and over again.
DoCmd.RunSQL "INSERT INTO T_QBK_SALESORDERLINE SELECT SalesOrderLine.TxnID, SalesOrderLine.TimeModified, SalesOrderLine.CustomerRefFullName, SalesOrderLine.ClassRefFullName, SalesOrderLine.TemplateRefFullName, SalesOrderLine.TxnDate, SalesOrderLine.RefNumber, SalesOrderLine.PONumber, SalesOrderLine.DueDate, SalesOrderLine.SalesRepRefFullName, SalesOrderLine.IsFullyInvoiced, SalesOrderLine.SalesOrderLineSeqNo, SalesOrderLine.SalesOrderLineGroupTxnLineID, SalesOrderLine.SalesOrderLineGroupItemGroupRefListID, SalesOrderLine.SalesOrderLineGroupItemGroupRefFullName, SalesOrderLine.SalesOrderLineGroupDesc, SalesOrderLine.SalesOrderLineGroupQuantity, SalesOrderLine.SalesOrderLineGroupIsPrintItemsInGroup, SalesOrderLine.SalesOrderLineGroupTotalAmount, SalesOrderLine.SalesOrderLineGroupSeqNo, SalesOrderLine.SalesOrderLineTxnLineID, SalesOrderLine.SalesOrderLineItemRefListID, SalesOrderLine.SalesOrderLineItemRefFullName, SalesOrderLine.SalesOrderLineDesc, SalesOrderLine.SalesOrderLineQuantity, SalesOrderLine.SalesOrderLineRate, SalesOrderLine.SalesOrderLineRatePercent, SalesOrderLine.SalesOrderLinePriceLevelRefListID, SalesOrderLine.SalesOrderLinePriceLevelRefFullName, SalesOrderLine.SalesOrderLineClassRefListID, SalesOrderLine.SalesOrderLineClassRefFullName, SalesOrderLine.SalesOrderLineAmount, SalesOrderLine.SalesOrderLineSalesTaxCodeRefListID, SalesOrderLine.SalesOrderLineSalesTaxCodeRefFullName, SalesOrderLine.SalesOrderLineInvoiced, SalesOrderLine.SalesOrderLineIsManuallyClosed FROM SalesOrderLine WHERE (SalesOrderLine.TimeModified>{ts '2006-09-18 00:00:00'});" |
|
|
|
|
Posted : 2006-11-26 04:57:19 |
In the above example I note that you are using an "insert Into" command instead of an "Update" Command.
Does this sample work the same as an update command? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-11-26 10:21:26 |
You can use DoCmd.RunSQL to run UPDATE commands, however, Update Subqueries don't currently work using QODBC. The following update subquery fails with a [QODBC] Expected lexical element not found: error:
DoCmd.RunSQL "UPDATE Invoice SET CustomFieldContract = (Select CustomFieldContract from Customer where FullName='Data Access Worldwide') where CustomerRefFullName='Data Access Worldwide';"
so you have specify the update values:
DoCmd.RunSQL "update Invoice set SalesRepRefListID='10000-1197757777' where TxnID='2B55-1071523067';" |
|
|
|
|
Posted : 2006-11-26 11:25:37 |
Thank you, your example helps alot!
One more question then is about requery or refresh of the dataset my form is using: I am allowing the user in a form to go to previous record, how do I refresh my data so the changes just made to this record is shown?
Thank you for your time |
|
|
|
|
Posted : 2006-11-26 12:36:06 |
What syntax would I use if I had two fields that I wanted to update, I tried using just a comma to seperate the fields and data identifiers with an error stating it can't go to that record.
Thank you for your time,
Julie |
|
|
|
|
Posted : 2006-11-26 13:12:24 |
Here is where I am currently:
I have a DoCmd.RunSQL statement that appears to update the information in Quickbooks but I get a "write Conflict" error window when trying to move to another record on my Access Form.
I believe I must have the syntax wrong, I wrote this based on some research I did on utterlyaccess web site demonstrating muliple fields and values from an Access Form.
I wonder if you might be able to assist me with this issue, I am providing my SQL command for your review.
strSQL = "UPDATE Customer SET Name = '" & Me.FullName & "', Email = '" & Me.Email & "', Fax = '" & Me.Fax _ & "' WHERE ListID ='" & Me.ListID & "'"
DoCmd.RunSQL strSQL
Thank you for your time, |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-11-27 10:12:17 |
Multiple update columns are handled using a comma, for example:
UPDATE PriceLevelPeritem SET PriceLevelPerItemCustomPrice = 43.21, PriceLevelPerItemItemRefListID = 'F0000-933272656' WHERE ListID = '30000-1197755980' AND PriceLevelPerItemItemRefListID = 'F0000-933272656'
See: Values Write to Table but Don't Update on Form
Sorry, this is a QODBC support forum, not a MS Access support forum. In depth questions regarding MS Access programming should be directed to Access support forums.
Try looking at: How do I supress Append query messages in MS Access? |
|
|
|
|
Posted : 2006-11-27 10:19:26 |
I was only trying to update the code on your forum, sorry if I overstepped your bounds -
I have been struggling with your QODBC site and how to post this type of update for several weeks on other forums and today I finally was making progress with these other forums, but it was looking like the update problem is one specific to the QODBC functionality....
It is difficult to update you on the status of something using this forum - especially with the 20+ hour time difference. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-11-27 10:27:07 |
As far as I know, it's Sunday in the United States right now and you are still getting same day answers here (within 9 minutes) using a non-priority support option. This can't be too bad. |
|
|
|
|
Posted : 2006-11-27 10:38:28 |
Sorry, I am just trying to finish what has proven to be a nightmare of a database project working with this QODBC interface.
|
|
|
|
|