Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
Run-time error 1004 - General ODBC Error | Excel Programming | |||
1004 General ODBC Error | Excel Programming | |||
Run time error 1004, General ODBC error | New Users to Excel | |||
Runtime error '1004' General ODBC error | New Users to Excel |