Use Arrays to Read and Write Data in Access VBA

In Microsoft Access, using only queries and macros without VBA will only take you so far. A professional database requires calculations and a user interface to make it versatile and user-friendly. Access VBA is worth the effort to learn — the payoff can be significant.

Reading data from tables and queries and writing results back to tables are the basic requirements of Access VBA. Since databases are structured in recordsets, reading multiple records is the norm. A good programmer must know how to use arrays to temporarily store that data.

Read Data from a Table or Query

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

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

Delete Records from a Table

Using a recordset loop:

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

Or use a SQL statement for faster deletion:

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