Results 1 to 2 of 2
  1. #1
    system243trd is offline Novice
    Windows 2K Access 2003
    Join Date
    Nov 2011
    Posts
    27

    Access 2010 Trying to execute a stored proedure via ADO

    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

  2. #2
    Stingaway is offline Efficiency Junkie
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2011
    Location
    The deep south. Keep going until you hit water basically.
    Posts
    224
    You might try creating a pass through query with "exec nameofstoredprochere" and just call the pass through query from the ADO..

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 07-31-2012, 12:25 PM
  2. Replies: 1
    Last Post: 05-15-2012, 06:04 PM
  3. Stored Procedure in MS Access 2007
    By sels1987 in forum Access
    Replies: 1
    Last Post: 05-13-2012, 12:23 PM
  4. Execute MySQL Stored Procedure with Access 2010 VBA?
    By DanielHofer in forum Programming
    Replies: 5
    Last Post: 01-23-2012, 01:08 PM
  5. Execute Excel Macro from Access failing
    By dcgrove in forum Programming
    Replies: 1
    Last Post: 12-22-2011, 02:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums