run-time error 1004

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!
 
G

Golfinray

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.
 
L

Larue

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...
 
G

Golfinray

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...
 
L

Larue

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"Run-time error '1004' 5
Run-time error 1004 from workbook attached to Outlook email 0
Run-time error 1004 3
Run time Error '1004' 3
Run-time error '1004' 5
Run - time error '1004' 1
Run time Error "1004": 1
Problems with a Run-time error of 1004.... 7

Top