Error 3709

[warway]

I get VB error 3709 - The connection cannot be used to perform this
operation. It is either closed or invalid in this context. Stopping at the
Set rs = CMD.Execute line when running the code below:

All I want to happen is for the ShipmentID field in the sp_ShipmentCurrent
recordset to be stored as exisitngShipmentID for future use. I have adapted
the SQL BOL sample but cannot get it to work. Thanks in advance for your
help!

Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim Cmd As New ADODB.Command

Set cn = CurrentProject.Connection

Cmd.CommandText = "sp_ShipmentCurrent"
Cmd.CommandType = adCmdStoredProc

Set rs = Cmd.Execute

With rs
existingShipmentID = !ShipmentID
..Update
rs.Close
End With


Warway

 

[Lyle Fairfield]

I get VB error 3709 - The connection cannot be used to perform this
operation. It is either closed or invalid in this context. Stopping at
the Set rs = CMD.Execute line when running the code below:

All I want to happen is for the ShipmentID field in the
sp_ShipmentCurrent recordset to be stored as exisitngShipmentID for
future use. I have adapted the SQL BOL sample but cannot get it to work.
Thanks in advance for your help!

Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim Cmd As New ADODB.Command

Set cn = CurrentProject.Connection

Cmd.CommandText = "sp_ShipmentCurrent"
Cmd.CommandType = adCmdStoredProc

Set rs = Cmd.Execute

With rs
existingShipmentID = !ShipmentID
.Update
rs.Close
End With

Where did you eatablish the command object's active connection?

BTW:

Some posts to Comp Databases MS-Access have discussed problems releasing
references set with "Dim Variable as New Anything" and have pointed out
that "Dim Variable as Anything", "Set Variable to New Anything" may be
remove the potential for these problems occurring.

 

[warway]

I don't beleive I have set this which is why I'm confused. The BOL examples
use a lot more lines of code to get to the same as my Set cn =
CurrentProject.Connection but doesn't mention anything about Cmd.Execute
apart from the Dim and Set statements.

Warway

 

[Lyle Fairfield]

I don't beleive I have set this which is why I'm confused. The BOL
examples use a lot more lines of code to get to the same as my Set cn =
CurrentProject.Connection but doesn't mention anything about Cmd.Execute
apart from the Dim and Set statements.

Air Code

Sub temp()
Dim cmd As ADODB.Command
Dim rcs As ADODB.Recordset

Set cmd = New ADODB.Command
With cmd
.CommandType = adCmdStoredProc
.CommandText = "spGetFFDBAAccounts"
.ActiveConnection = CurrentProject.Connection
Set rcs = .Execute()
End With

'do your stuff with record set


End Sub

Sub tempIfNotParameters()
Dim rcs As ADODB.Recordset
Set rcs = CurrentProject.Connection.Execute("SpGetFFDBAAccounts")

End Sub

 

[warway]

This seesm to work up the .Execute()

My sp contains a parameter

cn.Execute ("Execute sp_ShipmentCreate " + CStr(Me.QuoteID))

how to I specify this? I have tried it inside the "" and parenthesis but
without any luck any ideas?

Warway

 

[warway]

Sorted

I put the parameter inside the Execute(,Cstr(Me.Quote))

thanks for all your help

Warway

This seesm to work up the .Execute()

My sp contains a parameter

cn.Execute ("Execute sp_ShipmentCreate " + CStr(Me.QuoteID))

how to I specify this? I have tried it inside the "" and parenthesis but
without any luck any ideas?

Warway