I appreciate the feedback, but I don't think that is the problem. Consdering that this AM I let the code run for about 3 hours, and only 9 recordsets (9 jobs) were created. I don't want the flood the board, but I'll go ahead a post the entire sub that I'm using, plus the UDTs. I hope my naming conventions are understandable. Any advice you can think of would be great
Access 2000, Windows 200
THE FOLLOWING IS CONATINED IN A CODE MODUL
' Describes one field in a tabl
Public Type OneFiel
FName As Strin
DataType As Lon
End Typ
' Describes all the info needed about the update of one tabl
Public Type OneJo
' This is the info about the source of the updated dat
SourceTable As Strin
SourceCol As OneFiel
SLinkCol As OneFiel
' This is the table that is going to receive the updated dat
UpdateTable As Strin
UpdateCol As OneFiel
ULinkCol As OneFiel
End Typ
Public objJobs() As OneJo
THIS IS THE SLOW SU
Private Sub snr(
Dim J As Integer, intRowCount As Integer, i As Intege
Dim strSQL As String, strReplace As String, strWhere As String, strCap As String, strPer As Strin
Dim objConn As ADODB.Connectio
Dim objRS As ADODB.Recordse
Dim strR
' **********************************************************************************
' This sub loops through all the items in the objJobs array, and performs each updat
' described
' **********************************************************************************
For J = 0 To UBound(objJobs
lblStatus.Caption = "Processing (Job " & CStr(J + 1) & " of " & CStr(UBound(objJobs) + 1) & ")
strCap = lblStatus.Captio
Me.Repain
With objJobs(J
' Establish a connection to the current databas
Set objConn = CurrentProject.Connectio
strSQL = "SELECT [" & .SLinkCol.FName & "],[" & .SourceCol.FName & "] FROM [" & .SourceTable & "]
Set objRS = objConn.Execute(strSQL
strRS = objRS.GetRow
objRS.Clos
objConn.Clos
intRowCount = UBound(strRS, 2
For i = 0 To intRowCoun
lblStatus.Caption = strCap & vbCrLf & "Record " & CStr(i + 1) & " of " & CStr(intRowCount + 1
Me.Repain
If Not IsNull(strRS(0, i)) The
strWhere = strRS(0, i
Els
strWhere = "<NULL>
End I
' Check the data types of the various column
Select Case .ULinkCol.DataTyp
Case 0, 130, 200, 201, 202, 203 ' STRING value
strWhere = "'" & strWhere & "'
Case 7, 133, 134, 135 ' DAT
strWhere = "#" & strWhere & "#
Case Els
strWhere = strWher
End Selec
strWhere = "[" & .ULinkCol.FName & "]=" & strWher
If Not IsNull(strRS(1, i)) The
strReplace = strRS(1, i
Els
strReplace = "''
End I
Select Case .UpdateCol.DataTyp
Case 0, 130, 200, 201, 202, 203 ' STRING value
strReplace = "'" & strReplace & "'
Case 7, 133, 134, 135 ' DAT
strReplace = "#" & strReplace & "#
Case Els
strReplace = Val(strReplace
End Select
strSQL = "UPDATE [" & .UpdateTable & "] SET [" & .UpdateCol.FName & "]=" & strReplace & " WHERE " & strWhere
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Next
End With
Next
Set objRS = Nothing
Set objConn = Nothing
End Sub
----- Albert D. Kallal wrote: -----
A very slow pc can actually execute millions of VBA instructions per second.
So, in general if you loops are not executing several million times..then
speed of processing, or better put LACK OF processing is not your problem.
However, network bandwidth..and things like waiting for the operating system
to grab data is a whole new matter.
So, anything that opens a table now must:
First, the file has to be opened, and before that the file name has to
be verified if it exists. So, now we are waiting for the slow network or
speed of the operating system to come back to ms-access and tell that the
file exists. File systems tend to be very slow. We then wait for the table
to be opened (and, ms-access now has to create a locking file - again that
huge wait for the file system,a nd operating system to make that locking
file. ms-access then sets up some databuffres for the data to start flowing
from that table.
So, while it takes .00001 of second to execute the OpenRecordSet command,
all that huge huge huge amount of waiting for things to happen is where the
slow down occurs...and that is NOT the fault of ms-access. Mean, you can
have a super fast computer..but opening a file is still a slow process.
So, what that means is if you can eliminate ANY table/file opens in your
loop...you will speed things up by about 100 or more times.
So, if you have the choice between executing several lookups (sql
statements) in that loop..you are better to execute one sql, load up a
reocrd set..and do a find first on that (of course..if the recordset is more
then a few thousands..then now the findfirst will slow down).
