Code takes days to run...hoping to find a better way.

[KHerm]

I have a target table that has rows grouped by two columns, customer and order for the sake of argument. Customers can have many rows with the same order number. For each group of customers, I need to update the first row of a unique customer_number and order_number with a possible max and possible min value that is in another table, the source table where there is one row per customer - order. Here is the code I put in my module:

Function Update_Table()
Dim dbs As Database
Dim dssource As Recordset
Dim dstarget As Recordset
Dim mcriteria As String


Set dbs = CurrentDb
Set dssource = dbs.OpenRecordset("source_table", DB_OPEN_DYNASET)
Set dstarget = dbs.OpenRecordset("target_table", DB_OPEN_DYNASET)
dssource.MoveFirst
Do While Not dssource.EOF
mcriteria = "Customer_Number = " & """" & dssource("Customer_Number&quot & """" & "and Order_Number = " & dssource("Order_Number&quot & ""
dstarget.FindFirst mcriteria
If Not dstarget.NoMatch Then
dstarget.Edit
dstarget("PMAX&quot = dssource("PMAX&quot
dstarget("PMIN&quot = dssource("PMIN&quot
dstarget.Update
Else
MsgBox "There is something wrong"
End If
dssource.MoveNext
Loop
dssource.Close
dstarget.Close
Set dbs = Nothing
End Function

The target table has an index on customer_number (which is a text field) and has over 183,000 rows. It will be growing each month. When I called this code from a form using a macro which called the function, it took several days to run.

I'm hoping there's a better way.

Thanks for any help in advance.

Ken

 

[lameid]

The following method is faster but it isn't pretty if you are using a query as your source and not a table. The thing to know is that recordsets are inherently slow so anytime you can do something in a query instead of a recordset, it is faster.

That said, it looks to me like you just have two tables here. You should be able to join the tables on the criteria you are using and update based on the other fields. Just be sure to use sqare brackets "[]" around table and field names to ensure you don't end up with literal text.

If you are using a query that is not updateable, you will need to make it into an append table to append all the records to a table and then use the result table to make the update query mentioned above. The problem here is that a temp table in Access makes compacting necessary unless... You create a temporary backend file and place it somewhere. Then in your code you use filecopy to copy to a new location. You link the new location in your database. Now everytime your code runs it copies a temporary backend and upands to the temp table and then updates based on the temp table. Because of the filecopy and multiple transactions, there is a lot of initial overhead here and probably would be slower for a smaller dataset but in this case you are incurring too much overhead on each record.

 

[KHerm]

Thanks for your quick response, but I don't know how I can make a query, joined on my source and target tables, update only the first row of my customer_order group.

 

[ErikZ]

Are you actually using Access, or is this all in VB?

 

[KHerm]

The code I provided is a module function in VB. My tables are Access tables. Is there such a thing as update first or update where first in Access queries?

 

[KHerm]

I should give myself a star for the solution on this one. But lameid did keep me thinking about using an update query.

My problem was solved (and cut the run time from 2 1/2 days to 20 minutes) by putting a key column on my target table. My source table is actually created from the target table so I was able to get the right key number for the first row of the Customer_number/Customer_order group. So I could easily update my columns from the target table where key=key, cust_num=cust_num, ord_num=ord_num from the two tables.

Why didn't I think of that sooner????

Ken H.

 

[lameid]

Access uses SQL for queries... It isn't quite ANSI but close enough.

Insert Into is the statment you would use...

Something like...

UPDATE BILLTO INNER JOIN SHIPTO ON BILLTO.CUST = SHIPTO.CUST SET SHIPTO.CUSTNAME = [Billto].[Billname];

The above would update the Custname field in Shipto with the Billname field from billto where the cust fields matched. You might save some time but 20 minutes is not too bad...