Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

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

Forum : MS Access Update Query with QODBC (Slow Performance)Search Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC v6 Forum

 New Topic 
 
 Post Reply 
[1]  
 MS Access Update Query with QODBC (Slow Performance) 
 Author   Message 
  SEdison 
  
 Group: Members 
 Posts: 1 
 Joined: 2006-09-19 
 Profile
 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.

 

 
SEdison 
 
  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 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'});"

 

  Top 
  Julie Alley 
  
 Group: Members 
 Posts: 18 
 Joined: 2006-09-27 
 Profile
 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? 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 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';"

 

  Top 
  Julie Alley 
  
 Group: Members 
 Posts: 18 
 Joined: 2006-09-27 
 Profile
 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 

  Top 
  Julie Alley 
  
 Group: Members 
 Posts: 18 
 Joined: 2006-09-27 
 Profile
 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 

  Top 
  Julie Alley 
  
 Group: Members 
 Posts: 18 
 Joined: 2006-09-27 
 Profile
 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, 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 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?  

 

  Top 
  Julie Alley 
  
 Group: Members 
 Posts: 18 
 Joined: 2006-09-27 
 Profile
 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. 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 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. 

  Top 
  Julie Alley 
  
 Group: Members 
 Posts: 18 
 Joined: 2006-09-27 
 Profile
 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.

 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to