multiple data entry from excel to access database and loop

[sathyaganapathi]

Hello,
I have a excel vba code used with userform to enter data into access database.
The code worke fine for single entry. But, some times I have to enter the same record multiple times, say 5 to 6 times. I tried with 'for loop'. but it is thowing error saying "operation is not allowed when the object is open".
Could someone help me on this please?

Code is as below which works fine for single entry.

Private Sub Save1_Click()
        
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim qry As String
          
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\PlabInputDb.accdb"
    
    If Me.txtId.Value <> "" Then
        qry = "SELECT * FROM TBL_PlabInput WHERE ID = " & Me.txtId.Value
    Else
        qry = "SELECT * FROM TBL_PlabInput Where ID = 0"
    End If
    
    rst.Open qry, cnn, adOpenKeyset, adLockOptimistic
    
    If rst.RecordCount = 0 Then
        rst.AddNew
    End If
    
    rst.Fields("Process_DateTime").Value = CDate(Me.txtDate.Value)
    rst.Fields("File_Name").Value = Me.txtFileName.Value
    rst.Fields("Order_No").Value = Me.OrdNo.Value
 
    rst.Update
 
    Me.txtId.Value = ""
    Me.txtDate.Value = VBA.Format(Now(), "mm/dd/yyyy HH:mm")
    Me.txtFileName.Value = ""
    Me.OrdNo.Value = ""
 
    rst.Close
    cnn.Close
  
    MsgBox "Updated Successfully", vbInformation
    Call Me.List_box_Data
    
End Sub

Code used with for loop to copy same record upto 6 times.

Private Sub Save1_Click()
        
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim qry As String
          
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\PlabInputDb.accdb"

    On Error GoTo Get_Out
    RunTimes = InputBox("enter the number of line to copy the Sample data?", "Copy Sample (max 6 lines)")
    On Error GoTo 0
    If RunTimes > 6 Then
        MsgBox "enter value 6 or less only"
    Exit Sub
        ElseIf RunTimes = 0 Then
    Exit Sub
    
    Else
        
    For X = 1 To RunTimes

    
    If Me.txtId.Value <> "" Then
        qry = "SELECT * FROM TBL_PlabInput WHERE ID = " & Me.txtId.Value
    Else
        qry = "SELECT * FROM TBL_PlabInput Where ID = 0"
    End If
    
    rst.Open qry, cnn, adOpenKeyset, adLockOptimistic
    
    If rst.RecordCount = 0 Then
        rst.AddNew
    End If
    
    rst.Fields("Process_DateTime").Value = CDate(Me.txtDate.Value)
    rst.Fields("File_Name").Value = Me.txtFileName.Value
    rst.Fields("Order_No").Value = Me.OrdNo.Value
 
    rst.Update
     Next X
     Get_Out:
     End If
 
    Me.txtId.Value = ""
    Me.txtDate.Value = VBA.Format(Now(), "mm/dd/yyyy HH:mm")
    Me.txtFileName.Value = ""
    Me.OrdNo.Value = ""
 
    rst.Close
    cnn.Close
  
    MsgBox "Updated Successfully", vbInformation
    Call Me.List_box_Data
    
End Sub

 

[bobsan42]

If the line that throws the error is rst.open ... Then the reason is that you have to open the recordset before the loop. Otherwise you try to open it with each loop but it is already opened after the first.
So move the line for x = ... After rst.open ...

PS: i don't like the condition if recordcount=0
In this way you will only be able to add one record and not 6. To do it in this way you have to actually close the rst in the for loop. Looking at it again - discard the first idea above.
However you will need 6 different IDs but it will always get the value from txtid whill does not change?!

 

[bobsan42]

My edit time expired ?.
Do you understand what I mean?

 

[bobsan42]

Won't it be easier to cnn.execute sql insert or update clause?

 

[sathyaganapathi]

Won't it be easier to cnn.execute sql insert or update clause?

Hi bobsan42..
Thanks again for your time. the above step you mentioned is over my head. I am learner with very basic knowledge.
As mention by you I tried with moveing for x = after rst.open. But, code worked to enter data only ones. it is not copied to multiple lines as expected.
any other way?

 

[bobsan42]

Hi bobsan42..
Thanks again for your time. the above step you mentioned is over my head. I am learner with very basic knowledge.
As mention by you I tried with moveing for x = after rst.open. But, code worked to enter data only ones. it is not copied to multiple lines as expected.
any other way?

Read all the things I wrote.
Undo the change you did. And yes this result is to be expected considering your code.

 

[sathyaganapathi]

Read all the things I wrote.
Undo the change you did. And yes this result is to be expected considering your code.

Hi bobsan42,
I just did a turn around by putting the whole code inside for loop. working well.
Thanks a lot for your kind support.. good night.