Counting Database Objects

[Transentient]

I have been using the attached code within an Access 97 database to count different types of tables within the application. On calling the function a variable is passed telling the code what table type to count, i.e. ADD for Address Tables, CON for contacts tables.

It has always worked find in Access 97, however on upgrading the database to 2007, the code doe'nt work, seems to have problems with the Tabledefs funcrtion and the Left function.

Can anyone help, is there a different way of doing this with Access 2007?

Thanx in advance

Public Function Count(CountCriteria As String) As Integer
Dim i As Integer '
Dim db As Database '
Dim System_Filters '
Dim Current_TableName 'Declare Used Variables
Dim Hidden_Filters '
Dim Other_Filters '
Set db = DBEngine(0)(0) 'Define Global Variables
Count = 0 ' ' '
db.TableDefs.Refresh 'Refresh Table Objects List ' '
For i = 1 To db.TableDefs.Count 'Define & Excecute Loop 'Current_TableName = db.TableDefs(i) '.Name ' '
System_Filters = Left(Current_TableName, 4) 'Define Local Variables
Hidden_Filters = Left(Current_TableName, 1) 'For Filters
Other_Filters = Left(Current_TableName, 3) '

'Filter to Remove System Objects & User Defined Items
If System_Filters <> "MSys" And System_Filters <> "USys" And _
Hidden_Filters <> "~" And Other_Filters = CountCriteria Then
Count = Count + 1 'Update Variable
End If 'End Filter
Next i 'Loop Back
End Function

 

[ajetrumpet]

use "currentdb.tablesdefs.count" instead of the "dbengine(0)(0)" thing.

delete "db.tabledefs.refresh" - what's the point of this??????

 

[LPurvis]

It sounds more like you have a References issue than anything else.
(The VBA function Left failing is a classic sign of this).

Check those (in Tools>References).
Aything missing needs to be rectified by adding the reference library (you might be able to remove it if it's redundant).
Is this just a change of Access version? Or is this also a different PC? Such things can often have a much greater bearing - but folks do tend to focus on the change in Access rather than the more obvious entire machine discrepency. ;-)

If all references appear to be present then you can try unchecking one and then reselecting it. (Choose DAO as its always the best culprit - in Access 2007 it might actually be the annoyingly named "Microsoft Office 12.0 Access Database Engine Object Library").
Alternatively re-register all references found.
e.g. http://www.utteraccess.com/forums/showflat.php?Cat=&Board=84&Number=1558221

As for the questions raised...
The Currentdb function automatically refreshes all child collections of the database object it returns. Consequently you don't need to manually Refresh the collection to ensure it's up to date.
However as you weren't using that - it's possible that the database wasn't aware of recent additions and did need refreshing.
I can understand why you're doing what you are (iterating through and counting like types).
But since the tables are local anyway then you can just query the mSysObjects table. A simple DCount will let you do that.

Cheers.

 

[DCrake]

What I want to know is why do you need to count these objects. Do it have any significance to the operation of the application?