Hi all. I got a access vba 2000 code that i hardly understand what some of its part doing.
i need to learn this since i want to learn how to refrence these system tables.
I be happy if some expert explain to me what these part does.Thanks

part1:


Set rsMetadb = MetaDB.OpenRecordset("SysKeys")

part2:


For Each idx In tbl.Indexes
bUpdate = False
rsMetadb.AddNew
rsMetadb("Tablename") = tbl.Name
If idx.Primary = True Or idx.Unique = True Then rsMetadb("Keyname") = idx.Name


part3:


If bUpdate Then rsMetadb.Update


part4:


rsMetadb("Keytype") = "PRIMARY"

complete code:


Dim bUpdate, ref As String
Dim i As Integer


Set rsMetadb = MetaDB.OpenRecordset("SysKeys")

For Each tbl In db.TableDefs
If Left(tbl.Name, 4) <> "MSys" Then
For Each idx In tbl.Indexes
bUpdate = False
rsMetadb.AddNew
rsMetadb("Tablename") = tbl.Name
If idx.Primary = True Or idx.Unique = True Then rsMetadb("Keyname") = idx.Name
'Check primary key
If idx.Primary = True Then
rsMetadb("Keytype") = "PRIMARY"
bUpdate = True
Else
'Check Alternate key
If idx.Unique = True And idx.Primary = False And idx.Foreign = False Then
rsMetadb("Keytype") = "ALTERNATIVE"
bUpdate = True
End If
End If
If bUpdate Then rsMetadb.Update
Next idx
End If
Next tbl
rsMetadb.Close

Tony,

Basically the code just looks at all tables and collects the Primary Key
indexes & any other index that is unique. It stores the results in a
table called "SysKeys".

The "MetaDB" and "db" are references to Access databases, but it doesn't
state what they are.


Dim bUpdate As Boolean <-- Changed
Dim ref As String
Dim i As Integer

'
' Our results will go into a table called SysKeys
' The table exists already
' We'll refer to it by "rsMetadb"
'
Set rsMetadb = MetaDB.OpenRecordset("SysKeys") <-- implies that MetaDB is a database object
<-- and is probably this database
'
' For each table in some database
' What happened to "MetaDB" ???
' db must be THIS database, MetaDB must be SOME OTHER database
'
For Each tbl In db.TableDefs
If Left(tbl.Name, 4) <> "MSys" Then <-- Skip the system tables
'
' Process each index
'
For Each idx In tbl.Indexes
bUpdate = False
rsMetadb.AddNew <-- Add a new record to the SysKeys table
rsMetadb("Tablename") = tbl.Name <-- Add the Table Name
'
' If it is a Primary or unique index, add the index name in the "Keyname" field
' of the SysKeys table
'
If idx.Primary = True Or idx.Unique = True Then rsMetadb("Keyname") = idx.Name
'Check primary key
If idx.Primary = True Then <-- If its a primary key fill out the "Keytype" field
rsMetadb("Keytype") = "PRIMARY"
bUpdate = True
Else
'Check Alternate key <-- If it's unique, but not a PK or FK it's ALTERNATIVE
If idx.Unique = True And idx.Primary = False And idx.Foreign = False Then
rsMetadb("Keytype") = "ALTERNATIVE"
bUpdate = True
End If
End If
If bUpdate Then rsMetadb.Update <-- Only save the entry in the SysKeys table if it was a primary key
<-- index or was just a unique index
Next idx
End If
Next tbl
rsMetadb.Close


Wayne

Many Many thanks for your nice explaination. opss i forgot to paste this last part . could u tell me why we write the result two times? you said the it writes the result to syskeys . as u see why table name is writeen 2 times?
i be happy if u expalin this and this last part of the code.Thanks


rsMetadb.AddNew <-- Add a new record to the SysKeys table
rsMetadb("Tablename") = tbl.Name <-- Add the Table Name

last part



For Each tbl In db.TableDefs
If Left(tbl.Name, 4) <> "MSys" Then
For Each rel In db.Relations
If rel.ForeignTable = tbl.Name Then
For Each fld In rel.Fields
strTablename = rel.ForeignTable
strKeyname = rel.Name
strKeytype = "FOREIGNKEY"
strtablename_prim = rel.Table
MetaDB.Execute "insert into SysKeys values ('" & strTablename & "','" & strKeyname & "','" & strKeytype & "','" & strtablename_prim & "')"
Next fld
End If
Next rel
End If
Next tbl