If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
run-time error 1004
Hello,
I am getting the following error message when I try to run an Excel macro that is accessing a SQL Stored Procedure via a Pass-Through query in MS Access. Run-time error '1004' [Microsoft][ODBC Microsoft Access Driver] Reserved error (|); there is no message for this error The VB script is as follows: Sub NPB_Macro() ' ' NPB_Macro Macro ' Macro recorded 6/23/2008 by susan ' ' Application.CommandBars("Forms").Visible = False Workbooks.OpenDatabase Filename:= _ "X:\Finance\FinanceDB\Negative_Pledge_Balance.dqy" , CommandText:=Array( _ "SELECT NegativePledgeBalance.CoFRID, NegativePledgeBalance.Name, NegativePledgeBalance.CapCode, NegativePledgeBalance.CapYear, NegativePledgeBalance.TransTyp, NegativePledgeBalance.TotPlgAmt, NegativePledgeBalance.TotPayAmt, Negat" _ , _ "ivePledgeBalance.StaffFRID, NegativePledgeBalance.StaffName FROM `X:\Finance\FinanceDB\FinanceDB`.NegativePledgeBal ance NegativePledgeBalance ORDER BY NegativePledgeBalance.CapCode" _ ), CommandType:=xlCmdSql Columns("F:G").Select Selection.Style = "Currency" Range("A1").Select End Sub I am able to run this on some computers and not on others. Any help will be greatly appreciated. Thank you! |
#2
|
|||
|
|||
run-time error 1004
I am no sql expert, but I would see what my ODBC timeout time is. I think the
default is 60 seconds. If something takes longer than that to load, it timesout. Your query may be taking too long to run and get hit with the time limitation. "Larue" wrote: Hello, I am getting the following error message when I try to run an Excel macro that is accessing a SQL Stored Procedure via a Pass-Through query in MS Access. Run-time error '1004' [Microsoft][ODBC Microsoft Access Driver] Reserved error (|); there is no message for this error The VB script is as follows: Sub NPB_Macro() ' ' NPB_Macro Macro ' Macro recorded 6/23/2008 by susan ' ' Application.CommandBars("Forms").Visible = False Workbooks.OpenDatabase Filename:= _ "X:\Finance\FinanceDB\Negative_Pledge_Balance.dqy" , CommandText:=Array( _ "SELECT NegativePledgeBalance.CoFRID, NegativePledgeBalance.Name, NegativePledgeBalance.CapCode, NegativePledgeBalance.CapYear, NegativePledgeBalance.TransTyp, NegativePledgeBalance.TotPlgAmt, NegativePledgeBalance.TotPayAmt, Negat" _ , _ "ivePledgeBalance.StaffFRID, NegativePledgeBalance.StaffName FROM `X:\Finance\FinanceDB\FinanceDB`.NegativePledgeBal ance NegativePledgeBalance ORDER BY NegativePledgeBalance.CapCode" _ ), CommandType:=xlCmdSql Columns("F:G").Select Selection.Style = "Currency" Range("A1").Select End Sub I am able to run this on some computers and not on others. Any help will be greatly appreciated. Thank you! |
#3
|
|||
|
|||
run-time error 1004
Do you know where I change the ODBC timeout time? I have removed the flag
check box from the "Cancel the connection if not connected within" in the MS Query options. Is there another place that I change the timeout time? "Golfinray" wrote: I am no sql expert, but I would see what my ODBC timeout time is. I think the default is 60 seconds. If something takes longer than that to load, it timesout. Your query may be taking too long to run and get hit with the time limitation. "Larue" wrote: Hello, I am getting the following error message when I try to run an Excel macro that is accessing a SQL Stored Procedure via a Pass-Through query in MS Access. Run-time error '1004' [Microsoft][ODBC Microsoft Access Driver] Reserved error (|); there is no message for this error The VB script is as follows: Sub NPB_Macro() ' ' NPB_Macro Macro ' Macro recorded 6/23/2008 by susan ' ' Application.CommandBars("Forms").Visible = False Workbooks.OpenDatabase Filename:= _ "X:\Finance\FinanceDB\Negative_Pledge_Balance.dqy" , CommandText:=Array( _ "SELECT NegativePledgeBalance.CoFRID, NegativePledgeBalance.Name, NegativePledgeBalance.CapCode, NegativePledgeBalance.CapYear, NegativePledgeBalance.TransTyp, NegativePledgeBalance.TotPlgAmt, NegativePledgeBalance.TotPayAmt, Negat" _ , _ "ivePledgeBalance.StaffFRID, NegativePledgeBalance.StaffName FROM `X:\Finance\FinanceDB\FinanceDB`.NegativePledgeBal ance NegativePledgeBalance ORDER BY NegativePledgeBalance.CapCode" _ ), CommandType:=xlCmdSql Columns("F:G").Select Selection.Style = "Currency" Range("A1").Select End Sub I am able to run this on some computers and not on others. Any help will be greatly appreciated. Thank you! |
#4
|
|||
|
|||
run-time error 1004
I do not. I know I have had that error before several times. There are some
good resources in the help file for ODBC connections. I think you would have to set that up in Windows for each machine, though, for everyone to have the same timeout. Sorry I couldn't help more. "Larue" wrote: Do you know where I change the ODBC timeout time? I have removed the flag check box from the "Cancel the connection if not connected within" in the MS Query options. Is there another place that I change the timeout time? "Golfinray" wrote: I am no sql expert, but I would see what my ODBC timeout time is. I think the default is 60 seconds. If something takes longer than that to load, it timesout. Your query may be taking too long to run and get hit with the time limitation. "Larue" wrote: Hello, I am getting the following error message when I try to run an Excel macro that is accessing a SQL Stored Procedure via a Pass-Through query in MS Access. Run-time error '1004' [Microsoft][ODBC Microsoft Access Driver] Reserved error (|); there is no message for this error The VB script is as follows: Sub NPB_Macro() ' ' NPB_Macro Macro ' Macro recorded 6/23/2008 by susan ' ' Application.CommandBars("Forms").Visible = False Workbooks.OpenDatabase Filename:= _ "X:\Finance\FinanceDB\Negative_Pledge_Balance.dqy" , CommandText:=Array( _ "SELECT NegativePledgeBalance.CoFRID, NegativePledgeBalance.Name, NegativePledgeBalance.CapCode, NegativePledgeBalance.CapYear, NegativePledgeBalance.TransTyp, NegativePledgeBalance.TotPlgAmt, NegativePledgeBalance.TotPayAmt, Negat" _ , _ "ivePledgeBalance.StaffFRID, NegativePledgeBalance.StaffName FROM `X:\Finance\FinanceDB\FinanceDB`.NegativePledgeBal ance NegativePledgeBalance ORDER BY NegativePledgeBalance.CapCode" _ ), CommandType:=xlCmdSql Columns("F:G").Select Selection.Style = "Currency" Range("A1").Select End Sub I am able to run this on some computers and not on others. Any help will be greatly appreciated. Thank you! |
#5
|
|||
|
|||
run-time error 1004
Installing SP3 solved the issue.
"Golfinray" wrote: I do not. I know I have had that error before several times. There are some good resources in the help file for ODBC connections. I think you would have to set that up in Windows for each machine, though, for everyone to have the same timeout. Sorry I couldn't help more. "Larue" wrote: Do you know where I change the ODBC timeout time? I have removed the flag check box from the "Cancel the connection if not connected within" in the MS Query options. Is there another place that I change the timeout time? "Golfinray" wrote: I am no sql expert, but I would see what my ODBC timeout time is. I think the default is 60 seconds. If something takes longer than that to load, it timesout. Your query may be taking too long to run and get hit with the time limitation. "Larue" wrote: Hello, I am getting the following error message when I try to run an Excel macro that is accessing a SQL Stored Procedure via a Pass-Through query in MS Access. Run-time error '1004' [Microsoft][ODBC Microsoft Access Driver] Reserved error (|); there is no message for this error The VB script is as follows: Sub NPB_Macro() ' ' NPB_Macro Macro ' Macro recorded 6/23/2008 by susan ' ' Application.CommandBars("Forms").Visible = False Workbooks.OpenDatabase Filename:= _ "X:\Finance\FinanceDB\Negative_Pledge_Balance.dqy" , CommandText:=Array( _ "SELECT NegativePledgeBalance.CoFRID, NegativePledgeBalance.Name, NegativePledgeBalance.CapCode, NegativePledgeBalance.CapYear, NegativePledgeBalance.TransTyp, NegativePledgeBalance.TotPlgAmt, NegativePledgeBalance.TotPayAmt, Negat" _ , _ "ivePledgeBalance.StaffFRID, NegativePledgeBalance.StaffName FROM `X:\Finance\FinanceDB\FinanceDB`.NegativePledgeBal ance NegativePledgeBalance ORDER BY NegativePledgeBalance.CapCode" _ ), CommandType:=xlCmdSql Columns("F:G").Select Selection.Style = "Currency" Range("A1").Select End Sub I am able to run this on some computers and not on others. Any help will be greatly appreciated. Thank you! |
Thread Tools | |
Display Modes | |
|
|