Access VBA too slow and the WEEKDAY() function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the VBA function WEEKDAY() to convert dates into the day of the
week. But it is too slow. Here is the detail of the problem:

I have a Access table named "TestTbl5" with three fields, one named
"Tran_Dt" (Date/Time type) and another named "DayOfWeek" (Number type,
default to -1). The table has about 1,100,000 records and I used the
following VBA code to convert "Tran_Dt" into day-of-the-week and store it in
"DayOfWeek"

----------------------------------------------
Dim Num As Integer
Set DlyDmdRD = New ADODB.Recordset
DlyDmdRD.CursorLocation = adUseClient
DlyDmdRD.Open "SELECT * FROM TestTbl5", CurrentProject.Connection,
adOpenKeyset, adLockBatchOptimistic

Do While Not DlyDmdRD.EOF
Num = Weekday(DlyDmdRD.Fields("Tran_Dt")) - 1
If Num = 0 Then Num = 7
DlyDmdRD.Fields("DayOfWeek") = Num
DlyDmdRD.MoveNext
Loop
DlyDmdRD.UpdateBatch
DlyDmdRD.Close
Set DlyDmdRD = Nothing
------------------------------------------------------
The code works. But it is so slow that it is taking more than 7 HOURS to
complete. Does anyone know of faster way to accomplish this. I considered the
SQL Scalar function DAYOFWEEK(), since it should be faster. But it does not
work in this context.

Many thanks!

Derek
 
Hi,
you can try to use update query instead:

currentdb.execute "Update TestTbl5 Set DayOfWeek= Weekday(Tran_Dt)"

btw, weekday has second argument which specifies first day of week
 
No. You are going the wrong way.

The ODBC Scalar function will be slower since it has to go through the JET
ODBC driver to be translated to something JET can understand.

A JET UPDATE Query will do it in a few seconds.

BTW, Weekday function will return values 1 to 7 so I am not sure of your If
statement.

Post back WHICH day you want to be day 1 of the week and I work out an
UPDATE SQL.
 
See if an update query is faster than looping through the records:
Dim strSql As String
strSql = "UPDATE TestTbl5 SET DayOfWeek = IIf(Weekday([Tran_Dt]) = 1, 7,
Weekday([Tran_Dt],2));"
dbEngine(0)(0).Execute strSql, dbFailOnError
 
I tried it and it worked like a charm!!!

Thanks a lot!

By the way, what would be a good source for learning Jet SQL? I have a book
"Access 2002 developer's handbook" but it does not cover this.


Allen Browne said:
See if an update query is faster than looping through the records:
Dim strSql As String
strSql = "UPDATE TestTbl5 SET DayOfWeek = IIf(Weekday([Tran_Dt]) = 1, 7,
Weekday([Tran_Dt],2));"
dbEngine(0)(0).Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Derek Chen said:
I am using the VBA function WEEKDAY() to convert dates into the day of the
week. But it is too slow. Here is the detail of the problem:

I have a Access table named "TestTbl5" with three fields, one named
"Tran_Dt" (Date/Time type) and another named "DayOfWeek" (Number type,
default to -1). The table has about 1,100,000 records and I used the
following VBA code to convert "Tran_Dt" into day-of-the-week and store it
in
"DayOfWeek"

----------------------------------------------
Dim Num As Integer
Set DlyDmdRD = New ADODB.Recordset
DlyDmdRD.CursorLocation = adUseClient
DlyDmdRD.Open "SELECT * FROM TestTbl5", CurrentProject.Connection,
adOpenKeyset, adLockBatchOptimistic

Do While Not DlyDmdRD.EOF
Num = Weekday(DlyDmdRD.Fields("Tran_Dt")) - 1
If Num = 0 Then Num = 7
DlyDmdRD.Fields("DayOfWeek") = Num
DlyDmdRD.MoveNext
Loop
DlyDmdRD.UpdateBatch
DlyDmdRD.Close
Set DlyDmdRD = Nothing
------------------------------------------------------
The code works. But it is so slow that it is taking more than 7 HOURS to
complete. Does anyone know of faster way to accomplish this. I considered
the
SQL Scalar function DAYOFWEEK(), since it should be faster. But it does
not
work in this context.

Many thanks!

Derek
Click to expand...
Click to expand...
 
I tried it and it worked like a charm!!!

Thanks a lot!

By the way, what would be a good source for learning Jet SQL? I have a book
"Access 2002 developer's handbook" but it does not cover this.
 
I tried the Update query and it worked like a charm!!!

Thanks a lot!

By the way, what would be a good source for learning Jet SQL? I have a book
"Access 2002 developer's handbook" but it does not cover this.
 
In Access 2003, if you open Help from the Code window, the Table of Contents
includes:
Microsoft Jet SQL Reference

As far as books, go, SQL for Mere Mortals is popular.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Derek Chen said:
I tried it and it worked like a charm!!!

Thanks a lot!

By the way, what would be a good source for learning Jet SQL? I have a
book
"Access 2002 developer's handbook" but it does not cover this.


Allen Browne said:
See if an update query is faster than looping through the records:
Dim strSql As String
strSql = "UPDATE TestTbl5 SET DayOfWeek = IIf(Weekday([Tran_Dt]) = 1,
7,
Weekday([Tran_Dt],2));"
dbEngine(0)(0).Execute strSql, dbFailOnError

Derek Chen said:
I am using the VBA function WEEKDAY() to convert dates into the day of
the
week. But it is too slow. Here is the detail of the problem:

I have a Access table named "TestTbl5" with three fields, one named
"Tran_Dt" (Date/Time type) and another named "DayOfWeek" (Number type,
default to -1). The table has about 1,100,000 records and I used the
following VBA code to convert "Tran_Dt" into day-of-the-week and store
it
in
"DayOfWeek"

----------------------------------------------
Dim Num As Integer
Set DlyDmdRD = New ADODB.Recordset
DlyDmdRD.CursorLocation = adUseClient
DlyDmdRD.Open "SELECT * FROM TestTbl5", CurrentProject.Connection,
adOpenKeyset, adLockBatchOptimistic

Do While Not DlyDmdRD.EOF
Num = Weekday(DlyDmdRD.Fields("Tran_Dt")) - 1
If Num = 0 Then Num = 7
DlyDmdRD.Fields("DayOfWeek") = Num
DlyDmdRD.MoveNext
Loop
DlyDmdRD.UpdateBatch
DlyDmdRD.Close
Set DlyDmdRD = Nothing
------------------------------------------------------
The code works. But it is so slow that it is taking more than 7 HOURS
to
complete. Does anyone know of faster way to accomplish this. I
considered
the SQL Scalar function DAYOFWEEK(), since it should be faster. But it
does not work in this context.
Click to expand...
Click to expand...
Click to expand...
 
Storing a calculation in a table is a big NO, NO. As long as you're
capturing the Date/Time in one field, there is *no need* to store the
DayOfWeek in another as there are various functions that will allow you
to extract that information when you view the form, run a query or print
a report.
 
Back
Top