Hi all,
When importing files to my Access tables I keep track of how many records have been added / removed at each stage by counting the number of records at different points and then finding the differences where I'm interested.
When I designed my import function I used CurrentDb.TableDefs(TargetTable).RecordCount all over to count records.
Recently I have come to understand that using CurrentDb in each instance is considered bad practice and I have since changed to declaring CurrentDb as an explicit variable. Retrospectively updating my code I now have:
This works for the initial count of the table, but when I then change the number of records in a table db.TableDefs(TargetTable) doesn't refresh itself and so .RecordCount still returns the number of records from when db was initially set. The reason it was working previously was because CurrentDb.TableDefs(TargetTable).RecordCount would create a new instance of CurrentDb each time it was called (or something like that).
So is there an elegant way to 're-set' or refresh the TableDefs to get the latest .RecordCount? The only way I've found is to repeat the code
before each time I want to recount, which is not ideal.
Is this even the most efficient option for counting all the records in a table? I've tried out DCount("*", TargetTable) but some of the tables have 800k+ records and this function takes ~3s each time with those, compared against td.RecordCount which takes <0.1s each time.
Cheers
When importing files to my Access tables I keep track of how many records have been added / removed at each stage by counting the number of records at different points and then finding the differences where I'm interested.
When I designed my import function I used CurrentDb.TableDefs(TargetTable).RecordCount all over to count records.
Recently I have come to understand that using CurrentDb in each instance is considered bad practice and I have since changed to declaring CurrentDb as an explicit variable. Retrospectively updating my code I now have:
Code:
Dim db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb()
Set td = db.TableDefs(TargetTable)
rCount1 = td.RecordCount
So is there an elegant way to 're-set' or refresh the TableDefs to get the latest .RecordCount? The only way I've found is to repeat the code
Code:
Set db = CurrentDb()
Set td = db.TableDefs(TargetTable)
Is this even the most efficient option for counting all the records in a table? I've tried out DCount("*", TargetTable) but some of the tables have 800k+ records and this function takes ~3s each time with those, compared against td.RecordCount which takes <0.1s each time.
Cheers