QueryTables Error

[Sharlene England]

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=2048;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.

 

[Sharlene England]

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.

 

[GJones]

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

 

[RB Smissaert]

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

RBS