how do i count the number of tables in an access database?

N

navy a

i'm a beginner using Access 2003. what i want to do is to automate the
process of setting the first field of all the tables in one of my databases
as a primary key.i was thinking if i could count the number of tables in my
database(i don't know how to use the count function in access),and maybe use
a FOR loop in a way that it would get the table, go to the first field, using
the 'Alter table' statement set it as a PK, commit the changes and go to the
next table and so on until it reaches the last table in the database. is
there any other way of doing it?
 
A

Allen Browne

You can loop through the TableDefs collection to get the names of the
tables. Skip the attached tables (where the Connect property is not
zero-length), the system tables (names starting with MSys), and temp tables
(names starting with ~).

For each TableDef, you can then loop through the Fields collection to get
the name of fields, or if you just want the first one it will be:
dbEngine(0)(0).TableDefs("MyTable").Fields(0).Name

You can then create an index on the table for this field, and set the
index's Primary property to True.

This kind of thing:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index
Dim strTable As String

Set db = CurrentDb()
For Each tdf In db.TableDefs
If (tdf.Attributes And (dbSystemObject Or dbHiddenObject)) = 0 Then
strTable = tdf.Name
Debug.Print strTable
If (Len(tdf.Connect) = 0) And Not (strTable Like "~*") Then
Set ind = tdf.CreateIndex("PrimaryKey")
With ind
.Fields.Append .CreateField(tdf.Fields(0).Name)
.Unique = False
.Primary = True
End With
tdf.Indexes.Append ind
End If
End If
Next
 
Top