Vba ошибка 3705 - Не ошибается лишь тот, кто ничего не делает!

'Redundancy checking function
Private Function Redundancy_Check(Sup_ID)
rs.Open "Select*from tblSupplier where Supplier_ID='" & Sup_ID & "'", cn, 3, 3
If rs.RecordCount > 0 Then
    If Not (rs.BOF And rs.EOF) Then
        iTerminate = True
    End If
End If
Set rs = Nothing
End Function

Is there something wrong with my coding? I’ve checked the log and it states:

«Cannot load control usrGrid.»

I’m using Visual Basic 6.0 + MS Access.

Sergey Weiss

5,9048 gold badges30 silver badges40 bronze badges

asked Apr 1, 2016 at 10:42

The run-time error 3705 occurs because only client-side ADO recordsets rs can be disconnected. It occurs when you attempt to disconnect a server-side ADO recordset. You need to set the CursorLocation property of the ADO Recordset to adUseClient. Assume that your ADODB.Connection variable is called cn, you need to add the following line before open the connection :

Set cn = New ADODB.Connection
cn.CursorLocation = adUseClient ' avoid error 3705
cn.Open "..."

Here’s the complete example provided by Microsoft

Private Sub Command1_Click()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

'Place cn.CursorLocation = adUseClient here
cn.Open "Provider=SQLOLEDB;Data Source=<SQL Server>;Initial Catalog=pubs;User Id=<UID>;Password=<PWD>" 
rs.Open "Select * from authors", cn, adOpenStatic, adLockBatchOptimistic

Set rs.ActiveConnection = Nothing

rs.Close
cn.Close

End Sub

See

PRB: Run-Time Error Message 3705
BOF, EOF, and Bookmark Properties Example (VB)

answered Apr 1, 2016 at 11:45

Mincong HuangMincong Huang

5,2448 gold badges38 silver badges61 bronze badges

Another potential cause is that rs already has a recordset open. Because your code doesn’t Dim and initialize rs inside of your method I’m assuming it’s created as a module variable and it doesn’t look like it’s being closed.

Somewhere you need to call rs.Close before you can call rs.Open again. Add this before your rs.Open line and run your code.

If Not rs.State = adStateClosed Then
    MsgBox "The recordset is already open"
End If

answered Apr 1, 2016 at 13:21

MarcMarc

9734 silver badges13 bronze badges

Источник

I am trying to run this VB function in Access which will delete records from my table based on the criteria of another table.

The code looks like this:

Function deleteDuplicates()

Dim FHA_Case As String
Dim SeqNumber As String

         Dim SQL1, SQL2 As String
           Dim rs1 As New ADODB.Recordset
           Set rs1 = New ADODB.Recordset
           Dim rs2 As New ADODB.Recordset
           Set rs2 = New ADODB.Recordset
           Dim cnn As New ADODB.Connection

cnn.Open «Provider=SQLOLEDB.1;Password=simple;Persist Security

Info=True;User ID=sa;Initial Catalog=UP6SQL;Data

Source=HOSTSQLEXPRESS;»

With rs1
        .CursorLocation = adUseServer
        .CursorType = adOpenDynamic
        .LockType = adLockPessimistic
    End With

SQL1 = «select * from [GOODSSN-611];»
rs1.Open SQL1, cnn, adOpenKeyset, adLockOptimistic

Do While Not rs1.EOF
FHA_Case = rs1![CaseNum]
SeqNumber = rs1![MrtgrSeqNum]

SQL2 = «select * from [BADSSN-4] where [CaseNum] = ‘» & FHA_Case & _
«‘ and [MrtgrSeqNum] = ‘» & SeqNumber & «‘;»

rs2.Open SQL2, cnn, adOpenKeyset, adLockPessimistic ‘(Error 3705)

If Not rs2.EOF Then
      rs2.Delete
   End If
   rs1.MoveNext

    Loop
   rs1.Close
   rs2.Close
   cnn.Close
End Function

I get the error msg. «Operation is not allowed when the object is open»

Источник

I am getting error 3705 after adding the line

[MTSU_Data].[Tool no]=" CInt(WsInput.Range("J" & c).value) & ";"

The highlighted line where the error occurs is at

.ActiveConnection = conn

Full code

Dim conn As ADODB.Connection
Dim Accdata As ADODB.Recordset
Dim Accfield As ADODB.Field
Dim wsQueryR As Worksheet, wsFinal As Worksheet

Set wsFinal = Worksheets("Final")
Set conn = New ADODB.Connection
Set Accdata = New ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:Userswongki7DesktopMTSU Db.accdb;"
conn.Open
'G:BusUnitsMTSUMTSUMold Tool Set UpMTSU  ReportsShift ReportsMTSU Db.accdb;"
' On Error GoTo CloseConnection

  For c = 2 To WsInput.Range("J" & Rows.Count).End(xlUp).Row
    With Accdata
      .ActiveConnection = conn
      .Source = "SELECT * FROM [MTSU_Data] Where [MTSU_Data].[Date]>= #" _
            & Format(CDate(WsInput.Range("A2").value), "mm/dd/yyyy") & " # AND [MTSU_Data].[Date]<= #" _
            & Format(CDate(WsInput.Range("A3").value), "mm/dd/yyyy") & " # AND [MTSU_Data].[LT] = " _
            & CInt(WsInput.Range("M2").value) & " AND [MTSU_Data].[Tool no]=" _
            & CInt(WsInput.Range("J" & c).value) & ";"
      .LockType = adLockReadOnly
      '.CursorType = adOpenForwardOnly
      .Open
    End With
  Next
  Worksheets("Result").Select
  Sheets("Result").Range("a2").CopyFromRecordset Accdata
  Accdata.Close
  conn.Close

Any help is appreciated. Thanks and have a good day ahead.

Rdster

1,8461 gold badge16 silver badges30 bronze badges

asked Feb 1, 2016 at 5:55

Can you not try to move the line

.ActiveConnection = conn

To outside the loop. Obviously fully qualifying it..

Accdata.ActiveConnection = conn

That looks to me that you are trying to make the connection for each iteration and I think it is only necessary once… maybe.

answered Feb 1, 2016 at 8:04

PaulGPaulG

1,0317 silver badges9 bronze badges

To best fit your code, Id try something like:

Sub SomeRoutine()

Dim conn As ADODB.Connection
Dim Accdata As ADODB.Recordset
Dim Accfield As ADODB.Field
Dim wsQueryR As Worksheet, wsFinal As Worksheet
Dim c As Long

Set wsFinal = Worksheets("Final")
Set conn = New ADODB.Connection
Set Accdata = New ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:Userswongki7DesktopMTSU Db.accdb;"
conn.Open
Accdata.ActiveConnection = conn
Accdata.LockType = adLockReadOnly

For c = 2 To WsInput.Range("J" & Rows.Count).End(xlUp).Row
    Call Accdata.Open("SELECT * FROM BLah Blah")
    Sheets("Result").Range("a2").CopyFromRecordset Accdata
    Accdata.Close
Next
conn.Close
End Sub

You may be able to look at the way you build the SQL request to build a query to get the data in one go and and do a single paste to Excel with the CopyFromRecordset…. but that’s a different issue.

answered Feb 1, 2016 at 9:41

PaulGPaulG

1,0317 silver badges9 bronze badges

A mistake I’ve made many times before. When assigning objects, you need to use Set. Change the line to

Set .ActiveConnection = conn

.Source and .LockType are scalar properties, so they don’t need Set, but .Activeconnection does.

answered Apr 28, 2016 at 20:26

GlennFromIowaGlennFromIowa

1,5961 gold badge13 silver badges19 bronze badges

Источник

VBForums
Visual Basic
Visual Basic 6 and Earlier
[RESOLVED] runtime error 3705

Aug 31st, 2012, 07:15 AM

#1
Thread Starter

Member
[RESOLVED] runtime error 3705
I’m having error in making a login in rs.open it says «operation not allowed when the object is open»

Code:

Dim rs As New ADODB.Recordset Private Sub Command1_Click() OpenCon rs.Open "SELECT * FROM [tbladmin] WHERE [admin] = '" & txtusername.Text & "'", Con, adOpenStatic, adLockOptimistic If rs.RecordCount <> 0 Then If rs![password] = txtpassword.Text Then If rs![userType] = "Principal" Then MsgBox "The Principal has login", vbOKOnly + vbInformation, "LOGIN" End If End If End If End Sub
Aug 31st, 2012, 08:58 AM

#2
Re: runtime error 3705
can you try the following now ?
Code:

Dim rs As New ADODB.Recordset Private Sub Command1_Click() OpenCon 'did you check this procedure . rs.Open "SELECT * FROM [tbladmin] WHERE [admin] = '" & txtusername.Text & "'", Con, adOpenStatic, adLockOptimistic If rs.RecordCount <> 0 Then If rs![password] = txtpassword.Text Then If rs![userType] = "Principal" Then MsgBox "The Principal has login", vbOKOnly + vbInformation, "LOGIN" End If End If End If rs.close con.close End Sub
Aug 31st, 2012, 09:14 AM

#3
Thread Starter

Member
Re: runtime error 3705
Originally Posted by firoz.raj

can you try the following now ?

Code:

Dim rs As New ADODB.Recordset Private Sub Command1_Click() OpenCon 'did you check this procedure . rs.Open "SELECT * FROM [tbladmin] WHERE [admin] = '" & txtusername.Text & "'", Con, adOpenStatic, adLockOptimistic If rs.RecordCount <> 0 Then If rs![password] = txtpassword.Text Then If rs![userType] = "Principal" Then MsgBox "The Principal has login", vbOKOnly + vbInformation, "LOGIN" End If End If End If rs.close con.close End Sub

I tried it but when I clicked login button nothing would appear not even the message box I added nothing worked
Aug 31st, 2012, 12:20 PM

#4

Re: runtime error 3705

That would most likely mean that your OpenCon routine is trying to open a connection that is already open.
Sep 1st, 2012, 01:40 AM

#5
Re: runtime error 3705
The RecordCount property can’t always be trusted, it’s safer to check whether BOF and EOF are both true (which means that nothing was returned from the Query) also, I’d be a bit more informative and modify Frozen’s code, since you’re currently not reporting any Login errors:

Code:

OpenCon 'did you check this procedure . rs.Open "SELECT * FROM [tbladmin] WHERE [admin] = '" & txtusername.Text & "'", con, adOpenStatic, adLockOptimistic If Not (rs.BOF And rs.EOF) Then If rs![password] = txtpassword.Text Then If rs![userType] = "Principal" Then MsgBox "The Principal has login", vbOKOnly Or vbInformation, "LOGIN" Else MsgBox "Login Successful", vbOKOnly Or vbInformation, "LOGIN" End If Else MsgBox "Invalid Password", vbOKOnly Or vbInformation, "LOGIN" End If Else MsgBox "Invalid Username", vbOKOnly Or vbInformation, "LOGIN" End If rs.Close con.Close

BTW If you insist on using RecordCount, don’t use ‘<> 0’ since a -1 value can be returned (indicating that it’s not supported) use ‘ > 0’ instead
Last edited by Doogle; Sep 1st, 2012 at 01:58 AM.
Sep 1st, 2012, 09:14 PM

#6

Thread Starter

Member

Re: runtime error 3705

I used your code Doogle the username and password matches but it’ll display msgbox «login succesful» instead of the principal has log in
Sep 1st, 2012, 11:16 PM

#7

Re: [RESOLVED] runtime error 3705

Check the ‘usertype’ in your Table for the admin and password you entered. Remember that by default all tests of strings are case sensitive (ie if you have ‘principal’ in the table it will not match ‘Principle’)

VBForums
Visual Basic
Visual Basic 6 and Earlier
[RESOLVED] runtime error 3705

Posting Permissions

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

BB code is On
Smilies are On
[IMG] code is On
[VIDEO] code is On
HTML code is Off

Forum Rules

Click Here to Expand Forum to Full Width

Источник

Hi,

I’m getting Run Time Error 3705: OPERATION IS NOT ALLOWED WHEN THE OBJECT IS OPEN when I click on a Label Button with code behind it in a MS Access Form I have. I think I found the thing causing Error 3705 within the code below which I believe has to with this variable «rstTakLists» that I created.
</SPAN>
SIDE NOTE: When I click LABEL BUTTON this is what literally happens…
1st: A message window appears that I created and all the User has to do is click on the OK button to continue
2nd: Another message window appears that I created that asks the User a question and the User has the option to click on either the Yes Button or No Button to continue

So when I click the Label button, then the OK button, and then on the No Button I get a window with Run Time Error 3705 and it highlights this VBA code «.Source = strSQLStmt» in yellow thus indicating that this is where the error is occurring. I don’t think the problem is with this VBA code «.Source = strSQLStmt», I think I am getting the Error 3705 because I need to close object “rstTakLists”. </SPAN>

I need help with closing this variable “rstTakLists” without it affecting the rest of the VBA code from executing properly. I don’t know where to place «rstTakLists.Close» without it affecting the rest of the VBA code and thus resulting in another Error of some sort.</SPAN>

Here is my code:

Code:

Dim conn       As ADODB.Connection
Dim rstTakLists  As ADODB.Recordset
Set conn = CurrentProject.Connection
Set rstTakLists = New ADODB.Recordset
 
If (totalSavings < DeltaCost) Then
ContinueFlag = True
ProgramCount = 0
With rstTakLists
        strSQLStmt = "SELECT tblTakList.TakListID As TakListID " & _
                     " FROM tblTakList" & _
                     " ORDER BY tblTakList.TakListID;"
        .Source = strSQLStmt
        .ActiveConnection = CurrentProject.Connection
        .Open Options:=adCmdTxt
    End With
    If Not (rstTakLists.EOF) Then
        rstTakLists.MoveFirst
    Else
        boolError = True
        strErrorMsg = "Error Code 3: Populating Programs Array failed, empty Recordset."
        intErrorCode = 3
        GoTo Scenario_Error
    End If
End If
rstTakLists.MoveFirst
Do While Not (rstTakLists.EOF)
    ProgramCount = ProgramCount + 1
    arrProgramCutPeople(rstTakLists!TakListID) = 0
    arrProgramCutDollars(rstTakLists!TakListID) = 0
    arrProgramCutPercentage(rstTakLists!TakListID) = 0
    rstTakLists.MoveNext
Loop

****So I added the following code after the «Loop» and it got rid of Error 3705 which was making me feel good, but then I ran a different scenario in which the User clicks on the Label Button, then clicks on the OK Button, and then clicks on the Yes Button and………I get Run Time Error 3704: OPERATION IS NOT ALLOWED WHEN THE OBJECT IS CLOSED….

Code:

     rstTakLists.Close
     Set rstTakLists = Nothing
     Set rstTakLists = New ADODB.Recordset

…..Error 3704 points me to this part of the code:

Code:

rstTakLists.MoveFirst</SPAN>

Now I am really stuck and need some help from the forum because I don’t where to go from here.

Thanks,

— BC

How to fill five years of quarters?

Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

You only open the recordset when (totalSavings < DeltaCost). So this statement will fail if the recordset was never opened:
rstTakLists.MoveFirst (i.e., whenever totalSavings is not less than DeltaCost)

P.S.
You might need to post more of your code.

xenu,

I think I kind of understand what you saying. Below is more of a complete version of the code that I am using. The only Run Time Error I am getting now is 3704, which again points to this (rstTakLists.MoveFirst). So taking what you said, how can I go about fixing this error?

Code:

Dim conn                                                      As ADODB.Connection
Dim rstTakLists                                             As ADODB.Recordset
Dim boolProgramCut                                     As Boolean
Dim boolError                                               As Boolean
Dim boolUserWantsProgramCuts                  As Boolean
Dim totalSavings                                           As Double
Dim DeltaCost                                               As Double
Dim arrProgramCutPeople(1 To 1000)             As Double
Dim arrProgramCutDollars(1 To 1000)             As Double
Dim arrProgramCutPercentage(1 To 1000)         As Double
Dim ContinueFlag                                            As Boolean

Set conn = CurrentProject.Connection
Set rstTakLists = New ADODB.Recordset

boolError = False
boolProgramCut = False
boolUserWantsProgramCuts = False


If Not (boolProgramCut) Then
    totalSavings = 0
    RequirementCost = 0
    boolUserWantsProgramCuts = False'/then I have more code that continues here below 

 
If (totalSavings < DeltaCost) Then
ContinueFlag = True
ProgramCount = 0

If Not (boolProgramCut) Then '/I forgot to add this line of code in my original thread
    With rstTakLists
        strSQLStmt = "SELECT tblTakList.TakListID As TakListID " & _
                     " FROM tblTakList" & _
                     " ORDER BY tblTakList.TakListID;"
        .Source = strSQLStmt
        .ActiveConnection = CurrentProject.Connection
        .Open Options:=adCmdTxt
    End With
    
If Not (rstTakLists.EOF) Then
        rstTakLists.MoveFirst
    Else
        boolError = True
        strErrorMsg = "Error Code 3: Populating Programs Array failed, empty Recordset."
        intErrorCode = 3
        GoTo Scenario_Error
    End If
End If

rstTakLists.MoveFirst

Do While Not (rstTakLists.EOF)
    ProgramCount = ProgramCount + 1
    arrProgramCutPeople(rstTakLists!TakListID) = 0
    arrProgramCutDollars(rstTakLists!TakListID) = 0
    arrProgramCutPercentage(rstTakLists!TakListID) = 0
    rstTakLists.MoveNext
Loop
     rstTakLists.Close
     Set rstTakLists = Nothing
     Set rstTakLists = New ADODB.Recordset


'/This part of the code is the mesage box with the Yes or No Button
If (totalSavings < DeltaCost) Then
    If (Not boolUserWantsProgramCuts) Then
     Response = MsgBox("Costs could not be met for this Year " & currentYear & "  by cutting AT and TC, would you like to cut programs?", vbYesNo)
     If Response = vbYes Then
         continue = True
         boolUserWantsProgramCuts = True
     Else
         continue = False
     End If
    Else
     continue = True
    End If
       
    If (continue = True) Then......'/then I have more code that continues here below

Thank you for your help,

— BC

Possibly the simplest anser is that don’t need that line at all. The recordset will be at the first record when you open it. Try that!

xenou,

I tried that but that didn’t seem to do the trick.

-bc

Solved: Solution is just avery good understanding of the issues at hand

xenou,

I just wanted to thank you for your time in helping me out. I’ll be closing out this thread with a better understanding of the problem at hand and will rewrite some code in an attempt to eliminate these errors.

— BC

Re: Solved: Solution is just avery good understanding of the issues at hand

No problem. Glad I could be of some help.
ξ

Источник

<img decoding="async" src="https://www.vbforums.com/images/icons/completeclear.gif" alt="Resolved" /> [RESOLVED] runtime error 3705

Re: runtime error 3705

Re: runtime error 3705

Re: runtime error 3705

Re: runtime error 3705

Re: runtime error 3705

Re: [RESOLVED] runtime error 3705

<img decoding="async" src="https://www.vbforums.com/images/buttons/collapse_40b.png" alt /> Posting Permissions

[RESOLVED] runtime error 3705

Posting Permissions