Run Time Error '1004' General ODBC Error

[CLR]

Hi All.........
I have a little macro that queries an ACCESS file and returns desired
values. The program works fine on my computer, but when my user tries to run
it on his computer, (same versoion of Excel, 2000), he gets the following
error.

Run Time Error '1004' General ODBC Error

Obviously some difference between the two machines, but who knows what to do
to fix it?

TIA
Vaya con Dios,
Chuck, CABGx3

 

[Joel]

It would help to know which line of code is failing. One thing to check is
the error option in BA. go to VBA menu Tools - Options - General - Error
Trapping.

 

[CLR]

Hi Joel........
Thanks for the comeback.....
Here's the code...
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=" & NewFile & ";DefaultDir= " _
), Array( _
";DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT `NPR Database`.`Disposition Date`, `NPR
Database`.`Inspection Date`, `NPR Database`.`NPR Origin`, `NPR Database`.`NPR
Number`, `NPR Database`.`Part Number`, `NPR Database`.`Serial Number`, `NPR" _
, _
" Database`.`Vendor Code`, `NPR Database`.`Vendor Name`, `NPR
Database`.`No of Defects`, `NPR Database`.`Qty RTV`, `NPR Database`.`Defect
Description`, `NPR Database`.`Corrective Action`" & Chr(13) & "" & Chr(10) &
"FROM `" _
, _
"" & newfile2 & "`.`NPR Database` `NPR Database`" & Chr(13) & "" &
Chr(10) & "ORDER BY `NPR Database`.`Vendor Code`" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

When failing, the bottom line, ".Refresh BackgroundQuery:=False" is the one
that's highlighted.....but then that's the case with almost any error within
this routine.

My "Error Trapping" setting is "Break on unhandled errors".....if my User's
computer has a different setting could that be the reason? (He's tied up
right now so I can't check his setting).

Like I said, the code works fine on my computer, just not on his.

Vaya con Dios,
Chuck, CABGx3

 

[Joel]

You will never get an error until you get to the Refresh line. I would
remove some unecessary line in the command until you get it to work. First
try on the working computer to eliminate as many unecessary items as possible
and check to make sure it still works. then put these changes in the
non-working PC.

Here are things that I think are un-needed

1) DefaultDir=
2) DriverId=281

Also make sure all your variables are references the correct path names like
newfile2.

 

[CLR]

Hi Joel.........
Well, you saved the day, or week, or several weeks that I've been working on
that problem........just as you suggested, I removed everything from the
macro that I could and have it still function on my good machine and then
took it to my User and it ran perfectly on his machine......SUCCESS!!!

Many, many thanks, my friend,
Vaya con Dios,
Chuck, CABGx3

 

[Joel]

Recorded macro aren't very robust. I always edit recorded macros eliminating
the unecessary items and replace SELECTION with absolute cell references.

 

[Fay Chen]

Hi Joe,

My boss saved a macro in our server and he can run the macro in his computer until 8/23.

I can never run the macro from my computer.

But now, my boss is not able to run the Macro either. We havn't updated or installed or changed our computer setting or anything. Not sure why.

This is the error we have got:

Run-Time Error 1004, General ODBC error.
These are the codes highlighted in color when I debug the macro back on July. But now, I can't even debug.

Selection.QueryTable.Refresh BackgroundQuery:=False

Does this tell you something?

I have very little knowledge about program and languages.

Thank you for your help.