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.ConnectionSet cnn = New ADODB.Connectioncnn.ConnectionString = strcnncnn.Open CurrentProject.ConnectionDim strprodcode As StringDim strDescription As StringDim strSummary As StringDim strName As StringDim prodrs As ADODB.RecordsetDim deeprodrs As ADODB.RecordsetDim proddesrs As ADODB.RecordsetDim productid As IntegerSet prodrs = New ADODB.Recordsetprodrs.ActiveConnection = CurrentProject.Connectionprodrs.Source = "vgm_products"prodrs.CursorType = adOpenDynamicprodrs.LockType = adLockOptimisticSet deeprodrs = New ADODB.Recordsetdeeprodrs.ActiveConnection = CurrentProject.Connectiondeeprodrs.Source = "products"deeprodrs.CursorType = adOpenDynamicdeeprodrs.LockType = adLockOptimisticSet proddesrs = New ADODB.Recordsetproddesrs.ActiveConnection = CurrentProject.Connectionproddesrs.Source = "vgm_product_desc"proddesrs.CursorType = adOpenDynamicproddesrs.LockType = adLockOptimisticprodrs.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.MoveNextLoopRem all done close recordsetsprodrs.CloseSet prodrs = NothingSet deeprodrs = NothingSet proddesrs = NothingRem close the database connectioncnn.CloseSet cnn = NothingExit Subemptystringerror: Resume NextExit SubEnd Sub
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
sensburyAuthor Commented:
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.
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
Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.
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 syntaxb) then for each iteration, open the vgm_description table/appropriate record and update itand 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 =" & xxxxif proddeers.eof <> true then proddeers.edit proddesrs![Description] = strDescription proddesrs![summary] = strSummary proddesrs![Name] = strName proddeers.updateendif proddesrs.Close
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]"
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 =" & xxxxif proddeers.eof <> true then proddeers.edit proddesrs![Description] = strDescription proddesrs![summary] = strSummary proddesrs![Name] = strName proddeers.updateendif proddesrs.Close
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 :)
Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.
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