ADOB - Update runs very slow

[fluffyvampirekitten]

I'm doing an update table using vba - ADODB connection

But it runs too slow
Is there any other way to do an update

Option Compare Database
Option Explicit
Sub main()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
'initated recordset object
objRecordset.ActiveConnection = CurrentProject.Connection
Call objRecordset.Open("IntraSystemData", , , adLockBatchOptimistic)
End Sub
Sub Update_INS()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
Dim i As Integer
Dim value As Variant

'initated recordset obejct
objRecordset.ActiveConnection = CurrentProject.Connection
 Call objRecordset.Open("IntraSystemData", , , adLockBatchOptimistic)
 
DBEngine.SetOption dbMaxLocksPerFile, 50000
 
 ' Find no_of_ins_v for each record
 Do Until objRecordset.EOF
            
    If objRecordset("NO_INS").value = 0 Then
        objRecordset("INSTITUTION") = "P-NHGP"
        objRecordset.UpdateBatch
    Else
        DoCmd.RunSQL "Update IntraSystemData INNER JOIN IntraSystem_INS ON [IntraSystemData].[GROUP_NO] = [IntraSystem_INS].[GROUP_NO]" & _
        "SET [IntraSystemData].[INSTITUTION]  = [IntraSystem_INS].[FV_INS]" & _
        "WHERE [IntraSystemData].[GROUP_NO]  = [IntraSystem_INS].[GROUP_NO]"
                       
    End If
    objRecordset.MoveNext
Loop
        
    objRecordset.Close
    Set objRecordset = Nothing
    
End Sub

[ranman256]

Why are you using ADO and code?
Just use an access query, its faster.

I'm doing an update table using vba - ADODB connection

But it runs too slow
Is there any other way to do an update

Option Compare Database
Option Explicit
Sub main()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
'initated recordset object
objRecordset.ActiveConnection = CurrentProject.Connection
Call objRecordset.Open("IntraSystemData", , , adLockBatchOptimistic)
End Sub
Sub Update_INS()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
Dim i As Integer
Dim value As Variant

'initated recordset obejct
objRecordset.ActiveConnection = CurrentProject.Connection
 Call objRecordset.Open("IntraSystemData", , , adLockBatchOptimistic)
 
DBEngine.SetOption dbMaxLocksPerFile, 50000
 
 ' Find no_of_ins_v for each record
 Do Until objRecordset.EOF
            
    If objRecordset("NO_INS").value = 0 Then
        objRecordset("INSTITUTION") = "P-NHGP"
        objRecordset.UpdateBatch
    Else
        DoCmd.RunSQL "Update IntraSystemData INNER JOIN IntraSystem_INS ON [IntraSystemData].[GROUP_NO] = [IntraSystem_INS].[GROUP_NO]" & _
        "SET [IntraSystemData].[INSTITUTION]  = [IntraSystem_INS].[FV_INS]" & _
        "WHERE [IntraSystemData].[GROUP_NO]  = [IntraSystem_INS].[GROUP_NO]"
                       
    End If
    objRecordset.MoveNext
Loop
        
    objRecordset.Close
    Set objRecordset = Nothing
    
End Sub

[fluffyvampirekitten]

Okay , Ill try use an access query instead. Thank you