Microsoft Access Development

When adding items to a Collection object, remember not to add something

The following code will add a particular field's value from an ADODB.RecordSet object to a Collection. However, the scope of the RecordSet object is only within this function. After it completes, each item within the Collection object will have a null value.

Public Function GetPeriods() As Collection
Dim rs As ADODB.Recordset
Dim col As Collection

Set rs = New ADODB.Recordset
Set col = New Collection

I swear, I haven't seen something this stupid in a very long time (and I work with Access everyday).

DoCmd.OpenTable "TblOracleUploadAdjs", acViewNormal, acEdit
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdDelete
DoCmd.Close acTable, "TblOracleUploadAdjs"

For the uninitiated, what this does is open a table up, select all the records, deletes them, then closes the table. The better way to do this would be to execute the following query:

delete from TblOracleUploadAdjs;

Or to fully solve this:

Not that this is a very elegant solution, but I needed to get rid of a bug in production. The cause of this was the check for one of the recordset's fields. After looping a second time and MoveNext() bringing the recordset to EOF, you couldn't check its values anymore. So to fix this I added the following line:

If Not rsUinvcat.EOF Then

This checks your boundary conditions before attempting to read a value that might not currently exist. Always check your bounds!


For i = 0 To intCols
If Not rsUinvcat.EOF Then