Results 1 to 3 of 3

Run-time error 3709: The connection cannot be used to perform this operation.

This is a discussion on Run-time error 3709: The connection cannot be used to perform this operation. within the Microsoft Access forums, part of the Question Forums category; "...it is either closed or invalid in this context." I'm trying to run a make-table query through an ADO recordset. ...

  1. #1
    Board Regular
    Join Date
    Aug 2004
    Posts
    759

    Default Run-time error 3709: The connection cannot be used to perform this operation.

    "...it is either closed or invalid in this context."

    I'm trying to run a make-table query through an ADO recordset. Maybe that's the wrong way to do it... I guess that creating a recordset and making a table are two different things. I was trying to do one, then the other. But I'm getting the error mentioned above. Not sure why.

    Code:
    Sub CreateBlahTable()
    
    Dim strsQL As String
    
    Dim cnConnection As ADODB.Connection
    Set cnConnection = New ADODB.Connection
    
    Dim strConnection As String
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Data Source=" & "C:\Documents and Settings\My Name Here\My Documents" & "\Database12.accdb;"
    cnConnection.Open strConnection
    
    Dim cmdCommand As ADODB.Command
    Set cmdCommand = New ADODB.Command
    
    Dim rsRecordset As ADODB.Recordset
    Set rsRecordset = New ADODB.Recordset
    
    
    strsQL = "SELECT Sum(Analyzer.[Revenue Under Goal (Lifetime)]) AS [SumOfRevenue Under Goal (Lifetime)], Analyzer.[End Date], Analyzer.[Order Line], Analyzer.[Order ID], Analyzer.[Confidence Pct], Analyzer.Priority INTO [BLAH SITE REVENUE AT RISK JULY 29 2012]"
    strsQL = strsQL + "FROM Analyzer"
    strsQL = strsQL + "GROUP BY Analyzer.[Internet Site], Analyzer.[End Date], Analyzer.[Order Line], Analyzer.[Order ID], Analyzer.[Confidence Pct], Analyzer.Priority"
    strsQL = strsQL + "HAVING (((Analyzer.[Internet Site])='BLAH SITE') AND ((Sum(Analyzer.[Revenue Under Goal (Lifetime)]))>1000) AND"
    strsQL = strsQL + "((Analyzer.[End Date])>(Date()+7)) AND ((Analyzer.[Confidence Pct])='IO'))"
    strsQL = strsQL + "ORDER BY Sum(Analyzer.[Revenue Under Goal (Lifetime)]) DESC;"
    
    rsRecordset.Open strsQL
    
    End Sub

  2. #2
    Board Regular
    Join Date
    Mar 2011
    Location
    The Hague, Netherlands
    Posts
    409

    Default Re: Run-time error 3709: The connection cannot be used to perform this operation.

    Hi,

    you should replace

    Code:
    rsRecordset.Open strsQL
    with

    Code:
    cnConnection.Execute strsQL
    Problems are solutions in progress

  3. #3
    Board Regular
    Join Date
    Aug 2004
    Posts
    759

    Default Re: Run-time error 3709: The connection cannot be used to perform this operation.

    Thanks.

    Upon further reflection, I really don't need a recordset for this at all; I can just use DoCmd.RunSQL. I was working from a book that has exercises focused on forms, but I'm not using forms.

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com