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

use-array-to-read-dat-from-and-write-data-to-tables-and-queries

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.

More Posts

Excel Vlookup Function

The VLOOKUP function in Excel is a powerful tool that allows you to search for a specific value in a table and return a corresponding