Use Array to read dat from and write data to Tables and Queries

msofficeshare logo

Requirement:  Access Visual Basic Knowledge

File to download:  None

Related articles: Use Embedded SQL Query

In Microsoft Access, using only queries and macros (not VBA) can not make you go far enough.  A professional database requires certain calculations and user interface to make it versatile and user-friendly.  Access VBA (Visual Basic Application) is the final frontier that worth the effort to learn and master.  The payoff can be beyond your imagination.

 

Reading data from tables and queries and writing back the results to tables are the basic requirements of using Access VBA.  Since database is structured in recordsets, reading multiple records are essentially a norm rather than exception.  A good programmer must possess the knowledge of using arrays to temporarily store such data.  Below are the examples to read, edit, and write back data.

    Dim DB As Database

    Dim rs As Recordset

    Dim i as Long, a as Long 

 

    Set rs = DB.OpenRecordset("Select * from LoanHistory where (Issuer = " & IssuerChoice &  ") and (Facility = " & FacilityChoice & ") ")

    rs.MoveLast
    RecordCount = rs.RecordCount

    a = RecordCount - 1

 

    ReDim IssuerArray(a)
    ReDim PriceArray(a)

  
    rs.MoveFirst
    For i = 0 To a
       IssuerArray(i) = rs![Issuer]
       PriceArray(i) = rs![Price]

       rs.MoveNext
    Next i

 

    rs.Close

    db.Close

To use array to read data from tables or queries

To use array to write data back to a table

        Set rs = DB.OpenRecordset("AllocationHistory")

        rs.MoveLast
        rs.MoveNext
        

        For i = 0 To n
            rs.AddNew
            rs.Fields("Issuer").Value = IssuerArray(i)
            rs.Fields("Price").Value = PriceArray(i)
            rs.Update
        Next i
        rs.Close
        db.Close

To delete previous data in the table

    Dim DB As Database, rs As Recordset
    Set DB = CurrentDb()
    Set rs = DB.OpenRecordset("AllocationHistory")
    
    rs.MoveFirst
    rs.MoveNext
    Do While Not rs.EOF
       rs.Delete
       rs.MoveNext
    Loop
    rs.MoveFirst
    rs.Close

    db.Close

array logo

DoCmd.SetWarnings False

DoCmd.RunSQL "DELETE * FROM TableName" DoCmd.SetWarnings True

Or use the below SQL statement to do the deletion.