Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    7

    Exclamation can't open connection (run-time error 3709)

    hi,
    Im having a prolem with run time error 3709.VB is telling me that "the connection cannot be used to perform the operation-it is either closed or invalid in this context."
    Ive searched for this on the forum and found a reply
    You haven't assigned the connection object to the recordset's
    ActiveConnection property.
    I may not be understanding this statement correctly but I dont see how this could be in my case as Ive used the exact same code in other screens with no problems.
    heres my code which I have used successfully in several other screens.whats the story.
    Code:
    Private Sub Form_Load()
    Call CreateConnection
    Set rsTrolly = New ADODB.Recordset
    Dim strSql As String
    DisableCloseWindowButton Me
        
    strSql = "Select Distinct family From Products"
    
     With rsTrolly
        .CursorLocation = adUseClient
        .Open strSql
    
        Do Until .EOF
           If !Family <> "" Then
                 lstfamily.AddItem !Family
                 
           End If
          .MoveNext
        Loop
     End With
    
    rsTrolly.Close
    End Sub
    thanks.

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    You didn't assign a connection object or connection string to your recordset rstrolley.

    Code:
    rsTrolley.ActiveConnection = cn
    Or

    Code:
    rsTrolley.Open strSQL, cn
    Here is some sample code:

    Code:
    Dim cn as ADODB.Connection
    Dim rs as ADODB.Recordset
    
    Set cn = new ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Password=TestPass;Data Source=C:\Test.mdb;Persist Security Info=False;Exclusive=0"
    cn.open
    
    set rs = new ADODB.recordset
    rs.ActiveConnection = cn
    rs.Open "Select Distinct family From Products"
    
    rs.Close
    cn.Close
    Set rs=Nothing
    Set cn = Nothing
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Dec 2011
    Posts
    1

    Runtime Error

    Hi
    I'm having some problems with my problem
    Runtime Error 3709
    The Connection Cannot be used to perform this kind of operation.
    it is either closed or invalid in this context


    Heres the code

    Private Sub cmdadd_Click()
    Clear
    frlist.Enabled = False
    frentry.Enabled = True
    txtname.SetFocus
    cmdsave.Enabled = True
    cmdcancel.Enabled = True
    cmddel.Enabled = False
    cmdedit.Enabled = False
    cmdadd.Enabled = False
    cmdexit.Enabled = False
    lbladd_edit.Caption = "ADD"
    End Sub

    Private Sub cmdcancel_Click()
    Save_Cancel
    End Sub

    Private Sub cmddel_Click()
    If txtname.Text = "" Then
    MsgBox "Please select a record from the list first!", vbInformation, "Message"
    Exit Sub
    If MsgBox("Do you want to delete this record ?", vbQuestion + vbYesNo, "Message") = vbNo Then
    Exit Sub
    Set rs = New ADODB.Recordset
    With rs
    .Open "Select * from Records", cn, 2, 3
    Do While Not .EOF
    If txtname.Text = !Pname Then
    .Delete
    .Update

    display_list
    Clear
    Exit Do
    Else
    .MoveNext
    End If
    Loop
    End With
    End Sub
    Sub Clear()
    txtname.Text = ""
    txtaddress.Text = ""
    txtage.Text = ""
    txtcontactno.Text = ""
    txtmedhis.Text = ""
    txtchiefcom.Text = ""
    txtproddone.Text = ""
    txtamnt.Text = ""
    txtbal.Text = ""
    End Sub

    Private Sub cmdedit_Click()
    If txtname.Text = "" Then: MsgBox "Please select from the list first!", vbInformation, "Message"
    Exit Sub
    frlist.Enabled = False
    frentry.Enabled = True
    txtname.SetFocus
    cmdsave.Enabled = True
    cmdcancel.Enabled = True

    cmddel.Enabled = False
    cmdedit.Enabled = False
    cmdadd.Enabled = False
    cmdexit.Enabled = False
    lbladd_edit.Caption = "EDIT"
    End Sub

    Private Sub cmdexit_Click()
    Unload Me
    End Sub

    Private Sub cmdsave_Click()
    Set rs = New ADODB.Recordset
    With rs
    .Open "Select * from Records", cn, 2, 3

    If lbladd_edit.Caption = "ADD" Then
    If MsgBox("Do you want to save this new rocord?", vbQuestion + vbYesNo, "Message") = vbNo Then: Exit Sub
    .AddNew
    !Name = txtname.Text
    !Address = txtaddress.Text
    !Age = txtage.Text
    !ContactNo = txtcontactno.Text
    !MedicalHistory = txtmedhis.Text
    !ChiefComplaint = txtchiefcom.Text
    !ProcedureDone = txtproddone.Text
    !Amount = txtamnt.Text
    !Balance = txtbal.Text

    .Update
    Else
    If MsgBox("Do you want to save this changes?", vbQuestion + vbYesNo, "Message") = vbNo Then: Exit Sub
    Do While Not .EOF

    If lvlist.SelectedItem.Text = !Name Then
    !Pname = txtname.Text
    !Address = txtaddress.Text
    !Age = txtage.Text
    !ContactNo = txtcontactno.Text
    !MedicalHis = txtmedhis.Text
    !ChiefComp = txtchiefcom.Text
    !ProcedureDone = txtproddone.Text
    !Amount = txtamnt.Text
    !Balance = txtbal.Text
    .Update

    Exit Do
    Else
    .MoveNext
    End If
    Loop
    End If


    End With

    display_list
    Save_Cancel

    End Sub

    Sub Save_Cancel()
    Clear
    frlist.Enabled = True
    frentry.Enabled = False
    cmdsave.Enabled = False
    cmdcancel.Enabled = False

    cmddel.Enabled = True
    cmdedit.Enabled = True
    cmdadd.Enabled = True
    cmdexit.Enabled = True
    End Sub
    Private Sub Form_Activate()
    display_list
    End Sub
    Sub display_list()
    Set rs = New ADODB.Recordset
    With rs
    .Open "Select * from Records", cn, 2, 3 <<< Error is Right Here
    lvlist.ListItems.Clear
    Do While Not .EOF
    lvlist.ListItems.Add(1).Text = !Pname
    lvlist.ListItems.Item(1).ListSubItems.Add.Text = !Address
    lvlist.ListItems.Item(1).ListSubItems.Add.Text = !Age
    lvlist.ListItems.Item(1).ListSubItems.Add.Text = !ContactNo
    lvlist.ListItems.Item(1).ListSubItems.Add.Text = !MedicalHis
    lvlist.ListItems.Item(1).ListSubItems.Add.Text = !ChiefComp
    lvlist.ListItems.Item(1).ListSubItems.Add.Text = !ProcedureDone
    lvlist.ListItems.Item(1).ListSubItems.Add.Text = !Amount
    lvlist.ListItems.Item(1).ListSubItems.Add.Text = !Balance
    .MoveNext
    Loop

    End With
    End Sub
    Private Sub Form2_Load()
    connection
    End Sub
    Private Sub LvList_ItemClick(ByVal Item As MSComctlLib.ListItem)
    Set rs = New ADODB.Recordset
    With rs
    .Open "Select * from Records", cn, 2, 3
    Do While Not .EOF

    If !Name = lvlist.SelectedItem.Text Then

    !Pname = txtname.Text
    !Address = txtaddress.Text
    !Age = txtage.Text
    !ContactNo = txtcontactno.Text
    !MedicalHis = txtmedhis.Text
    !ChiefComp = txtchiefcom.Text
    !ProcedureDone = txtproddone.Text
    !Amount = txtamnt.Text
    !Balance = txtbal.Text

    Exit Do
    Else
    .MoveNext
    End If

    Module Codes

    Public cn As New ADODB.connection
    Public rs As New ADODB.Recordset
    Sub connection()
    Set cn = New ADODB.connection
    With cn
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=I:\Group5_Charity\Database\Information.accd b;Persist Security Info=False"
    .Open
    .CursorLocation = adUseClient
    End With

    End Sub


    Loop
    End With
    End Sub


    Hope You guys can help me
    I'm still learning this and have no idea how to fixed this
    I only copied the codes from another program
    Tnx in Advance

Posting Permissions

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