speed up code 1

[scottian]

Im using this code to create a common link between records in a table. The problem i have is that the table contains 87000 records an this update will take approx 4 hours to run.

does anyone have any ideas how to speed this code up


'========================================
Function CreateLink()
'---------------------------------------
Dim ThisDB As DAO.Database
Dim MySet As DAO.Recordset
Dim strSQL As String
Dim MyVal1 As Variant
Dim e As Integer
Dim c As Long
Dim d As Long

e = 0
DoCmd.SetWarnings False
If IsNull(DMax("[Index]", "table1")) Then
c = 1
Else
c = DMax("[Index]", "table1") + 1
End If
d = DCount("[FIELD1]", "table1", "[Index] is null")
Do While e < d
strSQL = "SELECT * FROM Top1 WHERE index is null"
Set ThisDB = CurrentDb()
Set MySet = ThisDB.OpenRecordset(strSQL, dbOpenDynaset)
MyVal1 = MySet!FIELD1
If MyVal1 = "TRANS" Then
c = c + 1
Else
End If
DoCmd.RunSQL "UPDATE Top1 SET Top1.[Index] = " & c & " ;"
e = e + 1
NLeft = DCount("[FIELD1]", "table1", "[Index] is null")
Loop
DoCmd.SetWarnings True
End Function
'-------------------------------------------

"My God! It's full of stars...

 

[p27br]

hi

i can't see what NLeft is for ?

 

[danvlas]

First If-Then-Else can be written like:

c=Val(Nz(DMax("[Index]", "table1"),0))+1

This will evaluate DMax only once.


A better option would be to use a recordset:
Select Val(Nz(Max(Index),0))+1 as SomeName From Table1

which would return exactly the next value

You could also try a recordset ordered by Index descending and take the first record:

strSQL = "Select index from Table1 Order By Index desc;"
Set rst = CurrentDb.OpenRecordset(strSQL,dbOpenForwardOnly)
with rst
If .EOF and .BOF then
c=1
Else
c = .Fields(0) + 1
End If
.Close
end With
set rst=nothing


In the second part, i don't see the use of DCount...

strSQL = "SELECT * FROM Top1 WHERE index is null And Field1='TRANS'"
set ThisDB = CurrentDb()
Set MySet = ThisDB.OpenRecordset(strSQL, dbOpenDynaset)
With MySet
While Not .EOF
.Edit
.Fields("Index") = c
c = c + 1
.Update
.MoveNext
Wend
.Close
End With
Set MySet = Nothing

This line can remain the same.
NLeft = DCount("[FIELD1]", "table1", "[Index] is null")

HTH





Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[scottian]

Please ignore. its a counter of how many recpords are left to update

"My God! It's full of stars...

 

[scottian]

Danvlas,

you sound like you know what you are talking about with regards to recordsets, which is how i initially thought the code may speed up. its the fact that im using a query to pull the first record and check for the value in field1. this where i think it slows down.

so can you explain a little more clearly how recordsets work and how they are created. i am using a recordset at the moment to check the value, but i havent a clue how it works, someone else wrote it in this forum

"My God! It's full of stars...

 

[Elysium]

The DCount and DMax are definitely working against you. I concur with danvlas about using a Select statement to arrive at a value.

Randy

 

[danvlas]

One recordset is one 'collection' of records. It's similar to a query, except the result is not displayed on the screen.

How they work:
They are created and stored in the memory:
Set MySet = db.OpenRecordset(SqlStatement, dbOpenDynaset, dbPessimistic)
This creates a recordset having the following characteristics:
- holds the records returned by the SqlStatement
- is a dynamic recordset, meaning you can write to it. Other possibilities:
* dbOpenSnapshot - you can navigate through the records, but it's read-only. It's faster than a dynamic set
* dbOpenForwardOnly - you can navigate through it only downwards and it's read-only. Fastest recordset

They contain a collection of fields, which can be referred to either by name or by their ordinal position:

rst.Fields("FirstColumnName") is the same as rst.Fields(0)

When referring to the value in one field of the recordset it is actually about the value in the current record.
To locate a specific record in a recordset, you can use the FindFirst method:

rst.FindFirst("WhereClauseWithoutTheWordWhere")
Note: FindFirst is applicable only to DAO.Recordsets. Newer ADO does not support this method.

More info on recordsets can be accessed by using the Help file.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[scottian]

This code has worked perfectly :-

strSQL = "SELECT * FROM Top1 WHERE index is null And Field1='TRANS'"
set ThisDB = CurrentDb()
Set MySet = ThisDB.OpenRecordset(strSQL, dbOpenDynaset)
With MySet
While Not .EOF
.Edit
.Fields("Index") = c
c = c + 1
.Update
.MoveNext
Wend
.Close
End With
Set MySet = Nothing


-----


but its only inserting the value of "C" into the records that contain "TRANS" and the value of "C" increments. how do i make it insert the value of "C" into all other records that follow and still only inrement when it hhits the "TRANS" value, what im trying to do is create a common link, Im interested in the TRANS record and every subsequent record after that until another TRANS record is detected
i.e
INDEX Field1
1 TRANS
1 R1
1 R2
2 TRANS
2 R1
2 R2
3 TRANS
3 R1
3 R2



"My God! It's full of stars...

 

[scottian]

Never mind, I managed to sort it.

and how fast is that?
amazing, from 4 hours to just under a minute. i cant believe it.

thanks so much.

"My God! It's full of stars...