Speed up code

[TonyLackey]

I have this code:-

Function ChangeDate(adate As String) As String
Dim t As Integer
Dim num As String
Dim num2 As String
Dim zyear As String
Dim yyear As String
Set dbcurrent = currentdb()
num2 = Right(adate, 4) 'This is assigning the last 4 digits in the number to num2
zyear = 19
yyear = 20

For t = 0 To t = dbcurrent.Tabledefs("TableTest").RecordCount 'This line is counting each line in table1

If Left(adate, 7) < 1000000 Then 'If date is less than jan 01 2000 then 19 is appended to the front
adate = zyear & Left(adate, 2) & num2
Else
adate = yyear & Mid(adate, 2, 2) & num2 ' Otherwise 20 is appended to front
End If
Next

ChangeDate = adate

End Function

Which is trying to update a field in an access table.

However when updating 10' of thousands of records it is slow, is there any way of making it faster.

cheers.

 

[ritchieroo]

Why do you have this For...Next loop in your code? It lloks as if you want it to loop through each record in TableTest, but it doesn't do that the way you have it written. Instead it does exactly the same thing over and over depending on how many rows you have in your table.

I'm assuming that you are using your function in a query so if your table has 10,000 rows on it, your loop will execute 100,000,000 times!

For t = 0 To t = dbcurrent.Tabledefs("TableTest").RecordCount 'This line is counting each line in table1 

  If Left(adate, 7) < 1000000 Then 'If date is less than jan 01 2000 then 19 is appended to the front 
    adate = zyear & Left(adate, 2) & num2 
  Else 
    adate = yyear & Mid(adate, 2, 2) & num2 ' Otherwise 20 is appended to front 
  End If 
Next

on a general point, a SQL update statement will almost always be faster than using a VBA function. To get really fast operations try to re-write your function as SQL.

You could still encapsulate the SQL in a VBA function if needed.

 

[TonyLackey]

ritchieroo,

Thanks for the tip. I will try it in a SQL statement. As you can guess i'm not a programmer at all......

I was trying to create a funtion that I can re-use many times within the same access query, especially when creating record sets. I'm more familiar with SQL so i'll stick to what i know.

Thanks for the pointer

T