Slow Running Sub (Snipped From Yesterday)

[Guest]

Dirk, David, whomever..... Here's where we left off. I appreciate your help. Here's where I stand.

Per Dirk's suggestion, I copied some Excel sheets from the network to my local PC. There was a noticable increase in performance, but we're still ticking away at about one update every second. Still seems WAY slower than it should be.

In response to David's post - comments inline -

----- 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 records

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

Or it might look like this
STORE GOA
7 250
15 260

Unfortunately, I can't control the structure of the sheets containing the new info. That's why my sub goes to so much effort to determine the names of columns and their data types, etc. There's also 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, the 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 involved reside 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 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.
' ****************************************************************************
*******

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

 

[Guest]

Resolved - thanks everyone.