Slow-Running Sub

[MDW]

Hey all

Posted this last week but I don't think I did it very well. I've created an Access database that allows you to link to an external source (such as an Excel worksheet or another Access database) and perform search-and-replace functions to another linked source (Excel, Access, etc.). This has a myriad of uses and is actually quite useful (mostly because of the elimination of human error). But the actual processing of the updates seems to take forever. Here is the code in question (and the definition of a UDT). Is there some reason that this sub could take so long to run? (For instance, I had 73 records to update, and I started it before I began typing this post, and it's still not done yet.

Here is the code - watch for line wrap

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 ' DATE

strReplace = "#" & strReplace & "#"

Case Else

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

 

[RobFMS]

When the application starts, create a global variable to the CurrentProject
Use the global variable within the sub. You should find an increase in time.

Let me know how this goes.

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hey all,

Posted this last week but I don't think I did it very well. I've created

an Access database that allows you to link to an external source (such as an
Excel worksheet or another Access database) and perform search-and-replace
functions to another linked source (Excel, Access, etc.). This has a myriad
of uses and is actually quite useful (mostly because of the elimination of
human error). But the actual processing of the updates seems to take
forever. Here is the code in question (and the definition of a UDT). Is
there some reason that this sub could take so long to run? (For instance, I
had 73 records to update, and I started it before I began typing this post,
and it's still not done yet.)

Here is the code - watch for line wrap.

Access 2000, Windows 2000

THE FOLLOWING IS CONATINED IN A CODE MODULE
' Describes one field in a table
Public Type OneField

FName As String
DataType As Long

End Type

' Describes all the info needed about the update of one table
Public Type OneJob

' This is the info about the source of the updated data
SourceTable As String
SourceCol As OneField
SLinkCol As OneField

' This is the table that is going to receive the updated data
UpdateTable As String
UpdateCol As OneField
ULinkCol As OneField

End Type

Public objJobs() As OneJob

THIS IS THE SLOW SUB
Private Sub snr()

Dim J As Integer, intRowCount As Integer, i As Integer
Dim strSQL As String, strReplace As String, strWhere As String, strCap As String, strPer As String
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strRS


' ****************************************************************************
*******
' This sub loops through all the items in the objJobs array, and performs each update
' described.
' ****************************************************************************
*******

For J = 0 To UBound(objJobs)

lblStatus.Caption = "Processing (Job " & CStr(J + 1) & " of " &

CStr(UBound(objJobs) + 1) & ")"

strCap = lblStatus.Caption
Me.Repaint

With objJobs(J)

' Establish a connection to the current database
Set objConn = CurrentProject.Connection

strSQL = "SELECT [" & .SLinkCol.FName & "],[" &

..SourceCol.FName & "] FROM [" & .SourceTable & "]"

Set objRS = objConn.Execute(strSQL)
strRS = objRS.GetRows

objRS.Close
objConn.Close

intRowCount = UBound(strRS, 2)

For i = 0 To intRowCount

lblStatus.Caption = strCap & vbCrLf & "Record " & CStr(i +

1) & " of " & CStr(intRowCount + 1)

Me.Repaint

If Not IsNull(strRS(0, i)) Then

strWhere = strRS(0, i)

Else

strWhere = "<NULL>"

End If

' Check the data types of the various columns
Select Case .ULinkCol.DataType

Case 0, 130, 200, 201, 202, 203 ' STRING values

strWhere = "'" & strWhere & "'"

Case 7, 133, 134, 135 ' DATE

strWhere = "#" & strWhere & "#"

Case Else

strWhere = strWhere

End Select

strWhere = "[" & .ULinkCol.FName & "]=" & strWhere

If Not IsNull(strRS(1, i)) Then

strReplace = strRS(1, i)

Else

strReplace = "''"

End If

Select Case .UpdateCol.DataType

Case 0, 130, 200, 201, 202, 203 ' STRING values

strReplace = "'" & strReplace & "'"

Case 7, 133, 134, 135 ' DATE

strReplace = "#" & strReplace & "#"

Case Else

strReplace = Val(strReplace)

End Select

strSQL = "UPDATE [" & .UpdateTable & "] SET [" &

..UpdateCol.FName & "]=" & strReplace & " WHERE " & strWhere

 

[RobFMS]

You found no increase in speed by taking the opening and closing the
CurrentProject.Connection out the loop (i.e. using a global variable
instead) ???

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Not noticing an increase in speed. Should I use DAO instead of ADO? I've

been playing with that, but I can't seem to create a recordset object using
DAO.

 

[gandalf]

note:
I'm not so familiar with what happens behind the scenes

1)if possible, try to limit queried by
strSQL = "SELECT [" & .SLinkCol.FName & "],["
& .SourceCol.FName & "] FROM [" & .SourceTable & "]"
Set objRS = objConn.Execute(strSQL)

to 1 or 0 with TOP 1

2)you are repainting your form
Me.Repaint
->is it a big form/small form
perhaps using the status-bar instead?

3)if you use DAO, you could use the tabledef object to
query the field-properties.

4)Have you tried to leave a portion out of the sub to see
what takes so long (perhaps the update?,repaint,...)

5)see
http://www.granite.ab.ca/access/
http://www.able-consulting.com/
http://www.mvps.org/access/
http://members.rogers.com/douglas.j.steele/
http://www.lebans.com/

for some great access-hints that may apply
(autocorrect...)

-----Original Message-----
Hey all,

Posted this last week but I don't think I did it very

well. I've created an Access database that allows you to
link to an external source (such as an Excel worksheet or
another Access database) and perform search-and-replace
functions to another linked source (Excel, Access, etc.).
This has a myriad of uses and is actually quite useful
(mostly because of the elimination of human error). But
the actual processing of the updates seems to take
forever. Here is the code in question (and the definition
of a UDT). Is there some reason that this sub could take
so long to run? (For instance, I had 73 records to update,
and I started it before I began typing this post, and it's
still not done yet.)

Here is the code - watch for line wrap.

Access 2000, Windows 2000

THE FOLLOWING IS CONATINED IN A CODE MODULE
' Describes one field in a table
Public Type OneField

FName As String
DataType As Long

End Type

' Describes all the info needed about the update of one table
Public Type OneJob

' This is the info about the source of the updated data
SourceTable As String
SourceCol As OneField
SLinkCol As OneField

' This is the table that is going to receive the updated data
UpdateTable As String
UpdateCol As OneField
ULinkCol As OneField

End Type

Public objJobs() As OneJob

THIS IS THE SLOW SUB
Private Sub snr()

Dim J As Integer, intRowCount As Integer, i As Integer
Dim strSQL As String, strReplace As String, strWhere As

String, strCap As String, strPer As String

Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strRS


' ***********************************************************
************************
' This sub loops through all the items in the objJobs

array, and performs each update

' described.
' ***********************************************************
************************

For J = 0 To UBound(objJobs)

lblStatus.Caption = "Processing (Job " & CStr(J +

1) & " of " & CStr(UBound(objJobs) + 1) & ")"

strCap = lblStatus.Caption
Me.Repaint

With objJobs(J)

' Establish a connection to the current database
Set objConn = CurrentProject.Connection

strSQL = "SELECT [" & .SLinkCol.FName & "],["

& .SourceCol.FName & "] FROM [" & .SourceTable & "]"

Set objRS = objConn.Execute(strSQL)
strRS = objRS.GetRows

objRS.Close
objConn.Close

intRowCount = UBound(strRS, 2)

For i = 0 To intRowCount

lblStatus.Caption = strCap & vbCrLf

& "Record " & CStr(i + 1) & " of " & CStr(intRowCount + 1)

Me.Repaint

If Not IsNull(strRS(0, i)) Then

strWhere = strRS(0, i)

Else

strWhere = "<NULL>"

End If

' Check the data types of the various columns
Select Case .ULinkCol.DataType

Case 0, 130, 200, 201, 202, 203 ' STRING values

strWhere = "'" & strWhere & "'"

Case 7, 133, 134, 135 ' DATE

strWhere = "#" & strWhere & "#"

Case Else

strWhere = strWhere

End Select

strWhere = "[" & .ULinkCol.FName & "]=" & strWhere

If Not IsNull(strRS(1, i)) Then

strReplace = strRS(1, i)

Else

strReplace = "''"

End If

Select Case .UpdateCol.DataType

Case 0, 130, 200, 201, 202, 203 ' STRING values

strReplace = "'" & strReplace & "'"

Case 7, 133, 134, 135 ' DATE

strReplace = "#" & strReplace & "#"

Case Else

strReplace = Val(strReplace)

End Select

strSQL = "UPDATE [" & .UpdateTable & "]

SET [" & .UpdateCol.FName & "]=" & strReplace & " WHERE "
& strWhere

 

[RobFMS]

Before you load the forms (or any other forms at that), you need to create
the CurrentProject.Connection and then set it to the global variable that
you defined. If you have a Startup procedure or a Main procedure, this is
where you would create the connection and set it to the global variable.

Also, comment out the Me.Repaint statements.

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

That is correct. This is code executing on a form, so I made a

module-level variable called objConn - and during form_load I set it to
CurrentProject.Connection. No change.

Also, it's worth nothing that the "For J = 0 To UBound(objJobs)" loop (the

loop in which the connection object is used) doesn't execute very often - 2
or 4 times. The loop that executes most often is the "For i = 0 To
intRowCount" loop. That's why I used GetRows. However, I can't seem to find
a way to make that work in DAO.

 

[MDW]

Comments inlin

----- gandalf wrote: ----

note
I'm not so familiar with what happens behind the scene

Cliff's Notes version - my department makes heavy use of Excel/Access data (primarily Excel), and a lot of time is spent transferring updated into between sheets. They maintain a fairly DB-like structure, inasmuch as all this data is associated with a non-repeating character code. They will frequently get updated info from management and these changes need to percolate out to many spreadhseets. They currently use VLookups across multiple books on different share drives, a manual and error-prone process

Using linked spreadsheets (and/or linked tables), this app is designed to take new data en masse and move it to a designated destination.

The short-short version: We get updated sales numbers for 72 out of 600 stores. These numbers need to go to multiple spreadsheets. Using the new numbers as the "source", I create a recordset, loop through it, and update the destination table (spreadsheet) with the new number. I can do this because both the source and destination sheets have the store number.

1)if possible, try to limit queried by
strSQL = "SELECT [" & .SLinkCol.FName & "],["
& .SourceCol.FName & "] FROM [" & .SourceTable & "]
Set objRS = objConn.Execute(strSQL

to 1 or 0 with TOP

Can't. I NEED all the info from this table, as it is my source

2)you are repainting your for
Me.Repain
->is it a big form/small for
perhaps using the status-bar instead

It's not a huge form, but it's not tiny either. Does repainting take up a lot of time? I update the caption box really for myself more than anything. So that I don't just see an hourglass...just some reassurance that SOMETHING is happening. I've never used the status bar....what do you mean by that? I can lose the whole repainting thing if it is causing a lot of lag.

3)if you use DAO, you could use the tabledef object to
query the field-properties

I've already queried the field properties. Part of the UDT is the data type. The select statements just pull the UDT info

4)Have you tried to leave a portion out of the sub to see
what takes so long (perhaps the update?,repaint,...

5)see
http://www.granite.ab.ca/access
http://www.able-consulting.com
http://www.mvps.org/access
http://members.rogers.com/douglas.j.steele
http://www.lebans.com

for some great access-hints that may appl
(autocorrect...

-----Original Message----
Hey all

well. I've created an Access database that allows you to
link to an external source (such as an Excel worksheet or
another Access database) and perform search-and-replace
functions to another linked source (Excel, Access, etc.).
This has a myriad of uses and is actually quite useful
(mostly because of the elimination of human error). But
the actual processing of the updates seems to take
forever. Here is the code in question (and the definition
of a UDT). Is there some reason that this sub could take
so long to run? (For instance, I had 73 records to update,
and I started it before I began typing this post, and it's
still not done yet.

' Describes one field in a tabl
Public Type OneFiel tabl
Public Type OneJo dat
SourceTable As Strin
SourceCol As OneFiel
SLinkCol As OneFiel updated dat
UpdateTable As Strin
UpdateCol As OneFiel
ULinkCol As OneField
Dim strSQL As String, strReplace As String, strWhere As

String, strCap As String, strPer As String

Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strRS ***********************************************************
************************
' This sub loops through all the items in the objJobs

array, and performs each update

' described.
' ***********************************************************
************************

1) & " of " & CStr(UBound(objJobs) + 1) & ")"

strCap = lblStatus.Caption
Me.Repaint

With objJobs(J)
' Establish a connection to the current

database
Set objConn = CurrentProject.Connection

strSQL = "SELECT [" & .SLinkCol.FName & "],["

& .SourceCol.FName & "] FROM [" & .SourceTable & "]"

Set objRS = objConn.Execute(strSQL)
strRS = objRS.GetRows

& "Record " & CStr(i + 1) & " of " & CStr(intRowCount + 1)

Me.Repaint

If Not IsNull(strRS(0, i)) Then
strWhere = strRS(0, i)
Else
strWhere = "<NULL>"
End If
' Check the data types of the various

columns
Select Case .ULinkCol.DataType

Case 0, 130, 200, 201, 202, 203 ' STRING values
strWhere = "'" & strWhere & "'"
Case 7, 133, 134, 135 ' DATE
strWhere = "#" & strWhere & "#"
Case Else
strWhere = strWhere
End Select
strWhere = "[" & .ULinkCol.FName & "]=" & strWhere
If Not IsNull(strRS(1, i)) Then
strReplace = strRS(1, i)
Else
strReplace = "''"
End If
Select Case .UpdateCol.DataType
Case 0, 130, 200, 201, 202, 203 ' STRING values
strReplace = "'" & strReplace & "'"
Case 7, 133, 134, 135 ' DATE
strReplace = "#" & strReplace & "#"
Case Else
strReplace = Val(strReplace)
End Select
strSQL = "UPDATE [" & .UpdateTable & "]

SET [" & .UpdateCol.FName & "]=" & strReplace & " WHERE "
& strWhere

 

[Phil Hunt]

virus ?

Comments inline

----- gandalf wrote: -----

note:
I'm not so familiar with what happens behind the scenes

Cliff's Notes version - my department makes heavy use of Excel/Access data

(primarily Excel), and a lot of time is spent transferring updated into
between sheets. They maintain a fairly DB-like structure, inasmuch as all
this data is associated with a non-repeating character code. They will
frequently get updated info from management and these changes need to
percolate out to many spreadhseets. They currently use VLookups across
multiple books on different share drives, a manual and error-prone process.

Using linked spreadsheets (and/or linked tables), this app is designed to

take new data en masse and move it to a designated destination.

The short-short version: We get updated sales numbers for 72 out of 600

stores. These numbers need to go to multiple spreadsheets. Using the new
numbers as the "source", I create a recordset, loop through it, and update
the destination table (spreadsheet) with the new number. I can do this
because both the source and destination sheets have the store number.

1)if possible, try to limit queried by
strSQL = "SELECT [" & .SLinkCol.FName & "],["
& .SourceCol.FName & "] FROM [" & .SourceTable & "]"
Set objRS = objConn.Execute(strSQL)

to 1 or 0 with TOP 1

Can't. I NEED all the info from this table, as it is my source.

2)you are repainting your form
Me.Repaint
->is it a big form/small form
perhaps using the status-bar instead?

It's not a huge form, but it's not tiny either. Does repainting take up a

lot of time? I update the caption box really for myself more than anything.
So that I don't just see an hourglass...just some reassurance that SOMETHING
is happening. I've never used the status bar....what do you mean by that?
I can lose the whole repainting thing if it is causing a lot of lag.

3)if you use DAO, you could use the tabledef object to
query the field-properties.

I've already queried the field properties. Part of the UDT is the data

type. The select statements just pull the UDT info.

4)Have you tried to leave a portion out of the sub to see
what takes so long (perhaps the update?,repaint,...)

5)see
http://www.granite.ab.ca/access/
http://www.able-consulting.com/
http://www.mvps.org/access/
http://members.rogers.com/douglas.j.steele/
http://www.lebans.com/

for some great access-hints that may apply
(autocorrect...)

-----Original Message-----
Hey all,

well. I've created an Access database that allows you to
link to an external source (such as an Excel worksheet or
another Access database) and perform search-and-replace
functions to another linked source (Excel, Access, etc.).
This has a myriad of uses and is actually quite useful
(mostly because of the elimination of human error). But
the actual processing of the updates seems to take
forever. Here is the code in question (and the definition
of a UDT). Is there some reason that this sub could take
so long to run? (For instance, I had 73 records to update,
and I started it before I began typing this post, and it's
still not done yet.)

' Describes one field in a table
Public Type OneField table
Public Type OneJob data
SourceTable As String
SourceCol As OneField
SLinkCol As OneField updated data
UpdateTable As String
UpdateCol As OneField
ULinkCol As OneField
Dim strSQL As String, strReplace As String, strWhere As

String, strCap As String, strPer As String

Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strRS ***********************************************************
************************
' This sub loops through all the items in the objJobs

array, and performs each update

' described.
' ***********************************************************
************************

1) & " of " & CStr(UBound(objJobs) + 1) & ")"

strCap = lblStatus.Caption
Me.Repaint

With objJobs(J)
' Establish a connection to the current

database
Set objConn = CurrentProject.Connection

strSQL = "SELECT [" & .SLinkCol.FName & "],["

& .SourceCol.FName & "] FROM [" & .SourceTable & "]"

Set objRS = objConn.Execute(strSQL)
strRS = objRS.GetRows

& "Record " & CStr(i + 1) & " of " & CStr(intRowCount + 1)

Me.Repaint

If Not IsNull(strRS(0, i)) Then
strWhere = strRS(0, i)
Else
strWhere = "<NULL>"
End If
' Check the data types of the various

columns
Select Case .ULinkCol.DataType

Case 0, 130, 200, 201, 202, 203 ' STRING values
strWhere = "'" & strWhere & "'"
Case 7, 133, 134, 135 ' DATE
strWhere = "#" & strWhere & "#"
Case Else
strWhere = strWhere
End Select
strWhere = "[" & .ULinkCol.FName & "]=" & strWhere
If Not IsNull(strRS(1, i)) Then
strReplace = strRS(1, i)
Else
strReplace = "''"
End If
Select Case .UpdateCol.DataType
Case 0, 130, 200, 201, 202, 203 ' STRING values
strReplace = "'" & strReplace & "'"
Case 7, 133, 134, 135 ' DATE
strReplace = "#" & strReplace & "#"
Case Else
strReplace = Val(strReplace)
End Select
strSQL = "UPDATE [" & .UpdateTable & "]

SET [" & .UpdateCol.FName & "]=" & strReplace & " WHERE "
& strWhere

DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
.

 

[Dirk Goldgar]

Hey all,

Posted this last week but I don't think I did it very well. I've
created an Access database that allows you to link to an external
source (such as an Excel worksheet or another Access database) and
perform search-and-replace functions to another linked source (Excel,
Access, etc.). This has a myriad of uses and is actually quite useful
(mostly because of the elimination of human error). But the actual
processing of the updates seems to take forever. Here is the code in
question (and the definition of a UDT). Is there some reason that
this sub could take so long to run? (For instance, I had 73 records
to update, and I started it before I began typing this post, and it's
still not done yet.)

Here is the code - watch for line wrap.

Access 2000, Windows 2000

THE FOLLOWING IS CONATINED IN A CODE MODULE
' Describes one field in a table
Public Type OneField

FName As String
DataType As Long

End Type

' Describes all the info needed about the update of one table
Public Type OneJob

' This is the info about the source of the updated data
SourceTable As String
SourceCol As OneField
SLinkCol As OneField

' This is the table that is going to receive the updated data
UpdateTable As String
UpdateCol As OneField
ULinkCol As OneField

End Type

Public objJobs() As OneJob

THIS IS THE SLOW SUB
Private Sub snr()

Dim J As Integer, intRowCount As Integer, i As Integer
Dim strSQL As String, strReplace As String, strWhere As String,
strCap As String, strPer As String
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strRS


'
************************************************************************
***********
' This sub loops through all the items in the objJobs array, and
performs each update ' described. '
************************************************************************
***********

For J = 0 To UBound(objJobs)

lblStatus.Caption = "Processing (Job " & CStr(J + 1) & " of "
& CStr(UBound(objJobs) + 1) & ")" strCap = lblStatus.Caption
Me.Repaint

With objJobs(J)

' Establish a connection to the current database
Set objConn = CurrentProject.Connection

strSQL = "SELECT [" & .SLinkCol.FName & "],[" &
.SourceCol.FName & "] FROM [" & .SourceTable & "]" Set
objRS = objConn.Execute(strSQL) strRS = objRS.GetRows

objRS.Close
objConn.Close

intRowCount = UBound(strRS, 2)

For i = 0 To intRowCount

lblStatus.Caption = strCap & vbCrLf & "Record " &
CStr(i + 1) & " of " & CStr(intRowCount + 1)
Me.Repaint

If Not IsNull(strRS(0, i)) Then

strWhere = strRS(0, i)

Else

strWhere = "<NULL>"

End If

' Check the data types of the various columns
Select Case .ULinkCol.DataType

Case 0, 130, 200, 201, 202, 203 ' STRING values

strWhere = "'" & strWhere & "'"

Case 7, 133, 134, 135 ' DATE

strWhere = "#" & strWhere & "#"

Case Else

strWhere = strWhere

End Select

strWhere = "[" & .ULinkCol.FName & "]=" & strWhere

If Not IsNull(strRS(1, i)) Then

strReplace = strRS(1, i)

Else

strReplace = "''"

End If

Select Case .UpdateCol.DataType

Case 0, 130, 200, 201, 202, 203 ' STRING values

strReplace = "'" & strReplace & "'"

Case 7, 133, 134, 135 ' DATE

strReplace = "#" & strReplace & "#"

Case Else

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

YIKES! MDW, you are closing the current connection inside your loop!
Why on earth would you do that? If this request is being honored at
all, I'd guess Access has to reopen the connection immediately after
you close it. This is bound to take a lot of time. Try moving these
lines ...

' Establish a connection to the current database
Set objConn = CurrentProject.Connection

.... outside the "For J = 0 To UBound(objJobs)" loop, and delete this
line ...

objConn.Close

entirely! You did not open that connection, and you most certainly
should not close it.

 

[Dirk Goldgar]

OK, well, I parsed out the code, and I find that the thing executes
blazingly fast if I comment out the line that actually executes the
SQL code to update the table. Not a surprise I suppose, but I'm not
sure where to go from here.

What is the single fastest way to send SQL commands to the DB?
DocMd.RunSQL, CurrentDB.Execute, etc?

If I create a query in the query designer that updates all the
columns in one of the tables (say, 422 records), that update query
runs pretty fast. Why don't my update queries run as fast? I'd be
willing to accept that there'd be SOME loss of speed because of the
way I'm doing it, but it just seems vastly slower than it aught to
be.

Since you have a reference, objConn, to the current project's Connection
object, why not use

objConn.Execute strSQL

?

 

[Dirk Goldgar]

I appreciate the help that everyone has been giving, but
unfortunately I think there is something else limiting the
performance. I think I mentioned it, but the source "tables" are, by
and large, linked spreadsheets pointing to Excel workbooks on various
network drives. Although queries using the Query Designer run very
quickly on these sheets, I think that for some reason my network is
causing the problem. It just seems like 3 minutes to update 43
records is horrible given what we can do, but that's where I'm at.

FYI, here's the most recent code, which doesn't seem to be running a
whit faster than when I first posted.

Hmm. Are these spreadsheets then represented as linked tables in the
database? It could be the slowness is due to the time involved in
opening the connections to each workbook, and that could be due either
to the overhead in mapping the Excel sheets to a table, or to network
slowness in connecting to the workbook or network drive. You could test
for network issues by copying all the workbooks to your local drive,
relinking to them there, and seeing if the code runs any faster. You
could also look to see how long it takes just to open each of these
linked tables in datasheet view.

Here are a few vague ideas:

If you have to update multiple "tables" on the same server, you might
try opening a connection to that server first, and holding it open while
you update the various tables.

You might try copying each linked table to be updated into a local
Access table, in the current database or in a temporary database you
create for the purpose. Then update the local table, and then copy it
back to the linked table.

 

[david epsom dot com dot au]

If I read this right, you are executing a seperate update for each row in
the 'table'.
That is not normally neccessary. You can include the string manipulation
inside the SQL, and run one update for the entire 'table':
ie for a date column:
"UPDATE [" & .UpdateTable & "] SET [" & .UpdateCol.FName & "]= # & [" &
..UpdateCol.FName & "] & #;"

Where you are still doing seperate updates, you need to take steps to ensure
that the XLS remains open. You can probably just open a copy of the 'table'
at the begining of the routine, and leave it open until you finish. (You
may have to set the spread sheet to allow shared access). There are two
reasons for doing this: (1) The overhead of opening, saving, and closing the
file is enormous, (2) When you open a file, you AV software typically kicks
in and scans it again.

(david)

Hey all,

Posted this last week but I don't think I did it very well. I've created

an Access database that allows you to link to an external source (such as an
Excel worksheet or another Access database) and perform search-and-replace
functions to another linked source (Excel, Access, etc.). This has a myriad
of uses and is actually quite useful (mostly because of the elimination of
human error). But the actual processing of the updates seems to take
forever. Here is the code in question (and the definition of a UDT). Is
there some reason that this sub could take so long to run? (For instance, I
had 73 records to update, and I started it before I began typing this post,
and it's still not done yet.)

Here is the code - watch for line wrap.

Access 2000, Windows 2000

THE FOLLOWING IS CONATINED IN A CODE MODULE
' Describes one field in a table
Public Type OneField

FName As String
DataType As Long

End Type

' Describes all the info needed about the update of one table
Public Type OneJob

' This is the info about the source of the updated data
SourceTable As String
SourceCol As OneField
SLinkCol As OneField

' This is the table that is going to receive the updated data
UpdateTable As String
UpdateCol As OneField
ULinkCol As OneField

End Type

Public objJobs() As OneJob

THIS IS THE SLOW SUB
Private Sub snr()

Dim J As Integer, intRowCount As Integer, i As Integer
Dim strSQL As String, strReplace As String, strWhere As String, strCap As String, strPer As String
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strRS


' ****************************************************************************
*******
' This sub loops through all the items in the objJobs array, and performs each update
' described.
' ****************************************************************************
*******

For J = 0 To UBound(objJobs)

lblStatus.Caption = "Processing (Job " & CStr(J + 1) & " of " &

CStr(UBound(objJobs) + 1) & ")"

strCap = lblStatus.Caption
Me.Repaint

With objJobs(J)

' Establish a connection to the current database
Set objConn = CurrentProject.Connection

strSQL = "SELECT [" & .SLinkCol.FName & "],[" &

..SourceCol.FName & "] FROM [" & .SourceTable & "]"

Set objRS = objConn.Execute(strSQL)
strRS = objRS.GetRows

objRS.Close
objConn.Close

intRowCount = UBound(strRS, 2)

For i = 0 To intRowCount

lblStatus.Caption = strCap & vbCrLf & "Record " & CStr(i +

1) & " of " & CStr(intRowCount + 1)

Me.Repaint

If Not IsNull(strRS(0, i)) Then

strWhere = strRS(0, i)

Else

strWhere = "<NULL>"

End If

' Check the data types of the various columns
Select Case .ULinkCol.DataType

Case 0, 130, 200, 201, 202, 203 ' STRING values

strWhere = "'" & strWhere & "'"

Case 7, 133, 134, 135 ' DATE

strWhere = "#" & strWhere & "#"

Case Else

strWhere = strWhere

End Select

strWhere = "[" & .ULinkCol.FName & "]=" & strWhere

If Not IsNull(strRS(1, i)) Then

strReplace = strRS(1, i)

Else

strReplace = "''"

End If

Select Case .UpdateCol.DataType

Case 0, 130, 200, 201, 202, 203 ' STRING values

strReplace = "'" & strReplace & "'"

Case 7, 133, 134, 135 ' DATE

strReplace = "#" & strReplace & "#"

Case Else

strReplace = Val(strReplace)

End Select

strSQL = "UPDATE [" & .UpdateTable & "] SET [" &

..UpdateCol.FName & "]=" & strReplace & " WHERE " & strWhere

 

[MDW]

Comments inlin

----- david epsom dot com dot au wrote: ----

If I read this right, you are executing a seperate update for each row i
the 'table'
That is not normally neccessary. You can include the string manipulatio
inside the SQL, and run one update for the entire 'table'
ie for a date column
"UPDATE [" & .UpdateTable & "] SET [" & .UpdateCol.FName & "]= # & ["
..UpdateCol.FName & "] & #;

I'm not sure if this would work, given the layout/setup of the underlying sheets. This is probably best described using an example. Say that this is what one of my sheets (an Excel spreadsheet linked to Access) looks like
STORE_NUM OFFICE JAN_SALES FEB_SALES MAR_SALES Q1_SALES ANNUAL_GOAL QTRLY_GOA
4 Corpoarte 145 144 11 300 1600 40
7 Main St. 216 189 54 459 3000 75
15 First Ave 104 454 37 595 2000 50
27 Public Sq 100 200 101 401 1200 30

and so forth. There are about 600 of these

Say that we get revised goals for these locations. The new goal sheet might look like this
LOCATION DESCRIPTION GOAL_OLD GOAL_NE
7 Main St. 3000 250
15 First Ave 2000 260

There's no guarantee that the revised goals will be in the same order as they are on the main sheet, and it's fairly certain that any revisions we get will only affect a subset of the entire population

Within the realm of my application, this smaller sheet would be the "source" table (the source of the new values), and the big one would be the "update" table. So all I'm looking to do is update the ANNUAL_GOAL column of the bigger sheet, setting it to 2500 for STORE_NUM 7 and to 2600 to STORE_NUM 15

A week from now, we might get different goals altogether, for maybe 2, maybe 20, maybe 100 more stores. Or we could get revised monthly sales numbers. This app was intended to be a way to manage these updates in as efficient and error-free way as possible

Now, it is reasonable to assume that all the Excel sheets that would be updated would be found on the same netwrok share (albeit maybe in different folders). It's been suggested that I hold open "a connection to the server", o

"Where you are still doing seperate updates, you need to take steps to ensur
that the XLS remains open. You can probably just open a copy of the 'table
at the begining of the routine, and leave it open until you finish.

How would I go about doing either/both of those

I appreciate your help

(Yo
may have to set the spread sheet to allow shared access). There are tw
reasons for doing this: (1) The overhead of opening, saving, and closing th
file is enormous, (2) When you open a file, you AV software typically kick
in and scans it again

(david

Hey all

an Access database that allows you to link to an external source (such as a
Excel worksheet or another Access database) and perform search-and-replac
functions to another linked source (Excel, Access, etc.). This has a myria
of uses and is actually quite useful (mostly because of the elimination o
human error). But the actual processing of the updates seems to tak
forever. Here is the code in question (and the definition of a UDT). I
there some reason that this sub could take so long to run? (For instance,
had 73 records to update, and I started it before I began typing this post
and it's still not done yet.)

' Describes one field in a table
Public Type OneField
SourceTable As String
SourceCol As OneField
SLinkCol As OneField
UpdateTable As String
UpdateCol As OneField
ULinkCol As OneField
Dim strSQL As String, strReplace As String, strWhere As String, strCap As String, strPer As String
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strRS ****************************************************************************
*******
' This sub loops through all the items in the objJobs array, and performs each update
' described.
' ****************************************************************************
*******

CStr(UBound(objJobs) + 1) & ")"

strCap = lblStatus.Caption
Me.Repaint

With objJobs(J)
' Establish a connection to the current database

Set objConn = CurrentProject.Connection

strSQL = "SELECT [" & .SLinkCol.FName & "],[" &

..SourceCol.FName & "] FROM [" & .SourceTable & "]"

Set objRS = objConn.Execute(strSQL)
strRS = objRS.GetRows

1) & " of " & CStr(intRowCount + 1)

Me.Repaint

If Not IsNull(strRS(0, i)) Then
strWhere = strRS(0, i)
Else
strWhere = "<NULL>"
End If
' Check the data types of the various columns Select Case .ULinkCol.DataType
Case 0, 130, 200, 201, 202, 203 ' STRING values
strWhere = "'" & strWhere & "'"
Case 7, 133, 134, 135 ' DATE
strWhere = "#" & strWhere & "#"
Case Else
strWhere = strWhere
End Select
strWhere = "[" & .ULinkCol.FName & "]=" & strWhere
If Not IsNull(strRS(1, i)) Then
strReplace = strRS(1, i)
Else
strReplace = "''"
End If
Select Case .UpdateCol.DataType
Case 0, 130, 200, 201, 202, 203 ' STRING values
strReplace = "'" & strReplace & "'"
Case 7, 133, 134, 135 ' DATE
strReplace = "#" & strReplace & "#"
Case Else
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

 

[david epsom dot com dot au]

On the data you have shown, you could link to those two 'tables',
or create select queries that reference those two 'tables', then
write an update query that updates one from the other:


UPDATE tbl1 INNER JOIN tbl2 ON tbl1.store_num = tbl2.location
SET tbl1.Jan_Sales = tbl2.Sales;

You can use DAO or TransferSpreadsheet to create or modify
linked 'tables'. You can also specify the 'table' directly in the update
query, using the IN key word as shown in this select query:

Select * from [Named Range or Sheet Name] IN "c:\xldata.xls" "EXCEL 5.0;"

or using the [source].[tbl] syntax.


Using an update query, you can update more than one column at a time, but
assuming that to be more work than it is worth, you would keep the 'table'
open with a recordset:

set rs = db.openrecordset('tbl1")
....
'update code here
....
rs.close

(david)

Comments inline

----- david epsom dot com dot au wrote: -----

If I read this right, you are executing a seperate update for each row in
the 'table'.
That is not normally neccessary. You can include the string manipulation
inside the SQL, and run one update for the entire 'table':
ie for a date column:
"UPDATE [" & .UpdateTable & "] SET [" & .UpdateCol.FName & "]= # & [" &
..UpdateCol.FName & "] & #;"

I'm not sure if this would work, given the layout/setup of the underlying

sheets. This is probably best described using an example. Say that this is
what one of my sheets (an Excel spreadsheet linked to Access) looks like:

STORE_NUM OFFICE JAN_SALES FEB_SALES MAR_SALES

Q1_SALES ANNUAL_GOAL QTRLY_GOAL

4 Corpoarte 145 144

11 300 1600 400

7 Main St. 216 189

54 459 3000 750

15 First Ave 104 454

37 595 2000 500

27 Public Sq 100 200

101 401 1200 300

and so forth. There are about 600 of these.

Say that we get revised goals for these locations. The new goal sheet might look like this:
LOCATION DESCRIPTION GOAL_OLD GOAL_NEW
7 Main St. 3000 2500
15 First Ave 2000 2600

There's no guarantee that the revised goals will be in the same order as

they are on the main sheet, and it's fairly certain that any revisions we
get will only affect a subset of the entire population.

Within the realm of my application, this smaller sheet would be the

"source" table (the source of the new values), and the big one would be the
"update" table. So all I'm looking to do is update the ANNUAL_GOAL column of
the bigger sheet, setting it to 2500 for STORE_NUM 7 and to 2600 to
STORE_NUM 15.

A week from now, we might get different goals altogether, for maybe 2,

maybe 20, maybe 100 more stores. Or we could get revised monthly sales
numbers. This app was intended to be a way to manage these updates in as
efficient and error-free way as possible.

Now, it is reasonable to assume that all the Excel sheets that would be

updated would be found on the same netwrok share (albeit maybe in different
folders). It's been suggested that I hold open "a connection to the server",
or

"Where you are still doing seperate updates, you need to take steps to ensure
that the XLS remains open. You can probably just open a copy of the 'table'
at the begining of the routine, and leave it open until you finish."

How would I go about doing either/both of those?

I appreciate your help.

(You
may have to set the spread sheet to allow shared access). There are two
reasons for doing this: (1) The overhead of opening, saving, and closing the
file is enormous, (2) When you open a file, you AV software typically kicks
in and scans it again.

(david)

Hey all,

created
an Access database that allows you to link to an external source (such as an
Excel worksheet or another Access database) and perform search-and-replace
functions to another linked source (Excel, Access, etc.). This has a myriad
of uses and is actually quite useful (mostly because of the elimination of
human error). But the actual processing of the updates seems to take
forever. Here is the code in question (and the definition of a UDT). Is
there some reason that this sub could take so long to run? (For instance, I
had 73 records to update, and I started it before I began typing this post,
and it's still not done yet.)

' Describes one field in a table
Public Type OneField
SourceTable As String
SourceCol As OneField
SLinkCol As OneField
UpdateTable As String
UpdateCol As OneField
ULinkCol As OneField
Dim strSQL As String, strReplace As String, strWhere As String,

strCap As
String, strPer As String

Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strRS

****************************************************************************

*******
' This sub loops through all the items in the objJobs array, and

performs
each update

' described.
'

****************************************************************************

*******

of " &
CStr(UBound(objJobs) + 1) & ")"

strCap = lblStatus.Caption
Me.Repaint

With objJobs(J)
' Establish a connection to the current database

Set objConn = CurrentProject.Connection

strSQL = "SELECT [" & .SLinkCol.FName & "],[" &

..SourceCol.FName & "] FROM [" & .SourceTable & "]"

Set objRS = objConn.Execute(strSQL)
strRS = objRS.GetRows

CStr(i +
1) & " of " & CStr(intRowCount + 1)

Me.Repaint

If Not IsNull(strRS(0, i)) Then
strWhere = strRS(0, i)
Else
strWhere = "<NULL>"
End If
' Check the data types of the various columns Select Case .ULinkCol.DataType
Case 0, 130, 200, 201, 202, 203 ' STRING values
strWhere = "'" & strWhere & "'"
Case 7, 133, 134, 135 ' DATE
strWhere = "#" & strWhere & "#"
Case Else
strWhere = strWhere
End Select
strWhere = "[" & .ULinkCol.FName & "]=" & strWhere
If Not IsNull(strRS(1, i)) Then
strReplace = strRS(1, i)
Else
strReplace = "''"
End If
Select Case .UpdateCol.DataType
Case 0, 130, 200, 201, 202, 203 ' STRING values
strReplace = "'" & strReplace & "'"
Case 7, 133, 134, 135 ' DATE
strReplace = "#" & strReplace & "#"
Case Else
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