QueryTables Error
 

I have the following code in a macro. This has always worked and comtinues
to work with Excel 2002, but since I upgraded to Excel 2003 the
BackgroundQuery Line causes an error.

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=dBASE
Files;DefaultDir=C:\;DriverId=533;MaxBufferSize=20 48;PageTimeout=" _
), Array("5;")), Destination:=Range("B27"))
.CommandText = Array( _
"SELECT JOB_NO.CUSTCODE, JOB_NO.PROJ_DESCR, JOB_NO.PO_NUMBER,
JOB_NO.DATE, JOB_NO.PROJMAN, JOB_NO.CUSTFORCD" & Chr(13) & "" & Chr(10) &
"FROM `K:\DATA\DBDATA\JOBDATA`\JOB_NO.DBF JOB_NO WHERE(JOB_NO.JOB_NO=" &
Worksheets("Creation").Range("B3").Value & ")" _
)
.Name = "Query from dBASE Files"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery = False <<--this line gets an
error
End With

___Microsoft Visual Basic ___
The error is Run-time error '1004':
Application-defined or object-defined error

What could be causing this, I did do a full install of Office 2003.

Thanks for any help or suggestions.

Sharlene England
Network Administrator
Westwood Companies
Vernon, BC
Canada.

QueryTables Error
 

The actual line is as below (just so you don't tell me that it is syntax)
The colon is in the line the gets the error.


.Refresh BackgroundQuery := False



Please help.

QueryTables Error
 

Hi Sharlene ;

I'll take a stab at it. I would check and see if the
DriverId 533 is still valid with the new version. In my
experience, that type of error usuall shows up when the
object did not ever get instanciated in the first place.
If the driver number is not valid then that would cause it
I think.

Thanks,

Greg
-----Original Message-----
I have the following code in a macro. This has always
worked and comtinues
to work with Excel 2002, but since I upgraded to Excel
2003 the
BackgroundQuery Line causes an error.

With ActiveSheet.QueryTables.Add(Connection:=Array
(Array( _
"ODBC;DSN=dBASE
Files;DefaultDir=C:\;DriverId=533;MaxBufferSize=2 048;PageT
imeout=" _
), Array("5;")), Destination:=Range("B27"))
.CommandText = Array( _
"SELECT JOB_NO.CUSTCODE, JOB_NO.PROJ_DESCR,
JOB_NO.PO_NUMBER,
JOB_NO.DATE, JOB_NO.PROJMAN, JOB_NO.CUSTFORCD" & Chr(13)
& "" & Chr(10) &
"FROM `K:\DATA\DBDATA\JOBDATA`\JOB_NO.DBF JOB_NO WHERE
(JOB_NO.JOB_NO=" &
Worksheets("Creation").Range("B3").Value & ")" _
)
.Name = "Query from dBASE Files"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery = False <<--
this line gets an
error
End With

___Microsoft Visual Basic ___
The error is Run-time error '1004':
Application-defined or object-defined error

What could be causing this, I did do a full install of
Office 2003.

Thanks for any help or suggestions.

Sharlene England
Network Administrator
Westwood Companies
Vernon, BC
Canada.



.

QueryTables Error
 

It might be worth it to start working with ADO.
Plenty of examples of this available and probably easier and more powerful.

RBS

"Sharlene England" wrote in message
...
I have the following code in a macro. This has always worked and
comtinues
to work with Excel 2002, but since I upgraded to Excel 2003 the
BackgroundQuery Line causes an error.

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=dBASE
Files;DefaultDir=C:\;DriverId=533;MaxBufferSize=20 48;PageTimeout=" _
), Array("5;")), Destination:=Range("B27"))
.CommandText = Array( _
"SELECT JOB_NO.CUSTCODE, JOB_NO.PROJ_DESCR, JOB_NO.PO_NUMBER,
JOB_NO.DATE, JOB_NO.PROJMAN, JOB_NO.CUSTFORCD" & Chr(13) & "" & Chr(10) &
"FROM `K:\DATA\DBDATA\JOBDATA`\JOB_NO.DBF JOB_NO WHERE(JOB_NO.JOB_NO=" &
Worksheets("Creation").Range("B3").Value & ")" _
)
.Name = "Query from dBASE Files"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery = False <<--this line gets an
error
End With

___Microsoft Visual Basic ___
The error is Run-time error '1004':
Application-defined or object-defined error

What could be causing this, I did do a full install of Office 2003.

Thanks for any help or suggestions.

Sharlene England
Network Administrator
Westwood Companies
Vernon, BC
Canada.