Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2012
    Posts
    38

    Unanswered: Connection Error (3709) in Opening Recordset

    Windows 7, MS Access 2007

    I'm getting "Run-time error '3709': The connection cannot be used to perform this operation. It is either closed or invalid in this context.

    My scenario is that I'm using a script similar to the one below on a form, and then this slightly altered version below for another form if the logic permits it. The original script on the 1st form works, but when it runs from this form I get the error noted above. I've been reading a lot about this error but I haven't been able to solve it yet.

    Any ideas?

    This code is supposed to take any records that have the Boolean EmailSent not checked and send emails to those. Emails are created/sent through the exporthtml routine near the end of this routine.

    Code:
    Public Function parse_WorkflowNew()
        Dim rs As ADODB.Recordset, str_getSend As String
        Dim rs_Missing As ADODB.Recordset
        
        Set rs = New ADODB.Recordset
        Set rs_Missing = New ADODB.Recordset
        
        rs_Missing.Open "Select Mfg_Cd from q_AuthToRoute Where [E-Mail] is null Group by Mfg_Cd", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
        rs.Open "Select [E-Mail] from q_AuthToRoute Where [E-Mail] is not null Group by [E-Mail]", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
        
        If Not rs.EOF And Not rs.BOF Then
            If EmailSent = False Then
            rs.MoveFirst
            Do
                    Dim rs_Data As ADODB.Recordset
                    Set rs_Data = New ADODB.Recordset
                    --------rs_Data.Open "Select * From q_AuthToRoute Where [E-Mail] = '" & rs.Fields("E-Mail") & "' And EmailSent = No, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly"
                    If Not rs_Data.BOF And Not rs_Data.EOF Then
                        rs_Data.MoveFirst
                        Dim str_Table As String
                    End If
                    exporthtml rs.Fields("E-Mail"), rs_Data
                rs.MoveNext
            Loop Until rs.EOF
        End If
        End If
    
    End Function
    Debugging highlights the line with --------

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    This seems highly dubious:
    Code:
    And EmailSent = No
    I would expect
    Code:
    And EmailSent = False
    Or:
    Code:
    And EmailSent = 0
    Or:
    Code:
    And EmailSent = 'No'
    Have a nice day!

  3. #3
    Join Date
    Jun 2012
    Posts
    38
    I tried = False already but I got the same error, and I just tried your other 2 suggestions to no avail. I have an SQL update statement saying EmailSent = Yes and that works fine.

  4. #4
    Join Date
    Jun 2012
    Posts
    38
    The error implied there was a connection issue so I was primarily researching the "CurrentProject.Connection" part, even though it's successfully used in multiple other places.

  5. #5
    Join Date
    Jun 2012
    Posts
    38
    Well I had a simple quotation error in this line:

    Code:
    rs_Data.Open "Select * From q_AuthToRoute Where [E-Mail] = '" & rs.Fields("E-Mail") & "' And EmailSent = No", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    But now I'm getting an error that says "No value for one or more required parameters" on that same line. I'm confused because that doesn't seem to be the case when I research "Open Method (ADO Recordset)".

  6. #6
    Join Date
    Jun 2012
    Posts
    38
    I had an attribute that wasn't in the q_AuthToRoute!! ARRRGG

Posting Permissions

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