Run Time Error '1004' General ODBC Error
 

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

Run Time Error '1004' General ODBC Error
 

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" wrote:

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

Run Time Error '1004' General ODBC Error
 

Hi Joel........
Thanks for the comeback.....
Here's the code...
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"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" wrote:

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" wrote:

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

Run Time Error '1004' General ODBC Error
 

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" wrote:

Hi Joel........
Thanks for the comeback.....
Here's the code...
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"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" wrote:

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" wrote:

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

Run Time Error '1004' General ODBC Error
 

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" wrote in message
...
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" wrote:

Hi Joel........
Thanks for the comeback.....
Here's the code...
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"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" wrote:

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" wrote:

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

Run Time Error '1004' General ODBC Error
 

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

"CLR" wrote:

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" wrote in message
...
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" wrote:

Hi Joel........
Thanks for the comeback.....
Here's the code...
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"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" wrote:

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" wrote:

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

Run Time Error 1004 General ODBC error
 

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.


On Friday, November 07, 2008 9:38 AM CL wrote:

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


On Friday, November 07, 2008 10:08 AM Joe wrote:

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" wrote:


On Friday, November 07, 2008 10:36 AM CL wrote:

Hi Joel........
Thanks for the comeback.....
Here's the code...
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"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" wrote:


On Friday, November 07, 2008 11:21 AM Joe wrote:

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" wrote:


On Friday, November 07, 2008 5:03 PM CLR wrote:

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" wrote in message
...


On Saturday, November 08, 2008 8:20 AM Joe wrote:

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

"CLR" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Auto-Generate Code for LINQ to SQL Repository Pattern using T4
http://www.eggheadcafe.com/tutorials...-using-t4.aspx