Store the results in array in access VBA

[YasserKhalil]

Hello everyone
I have tried the following code that deals with access table named "Approved" >> the code is looping through the records and fill each field "NewGrade" field and "NewExpiryDate" field with some values
Is it possible to store the results in an array and put the results in one shot in the fields "NewGrade" and "NewExpiryDate" .. as we can do in the excel application
Here's the code

Code: Select all

Sub Test()
    Dim rs As DAO.Recordset, dic As Object, sCode As String, sResult As String, i As Long
 
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    Set rs = CurrentDb.OpenRecordset("Approved")
    
    If Not (rs.BOF And rs.EOF) Then
        rs.MoveFirst
        i = 1
        Do
            sCode = rs.fields("SupplierCode").Value
        
            If sCode <> "" Then
                If Not dic.Exists(sCode) Then
                    sResult = GetGradeExpiryDate(CStr(sCode))
        
                    rs.Edit
                    rs.fields("NewGrade").Value = i * 2
                    rs.fields("NewExpiryDate").Value = i * 3
                    rs.UPDATE
                    dic(sCode) = Array(rs.fields("NewGrade").Value, rs.fields("NewExpiryDate").Value)
                Else
                    rs.Edit
                    rs.fields("NewGrade").Value = dic(sCode)(0)
                    rs.fields("NewExpiryDate").Value = dic(sCode)(1)
                    rs.UPDATE
                End If
            End If
     
            rs.MoveNext
            i = i + 1
            
            If i > 10 Then Exit Do
        Loop Until rs.EOF
    End If
End Sub

[HansV]

Access does not have a way to populate a table from an array without looping.

[YasserKhalil]

Thanks a lot. What about creating new fields for the data in the array and append those to the table?
It seems different from excel...
If not is it possible to make it faster ..? I think rs.Edit for each item is somewhat slow ..or what's your opinion?

[HansV]

It is usually faster to execute a SQL statement than to manipulate a recordset.

I fear that the problem is that you always want to do unusual things...

[YasserKhalil]

Sorry. Not unusal but less knowledge from my side. I am newbie in access VBA and I need to learn the best approaches and faster ones to do any task

Can you give me an example of how to execute SQL statements on such code?

[HansV]

As I mentioned, you do need a loop for your problem, that's why it's slow...

An alternative could be to populate a table in an Excel worksheet from the array and to import that table from Excel into your Access database.

[YasserKhalil]

Regardless what I am seeking for .. Can you please have a look at the code and if there are any notes to tell me
I would like to take your notes as the best guidance for me

[HansV]

I have no comments.

[YasserKhalil]

Thanks a lot my tutor.