problem understaing this vba code!

[tony007]

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

 

[WayneRyan]

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

 

[tony007]

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
         [B]   rsMetadb("Tablename") = tbl.Name <-- Add the Table Name[/B]

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
                  [B]   strTablename = rel.ForeignTable[/B]
                     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