run-time error 1004

  • Thread starter Thread starter Larue
  • Start date Start date
L

Larue

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`.NegativePledgeBalance 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!
 
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.
 
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 said:
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 said:
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`.NegativePledgeBalance 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!
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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`.NegativePledgeBalance 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!
Click to expand...
Click to expand...
Click to expand...
 
Installing SP3 solved the issue.

Golfinray said:
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 said:
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 said:
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.

:

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`.NegativePledgeBalance 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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top