M
MDW
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
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.)
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
an Access database that allows you to link to an external source (such as anMDW said:
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.)
CStr(UBound(objJobs) + 1) & ")"
..SourceCol.FName & "] FROM [" & .SourceTable & "]"
1) & " of " & CStr(intRowCount + 1)