none
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

    Tuesday, December 11, 2012 9:08 PM

Answers

  • 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

    • Marked as answer by system243trd Wednesday, December 12, 2012 9:28 PM
    Tuesday, December 11, 2012 9:36 PM
  • 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

    • Marked as answer by system243trd Wednesday, December 12, 2012 9:28 PM
    Wednesday, December 12, 2012 5:13 AM
  • 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


    Wednesday, December 12, 2012 8:57 PM

All replies

  • Does "Option Explicit" make a difference?

    Chris Ward

    Tuesday, December 11, 2012 9:30 PM
  • 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

    • Marked as answer by system243trd Wednesday, December 12, 2012 9:28 PM
    Tuesday, December 11, 2012 9:36 PM
  • Is it .adp or .accdb?

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

    Wednesday, December 12, 2012 3:32 AM
  • 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

    • Marked as answer by system243trd Wednesday, December 12, 2012 9:28 PM
    Wednesday, December 12, 2012 5:13 AM
  • Hi Andy,

    It is accdb.

    Thanks

    Wednesday, December 12, 2012 8:42 PM
  • 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


    Wednesday, December 12, 2012 8:57 PM
  • Cheers guys worked a treat!!!
    Wednesday, December 12, 2012 9:28 PM