D
dnickelson
This question has three parts, all related to an SQL query being run
every time a cell in any workbook is updated .
First of all, the only choice I seem to have is to turn auto-calculate
off and manually calculate in any open workbook whenever this workbook
is open. This is not workable as I have several calculations that
'need' to take place on their own, so I either have to close this
workbook, or deal with the query running on every update, (not good for
the database on the larger queries)
Second, I cannot get the query to run on it's own. I've followed
instructions I've found on setting up the database, which include
entering a username and password when setting up a System DSN in the
control panel, but every time the query runs, it brings up a box to
confirm the user\pass. Hitting okay works, and the query runs, but from
my earlier problem, you may see where this is annoying.
Third, when I run a macro on this workbook, this prompt, and apparently
the query, runs each time the macro makes a change to a group of cells.
I have had the macro turn off the auto calculate feature before
running, and turning it back on afterwards, with the strange result
that the 'updating' waits until the macro is finished, but still
updates the same number of times as it would have if it had been active
during the macro (if the macro makes 3 updates, I am prompted to
confirm user\pass 3 times when the macro is finished). Neglecting to
confirm on any of the requests results in the update failing.
Anyone know how this SQL thing is supposed to work in the updating
area???
-Sorry for the long winded question. Please let me know if this should
be broken up into 3 submission. Also, I can provide code if a general
answer is not available.
every time a cell in any workbook is updated .
First of all, the only choice I seem to have is to turn auto-calculate
off and manually calculate in any open workbook whenever this workbook
is open. This is not workable as I have several calculations that
'need' to take place on their own, so I either have to close this
workbook, or deal with the query running on every update, (not good for
the database on the larger queries)
Second, I cannot get the query to run on it's own. I've followed
instructions I've found on setting up the database, which include
entering a username and password when setting up a System DSN in the
control panel, but every time the query runs, it brings up a box to
confirm the user\pass. Hitting okay works, and the query runs, but from
my earlier problem, you may see where this is annoying.
Third, when I run a macro on this workbook, this prompt, and apparently
the query, runs each time the macro makes a change to a group of cells.
I have had the macro turn off the auto calculate feature before
running, and turning it back on afterwards, with the strange result
that the 'updating' waits until the macro is finished, but still
updates the same number of times as it would have if it had been active
during the macro (if the macro makes 3 updates, I am prompted to
confirm user\pass 3 times when the macro is finished). Neglecting to
confirm on any of the requests results in the update failing.
Anyone know how this SQL thing is supposed to work in the updating
area???
-Sorry for the long winded question. Please let me know if this should
be broken up into 3 submission. Also, I can provide code if a general
answer is not available.