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