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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
Back
Top