Help... Error run "3705"; Operation not allowed, object is open??

Posted on 2003-02-20
Medium Priority
Last Modified: 2008-01-09
i am programming a system for a clinic, with a dbase in Access.. the problem is that; i always get this error "operation is not allowed object is open"...; and when i press debug, it always go to the module; where i coonect access and vb..

can anyone take a look/check, to what i did wrong?? and what can i do to solve this error..

i would appreciate all the help/ suggestions.. thank u very much..

this is my code for LOGinform:

Option Explicit
Private rsSearch As New ADODB.Recordset


Private Sub cmdCancel_Click()
  MsgBox "Unable to Log In", vbOKOnly, "GoodBye"
  Unload Me
End Sub


Private Sub cmdEnter_Click()

Dim intSwitch As Integer
Dim i As Integer

rsSearch.Open "SELECT * FROM Login", CN, adOpenStatic, adLockPessimistic

    ' checks the fields of Username Input
    If txtUserName.Text = "" Then
        MsgBox "Incomplete Information", vbExclamation + vbOKOnly
        If Not rsSearch.EOF Then                                        ' searches TAlbe login until end of file
            For i = 1 To rsSearch.RecordCount                           ' loop searches for match Username and for moving the pointer
                If rsSearch("UserName").Value = txtUserName.Text Then
                      If rsSearch!Password = txtPassword.Text Then      ' checks if the inputed passwrod is correct corresponding with the username
                        Call Switch2
                        MsgBox "Invalid Password", vbCritical, vbOKOnly
                      End If
                intSwitch = 1                                           ' a flag varible; 1 = true; 0 = false
                End If
            Next i
        End If

        If intSwitch = 0 Then                                           ' didnt found a matched user
             MsgBox "User dosen't Exist!", vbCritical + vbOKOnly
             txtUserName.Text = ""
             txtPassword.Text = ""
        End If
    End If
 Set rsSearch = Nothing

' d code; when added = error 3705

rsSearch.Open "SELECT * FROM LoginTime", CN, adOpenStatic, adLockPessimistic
    rsSearch!UserName = txtUserName.Text
  rsSearch!DateLogin = Format(Date, "mm-dd-yyyy")
 rsSearch!TimeLogin = Time
Set rsSearch = Nothing

End Sub

Private Sub Form_Load()

'calls for modele (connection.bas)= conects access with vb..
End Sub


' function for calling other forms
'+ =============================

Public Sub Switch2()

    MsgBox "Welcome!", vbOKOnly, "Welcome"
        Unload Me
    End Sub


'codes in connection.bas

Option Explicit

Public CN As New ADODB.Connection
Public cnProvider As String

Public Function LoadConnection()

    cnProvider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\CMS\dbase\medicalrecords.mdb;Persist Security Info=False"
    CN.Open cnProvider
End Function

Question by:mauve928
LVL 53

Expert Comment

ID: 7987557
Try this:
Set rsSearch = Nothing

set rsseach = new recordset
rsSearch.Open "SELECT * FROM LoginTime", CN, adOpenStatic, adLockPessimistic
LVL 44

Accepted Solution

Arthur_Wood earned 75 total points
ID: 7987759
if the highlighter line, when you get to DEBUG mode is:

CN.Open cnProvider

then the connection is already open and cannot be opened AGAIN.

there are two possible corrections, if that is the source of the error.


Public Function LoadConnection()

   cnProvider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\CMS\dbase\medicalrecords.mdb;Persist Security Info=False"
   if CN.State = 0  then ' Closed
      CN.Open cnProvider
   end if
End Function



Public Function LoadConnection()

   cnProvider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\CMS\dbase\medicalrecords.mdb;Persist Security Info=False"
   Set CN = New ADODB.Connection  ' create a New Connection object
   CN.Open cnProvider
End Function

My choice would be Option 1) (if that procedure is in fact the source of the error message)


Author Comment

ID: 7999057
Thank you, everyone.. for the help and suggestions.. :)

i already got rid of that error.. thank you, Arthur wood..

anyway, thank to the both of u..

till next time

mauve :)


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question