Solved

access updating recordsets is sooo slow

Posted on 2010-11-11
7
485 Views
Last Modified: 2012-06-27
Hi
I am trying to link data from three different sources into one single database and all was going well but all of a sudden I have hit a brick wall. The current updates I am running to merge the latest set of data just seem to be running so slow.  I could really do with some advice on how to optimise this section of code as we have 11,700 records to update and it is painful.  Like I said I am not an expert - just starting out with access so am sure I could be doing things better.

thanks
Sharon
Sub applydescriptionstoproducts()





Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection



cnn.ConnectionString = strcnn

cnn.Open CurrentProject.Connection





Dim strprodcode As String

Dim strDescription As String

Dim strSummary As String

Dim strName As String

Dim prodrs As ADODB.Recordset

Dim deeprodrs As ADODB.Recordset

Dim proddesrs As ADODB.Recordset

Dim productid As Integer



Set prodrs = New ADODB.Recordset

prodrs.ActiveConnection = CurrentProject.Connection

prodrs.Source = "vgm_products"

prodrs.CursorType = adOpenDynamic

prodrs.LockType = adLockOptimistic



Set deeprodrs = New ADODB.Recordset

deeprodrs.ActiveConnection = CurrentProject.Connection

deeprodrs.Source = "products"

deeprodrs.CursorType = adOpenDynamic

deeprodrs.LockType = adLockOptimistic



Set proddesrs = New ADODB.Recordset

proddesrs.ActiveConnection = CurrentProject.Connection

proddesrs.Source = "vgm_product_desc"

proddesrs.CursorType = adOpenDynamic

proddesrs.LockType = adLockOptimistic





prodrs.Open "SELECT vgm_products.[code],vgm_products.[product_id] FROM vgm_products "

Do Until prodrs.EOF

    strDescription = " "

    strSummary = " "

    strName = " "

    

    

    strprodcode = prodrs!code

    productid = prodrs!product_id

    Debug.Print productid



    

    deeprodrs.Open "SELECT products.[short_desc],products.[name],products.[description] FROM products WHERE (((products.[ProductStyle])= " & Chr(34) & strprodcode & Chr(34) & "));"

    

    Do Until deeprodrs.EOF

            Rem there are blanks in this column so we need to ignore this error

            Rem that is caused by assigning null to a string

            On Error GoTo emptystringerror:

            strDescription = deeprodrs!Description

            strSummary = deeprodrs![short_desc]

            strName = deeprodrs![Name]

            deeprodrs.MoveNext

    Loop

        

      

    deeprodrs.Close

    

       

       

       

   proddesrs.Open "SELECT * FROM vgm_product_desc WHERE (((vgm_product_desc.[product_id])= " & productid & "));"

   Rem if we found it save the id

   Do Until proddesrs.EOF

       proddesrs![Description] = strDescription

       proddesrs![summary] = strSummary

       proddesrs![Name] = strName

       proddesrs.MoveNext

   Loop



   proddesrs.Close

   



    prodrs.MoveNext

Loop



Rem all done close recordsets

prodrs.Close





Set prodrs = Nothing

Set deeprodrs = Nothing

Set proddesrs = Nothing



Rem close the database connection

cnn.Close

Set cnn = Nothing

Exit Sub





emptystringerror:

    Resume Next

Exit Sub





End Sub

Open in new window

0
Comment
Question by:sensbury
  • 4
  • 2
7 Comments
 
LVL 6

Expert Comment

by:YohanF
ID: 34111343
Hi

It is slightly confusing, could you please right down the steps you want to do.

1 - open main products
2 - match in to the new file using xxx parameters
3 - Update part...

I have no clue what you are updating and why... so bit more detail on code would help you and me both realise whats going wrong..

cheers
0
 

Author Comment

by:sensbury
ID: 34112622
hi thanks for the swift response

Basically I have three tables vgm_products, products and vgm_product_descriptions.

products is a table that has been imported from excel it has in it information about the products such as its name long description and short description.  I want to extract information about producs from this table and place it in vgm_product_descriptions.  

So I am going through vgm_products and for each product in that table I am extracting that products id (a numerical value) and its product code which is a string.

then I am going through products and searching for the record containing that product code. In the products table the product code stored in the productstyle field.  Once I have found the record, I want to extract the contents of the description, name and short_description field.

Once I have this data I  want to go to vgm_product_description and find the record that has the id that i got from vgm_products and update that with the newly found description, name and summary.

When I have completed this task I will be getting rid of the table products it is purely being used at the moment to populate vgm_product_description. But  records in vgm_product description are identified by a product id and in products they are identified by product style hence I am using  vgm_products to identify the records in the other two tables.

I hope that makes sense.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34114770
Can you define "sooo slow", in chronological terms please?

Depending on what is happening, perhaps this will never really be "Fast" in your terms...

JeffCoachman
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 6

Accepted Solution

by:
YohanF earned 500 total points
ID: 34119885
There are a couple of ways to do this

1 - simple update query

do a query with 3 joins, a) vgm_products, b) vgm_product description and c) products and join them using their common fields.. and then add update statements to update appropriate items (use ms access query builder and you will be done with this within no time)

2 - the vba method

a) open up a record set, combining both vgm_products and products  (rather than opening 2 record sets)

prodrs.Open "SELECT vgm_products.
,vgm_products.[product_id], products.[short_desc],products.[name],products.[description] FROM vgm_products INNER JOIN products ON products.[ProductStyle] = vgm_products.[code]"

please check the query syntax, there may be small errors in syntax

b) then for each iteration, open the vgm_description table/appropriate record and update it

and if you want to update you have to use following code (see the edit and update lines of code).. This way you will only open up 1 record and no need to iterate - which is waste of a line of code (unless it appears more than once)
[code]
  proddesrs.Open "select * from vgm_description where xxx =" & xxxx

if proddeers.eof <> true then
       proddeers.edit

       proddesrs![Description] = strDescription
       proddesrs![summary] = strSummary
       proddesrs![Name] = strName

       proddeers.update
endif
   proddesrs.Close

Open in new window


Hope this help... buzzz if you need more info...
0
 
LVL 6

Expert Comment

by:YohanF
ID: 34119890
The answer should be more like this....

There are a couple of ways to do this

1 - simple update query

do a query with 3 joins, a) vgm_products, b) vgm_product description and c) products and join them using their common fields.. and then add update statements to update appropriate items (use ms access query builder and you will be done with this within no time)

2 - the vba method

a) open up a record set, combining both vgm_products and products  (rather than opening 2 record sets)
prodrs.Open "SELECT vgm_products.[code],vgm_products.[product_id], products.[short_desc],products.[name],products.[description] FROM vgm_products INNER JOIN products ON products.[ProductStyle] = vgm_products.[code]"

Open in new window

please check the query syntax, there may be small errors in syntax

b) then for each iteration, open the vgm_description table/appropriate record and update it

and if you want to update you have to use following code (see the edit and update lines of code).. This way you will only open up 1 record and no need to iterate - which is waste of a line of code (unless it appears more than once)
  proddesrs.Open "select * from vgm_description where xxx =" & xxxx

if proddeers.eof <> true then
       proddeers.edit

       proddesrs![Description] = strDescription
       proddesrs![summary] = strSummary
       proddesrs![Name] = strName

       proddeers.update
endif
   proddesrs.Close

Open in new window


Hope this help... buzzz if you need more info...
0
 

Author Closing Comment

by:sensbury
ID: 34121797
Thanks Yohan this really helped to speed up my task. I had some errors in my code anyway but once I fixed those and added your additional optimisation the sub updates 11,700 records in a matter of minutes.instead of never finishing - I am very happy :)
0
 
LVL 6

Expert Comment

by:YohanF
ID: 34124601
Glad to hear... :)
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now