By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,292 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

Error on Execute in access application

P: 6
Hi
This code is part of an application currently running in production which I must perform maintenance on. On my PC it throwing an error after the execute command. My Odbc connection is working fine and connecting to the db.
The error message says that "The connection cannot be used to perform this type of operation it is either closed or can not be used in this context"
Code must be ok so whats amiss

Dim cmdInv As ADODB.Command
Dim rstInv As ADODB.Recordset
Dim fOpen As Boolean

On Error GoTo ErrHandler

'fOpen = OpenInvCSConnection

Set cmdInv = New ADODB.Command
Set rstInv = New ADODB.Recordset

Set cmdInv.ActiveConnection = cnnSCDB
If OpenSCDBConnection Then

End If
With cmdInv
.CommandType = adCmdText
.CommandText = "Select * From tblInvoiceNos where RecID = " & 1
End With

Set rstInv = cmdInv.Execute
rstInv.MoveFirst

Select Case InvType
Case "R"
GetNextInvNo = rstInv!RentalInvNos
'Case "M"
' GetNextInvNo = rstInv!MaintInvNos
'Case "S"
' GetNextInvNo = rstInv!ServiceInvNos
End Select

rstInv.Close

ExitHere:
Exit Function

ErrHandler:
Call DispErrObj(Err, name & " GetNxtInvNos")

Thanks in anticipation!!
Nov 20 '06 #1
Share this Question
Share on Google+
7 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
I just don't think you can do this ... you've already set the resInv anyway.

Expand|Select|Wrap|Line Numbers
  1. Set rstInv = cmdInv.Execute
try this instead

Expand|Select|Wrap|Line Numbers
  1. rstInv.Open cmdInv.Execute
Nov 21 '06 #2

Expert 5K+
P: 8,434
I just don't think you can do this ...
Do you think maybe it's due to the poster running a different version of Access to whoever developed the code? I know I've been tripped up a few times by version changes in database access (from VB in my case though, not Access).
Nov 21 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Do you think maybe it's due to the poster running a different version of Access to whoever developed the code? I know I've been tripped up a few times by version changes in database access (from VB in my case though, not Access).
ADO's not my area of expertise but I think you have to open a recordset and I can't see any code doing that.

Version changes are a problem at times but usually because of library and functional changes.
Nov 21 '06 #4

100+
P: 1,646
Dim cmdInv As ADODB.Command
Dim rstInv As ADODB.Recordset
Dim fOpen As Boolean

On Error GoTo ErrHandler

'fOpen = OpenInvCSConnection

Set cmdInv = New ADODB.Command
Set rstInv = New ADODB.Recordset

Set cmdInv.ActiveConnection = cnnSCDB
If OpenSCDBConnection Then

End If
With cmdInv
.CommandType = adCmdText
.CommandText = "Select * From tblInvoiceNos where RecID = " & 1
End With

Set rstInv = cmdInv.Execute
Hi.
2 things here. there is no need to initialize the recordset with New (not a major problem, just a waste of code) and there is no indication here that the connection object cnnSCDB is open. This second item is likely to be causing the error. Your code is fine, just can't see where the connection is opened.
Nov 21 '06 #5

Expert 5K+
P: 8,434
ADO's not my area of expertise but I think you have to open a recordset and I can't see any code doing that.
Version changes are a problem at times but usually because of library and functional changes.
Well, it's just that the OP did say it was known-working Production code, which then wouldn't run on his/her PC.
Nov 21 '06 #6

100+
P: 1,646
Well, it's just that the OP did say it was known-working Production code, which then wouldn't run on his/her PC.
Which would indicate that the connection object is not working
Nov 22 '06 #7

Expert 5K+
P: 8,434
Which would indicate that the connection object is not working
Sounds reasonable.

I was just making the point that if the code does work on a bunch of PCs but not on one particular one, then it seems unlikey to be an error in the code.
Nov 22 '06 #8

Post your reply

Sign in to post your reply or Sign up for a free account.