Access 2010 Trying to execute a stored proedure via ADO

Question

Hi,

I am using Access 2010 and have the following code on my form load which should execute a stored procedure, unfortunately I am receiving an error message on the cmd.Execute line 'Run time error 3709 The connection cannot be used to perform this operation.  It is either closed or invalid in the context. '

I have tested the code without the command and the connection does work.

Option Compare Database

Private Sub Form_Load()
Dim cnn As ADODB.Connection
Dim strcnn As String
Dim cmd As New ADODB.Command
Set cnn = New ADODB.Connection
strcnn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=StepSample;Data Source=CONNECTION\NAMEOFCONNECTION"
cnn.ConnectionString = strcnn
cnn.Open CurrentProject.Connection
    

    cmd.CommandText = "[myprocedurename]"
       cmd.CommandType = adCmdStoredProc
    cmd.Execute
    

cnn.Close
Set cnn = Nothing
End Sub

Answer 1

Does "Option Explicit" make a difference?

---

Chris Ward

Answer 2

here is an ADO sample you could try out -- this one also includes a parmater.  If you don't have params -- just comment out that line.

Dim cmd As New ADODB.Command

cmd.ActiveConnection = "Provider=SQLOLEDB; Data Source=yourSvr;Database=yourDB;Trusted_Connection=Yes"

cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdStoredProc
cmd.commandText = "stp_yourProc"
cmd.parameters("@userID").value = GetUserId
cmd.Execute
cmd.ActiveConnection.Close

---

Rich P

Answer 3

Is it .adp or .accdb?

---

Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru

Answer 4

I don't see any line setting the Command object's ActiveConnection property, so cmd has no open connection to operate on.  I would expect to see:

    Set cmd.ActiveConnection = cnn

I also don't understand why you are both setting cnn's ConnectionString property *and* assigning it the connection string of CurrentProject.Connection at open time.  If strcnn is the connection string you want to use, I suggest you change this line:

    cnn.Open CurrentProject.Connection

to just this:

    cnn.Open

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 5

Hi Andy,

It is accdb.

Thanks

Answer 6

Hi,

that's why the issue exists. CurrentProject.Connection returns the connection string to your .accdb, not to your SQL Server. Just follow Dirk's suggestion and all should be fine.

---

Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru

Answer 7

Cheers guys worked a treat!!!