I am trying to run a program that accesses one of our
database systems and creates a query table. I keep
getting the following error message "Run-Time Error 1004:
The operation cannot be done because the data is
refreshing in the background." If anyone has any
suggestions that would be great. My code is as follows:


Sub LFQuery()
' LFQuery Macro
' Macro recorded 6/21/2004 by Randy Kreider and Dale D.
Marques
' Declaring and Setting Variables
var1 = CStr(Worksheets("Customer").Range("F2").Value)
var2 = CStr(Worksheets("Customer").Range("F4").Value)
Worksheets("Customer").Unprotect
Range("A7:L18000").ClearContents
' Import Query Table
With ActiveSheet.QueryTables().Add(Connection:= _
"ODBC;DRIVER={Microsoft ODBC for
Oracle};UID=;PWD=;SERVER=cdapd;", _
Destination:=Worksheets("Customer").Range("A7"))
.Sql = " SELECT CDM_LOCATIONS.OP_CENTER,
CDM_ESSR.METER_READ_DT, CDM_BILL_ACCOUNTS.BILL_ACCT_NUM,
CDM_BILL_ACCOUNTS.CUSTOMER_NAME,
CDM_TARIFF_SCHEDULES.TARIFF_RATE_DESIGNATION,
CDM_ESSR.REV_YR_MO, SUM(CDM_ESSR.KWH), SUM
(CDM_ESSR.MAXIMUM_DEMAND), SUM(CDM_ESSR.BILLED_DEMAND), SUM
(CDM_ESSR.COUNT_AS_BILL), SUM(CDM_ESSR.BILLING_DAYS)" & _
" FROM CDADM.CDM_LOCATIONS CDM_LOCATIONS,
CDADM.CDM_ESSR CDM_ESSR, CDADM.CDM_BILL_ACCOUNTS
CDM_BILL_ACCOUNTS, CDADM.CDM_SERVICE_SUPPLIERS
CDM_SERVICE_SUPPLIERS, CDADM.CDM_TARIFF_SCHEDULES
CDM_TARIFF_SCHEDULES " & _
" WHERE (CDM_LOCATIONS.LOCATION_GK_PK =
CDM_ESSR.LOCATION_FK) AND
(CDM_BILL_ACCOUNTS.BILL_ACCT_GK_PK =
CDM_ESSR.BILL_ACCT_FK ) AND (
CDM_TARIFF_SCHEDULES.TARIFF_SCHED_GK_PK =
CDM_ESSR.TARIFF_SCHED_FK) AND (CDM_ESSR.METER_READ_DT
BETWEEN TO_DATE('" & var1 & "','YYYYMMDD') AND TO_DATE('"
& var2 & "','YYYYMMDD')) AND
(CDM_TARIFF_SCHEDULES.TARIFF_RATE_DESIGNATION IN
(( 'LP6 ')) ) AND
(CDM_SERVICE_SUPPLIERS.SERVICE_SUPPLIER_GK_PK =
CDM_BILL_ACCOUNTS.SERVICE_SUPPLIER_FK)" & _
" GROUP BY CDM_LOCATIONS.OP_CENTER,
CDM_ESSR.METER_READ_DT, CDM_BILL_ACCOUNTS.BILL_ACCT_NUM,
CDM_BILL_ACCOUNTS.CUSTOMER_NAME,
CDM_TARIFF_SCHEDULES.TARIFF_RATE_DESIGNATION,
CDM_ESSR.REV_YR_MO"
' Query Table Formatting
.FieldNames = False
.RefreshStyle = xlOverwriteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = False
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery = False
.SavePassword = True
.SaveData = True
End With
Worksheets("Customer").Protect DrawingObjects = True,
contents = True, Scenarios = True
End Sub

Dale

On which line do you get the error? Do you get it every time you run it,
just the first time, or just any time after the first time?

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Dale Marques" wrote in message
...
I am trying to run a program that accesses one of our
database systems and creates a query table. I keep
getting the following error message "Run-Time Error 1004:
The operation cannot be done because the data is
refreshing in the background." If anyone has any
suggestions that would be great. My code is as follows:


Sub LFQuery()
' LFQuery Macro
' Macro recorded 6/21/2004 by Randy Kreider and Dale D.
Marques
' Declaring and Setting Variables
var1 = CStr(Worksheets("Customer").Range("F2").Value)
var2 = CStr(Worksheets("Customer").Range("F4").Value)
Worksheets("Customer").Unprotect
Range("A7:L18000").ClearContents
' Import Query Table
With ActiveSheet.QueryTables().Add(Connection:= _
"ODBC;DRIVER={Microsoft ODBC for
Oracle};UID=;PWD=;SERVER=cdapd;", _
Destination:=Worksheets("Customer").Range("A7"))
.Sql = " SELECT CDM_LOCATIONS.OP_CENTER,
CDM_ESSR.METER_READ_DT, CDM_BILL_ACCOUNTS.BILL_ACCT_NUM,
CDM_BILL_ACCOUNTS.CUSTOMER_NAME,
CDM_TARIFF_SCHEDULES.TARIFF_RATE_DESIGNATION,
CDM_ESSR.REV_YR_MO, SUM(CDM_ESSR.KWH), SUM
(CDM_ESSR.MAXIMUM_DEMAND), SUM(CDM_ESSR.BILLED_DEMAND), SUM
(CDM_ESSR.COUNT_AS_BILL), SUM(CDM_ESSR.BILLING_DAYS)" & _
" FROM CDADM.CDM_LOCATIONS CDM_LOCATIONS,
CDADM.CDM_ESSR CDM_ESSR, CDADM.CDM_BILL_ACCOUNTS
CDM_BILL_ACCOUNTS, CDADM.CDM_SERVICE_SUPPLIERS
CDM_SERVICE_SUPPLIERS, CDADM.CDM_TARIFF_SCHEDULES
CDM_TARIFF_SCHEDULES " & _
" WHERE (CDM_LOCATIONS.LOCATION_GK_PK =
CDM_ESSR.LOCATION_FK) AND
(CDM_BILL_ACCOUNTS.BILL_ACCT_GK_PK =
CDM_ESSR.BILL_ACCT_FK ) AND (
CDM_TARIFF_SCHEDULES.TARIFF_SCHED_GK_PK =
CDM_ESSR.TARIFF_SCHED_FK) AND (CDM_ESSR.METER_READ_DT
BETWEEN TO_DATE('" & var1 & "','YYYYMMDD') AND TO_DATE('"
& var2 & "','YYYYMMDD')) AND
(CDM_TARIFF_SCHEDULES.TARIFF_RATE_DESIGNATION IN
(( 'LP6 ')) ) AND
(CDM_SERVICE_SUPPLIERS.SERVICE_SUPPLIER_GK_PK =
CDM_BILL_ACCOUNTS.SERVICE_SUPPLIER_FK)" & _
" GROUP BY CDM_LOCATIONS.OP_CENTER,
CDM_ESSR.METER_READ_DT, CDM_BILL_ACCOUNTS.BILL_ACCT_NUM,
CDM_BILL_ACCOUNTS.CUSTOMER_NAME,
CDM_TARIFF_SCHEDULES.TARIFF_RATE_DESIGNATION,
CDM_ESSR.REV_YR_MO"
' Query Table Formatting
.FieldNames = False
.RefreshStyle = xlOverwriteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = False
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery = False
.SavePassword = True
.SaveData = True
End With
Worksheets("Customer").Protect DrawingObjects = True,
contents = True, Scenarios = True
End Sub

The error comes in the line directly following :
.refresh backgroundquery = false, which is the
.savepassword =true, line.

I tried removing that line and when I did it did the same
error one the line following. So I removed that libe and
then it ran with errors but did not bring up any data. I
have run the query in Oracle Discoverer, so the query
works fine. I dont know if there is any problems with my
top connection statement or not. Also at some points I do
get an SQL error. But that is if I delete spaces out of
the sql lines.

the query itself has .Backgroundquery to true

in my experience
the refresh with bgqry argument false will NOT override it :(
(certainly when it hasn't executed before)

so what you do:
qt.backgroundquery = false
qt.refresh
ws.protect

the sheet is protected only when control is returned to the running
procedure e.g. ADO's STATE is DONE.

what would you expect to happen when the qt autorefreshes?
OR when you try to protect a sheet while ado's state is 'fetching'

conclusion:
the backgroundrefresh CANNOT en NEVER be true on protected sheets.
unless you fiddle with the settings till kingdom come...and you're sure
that the query under ALL circumstances:
will return the same number of fields and records.
will never OVERRIDE the cells, but UPDATE the cells.
preserveformatting is true
the table's cells are UNLOCKED
etc etc.

solution:
execute the queries on UNPROTECTED (but VERYHIDDEN?) sheets
get the data from there.



you'll need code needs to refresh
ws unprotect
qt.refresh
ws.protect



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Dale Marques" wrote:

.FieldNames = False
.RefreshStyle = xlOverwriteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = False
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery = False
.SavePassword = True
.SaveData = True
End With
Worksheets("Customer").Protect DrawingObjects = True,
contents = True, Scenarios = True
End Sub