Random Access Files ..how?
[This is a follow up to the article "Why Random Access Files" (in VBA)]
Random Access Files work because, unlike Sequential files, they use structure. The structure results in each record taking up (reserving) exactly the same amount of space.
Creating a Record Type
We use the Type command to define the structure of the records in our Random Access File
Type FCast
Qty As Long 'Storage size = 4 bytes
Sell As Currency 'Storage size = 8 bytes
Cost As Currency 'Storage size = 8 bytes
GM As Single 'Storage size = 4 bytes
Comments As String * 30 'Storage size = 30 bytes
End Type
Opening a Random Access File
When we open a random access file we must specify the record length in the Open statement. The record length is obtained by using the Len statement on the variable type.
Dim myFCast As FCast
Open "RepCode_Cust_Product.dat" for Random As #1 Len = Len(myFCast)
Adding Records with Put
After you open a file for Random Access, use the Put command to store records in the file:
' Populate record fields
myFCast.Qty = lngQty
myFCast.Sell = curSell
myFCast.Cost = curCost
myFCast.GM = (curSell - curCost) / curSell
myFCast.Comments = "tendered price"
' Now write this to file
'Put filenumber, [recnumber], variablename
'in this case
Put #1, [recnumber], myFCast
Retrieving Records with Get
To retrieve Records use the Get statement
Get filenumber, [recnumber], variablename
Close the Random Access File
Close filenumber
' in this case
Close #1
Application design considerations
From an application design perspective it is worth carefully thinking through the structure of the records (to minimise the space required) and your rationale for record numbers.
For example if our records needed to cover history back to January 2000 and go through to December 2025 with record types of Budget, Forecast and Actual we might need to set up a table to determine the Record number.
' Table to determine record numbers
Month Budget Forecast Actual
Jan 2000 1 2 3
Feb 2000 4 5 6
... and of course these record numbers can easily be determined with a formula
#excel #vba #accessanalytic #randomaccess