TableDefs not recognized (I think?)

[Deekay]

I am writing some code to seen if an import error table has been created.

When using the VBA editor in Access and I type in lowerrcase the following two statements
dim db as DAO.database
dim td as DAO.tabledef

it corrects it to
Dim db As DAO.Database
Dim td As DAO.TableDef
recognising the reserved words each time. However typing
Dim mydefs As DAO.TableDefs
is reverting to lowercase 'tabledefs' AND no subsequent code referring to 'mydefs' or any code with 'TableDefs' appears to be recognized or actioned.
This is happening regardless of whether I use 'DAO' or not.
Am I missing something or using the wrong syntax for tabledefs? Anybody else had the same problem?

Any help thankfully received.

[gershwyn]

The TableDefs collection is a member of the Database object. I get an error when I refer to tabledefs or dao.tabledefs, but the following works fine:

msgbox CurrentDb.TableDefs.Count

[missinglinq]

It think it should be TableDef, as in the example that works, not TableDefs, which doesn't. Try dropping the s from the end.

Linq ;0)>

[Deekay]

Thank you Gershwyn and Missingling for your replies.
db.tabledefs.count appears to be working so that is recognized. Tabledefs is definately different to tabledef I believe where the first refers to the collection of tables and the second is used to refer to an individual table. But following on from my previous post...

str = mydefs.td.name (where str is a string variable)

is generating an error on compiling as not recognized.

Any further thoughts from anyone?

[gershwyn]

There is no member of the tabledefs collection called "td" so that will give an error. td is an object variable you've defined yourself.

The syntax to get the name of the first tableDef object in the collection would be this:

tblName = mydefs(0).Name

If you know the name, you can also refer to it this way:

Debug.Print myDefs("TableName").RecordCount

Note: Str is the name of a built in function in Access, so I would avoid using it as a variable name.

[Stewart Ross]

As Linq and Gershwyn have indicated, you appear to be trying to access the tabledefs collection itself instead of the relevant tabledef contained within the collection. I'm not sure why you'd want to do so, as all the applications of the tabledefs collection I know of result from retrieving a given tabledef from the collection and working on that.

The Tabledefs() collection is not an object which you can declare in the way you have tried; it is a container for the objects within it. If you wanted to declare an object as a copy of your tabledefs collection as far as I can tell you'd have to declare it as a Collection object. I don't think this is at all necessary.

I have found that if you use the CurrentDB function to return the Tabledefs collection you can get problems with apparent lack of consistency of updates etc. In these circumstances it is best to declare a new database object, assign it the value of CurrentDB, then refer to the Tabledefs collection of that object for whatever you need to do, as shown in the example below:

Dim MyDb as Database
Dim MyTableDef as DAO.Tabledef
Set MyDb = CurrentDB
Set MyTableDef = MyDb.Tabledefs("sometablename")
...

-Stewart

[Deekay]

Thanks for the various contributions - have solved it now!
(Still strange that in declaring tabledefs it reverts to lowercase whereas other declarations using reserved words capitalise in some way - as has happened using a code based on Stewart's suggestion (and which works) - somthing I'm still not understanding.)