Access : VBA code : compacting DB + count records of a table

  • Thread starter Thread starter Ulrik Beersaerts
  • Start date Start date
U

Ulrik Beersaerts

Hello,

In a form I have several textboxes where the results from the count of the
records of several tables must appear.
The results I'm using now are comming from several Querry's. But you'll
understand that it takes much time to run al those querry's and to fill in
my form.
So I ask if there is an easyer way (f.e. with VBA) to come to the same
result ?

A second question : When I close my Database I would like that automaticly
my DB was compacted (now i've to do it manually by Tools ....)
Is there a possibility with VBA ?

Thank you already for your reactions,

Ulrik
 
Ulrik,

You coan use VBA to get the table recordcounts, like:

Dim rst As DAO.Recordset

Set rst = CurrentDB.OpenRecordset("Table1")
Me!txtbox1 = rst.RecordCount
rst.Close

Set rst = CurrentDB.OpenRecordset("Table2")
Me!txtbox2 = rst.RecordCount
rst.Close

'etc

Set rst = Nothing

If the number of tables / textboxes is ore than, say, 5, I would suggest you
use an array to assign table and tetxbox names and loop through:

Option Base 1
Dim rst As DAO.Recordset
Dim vNames(5,2) As Text
vNames(1,1) = "Table1"
vNames(2,1) = "Table2"
'etc
vNames(1,2) = "txtbox1"
vNames(2,2) = "txtbox2"
'etc

For i = 1 to 5
Set rst = CurrentDB.OpenRecordset(vNames(i,1))
Me!Controls(vNames(i,2)) = rst.RecordCount
rst.Close
Next

Set rst = Nothing

Either way, you need to add Microsoft DAO to your references in the VB
window for the code to run. You need to put this code in the right event
depending on your needs, e.g. Form Open (?).

For Compact On Close: Tools > Options > General > check Compact On Close, no
code required if you are using A2K or later (I think A97 didn't offer that
feature).

HTH,
Nikos
 
Back
Top