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
DoCmd.SetWarnings False
DoCmd.RunSQL “DELETE * FROM TableName” DoCmd.SetWarnings True
Or use the below SQL statement to do the deletion.
