I am trying to write a utility function to compare two databases and
I've hit a stumbling block. I get two TableDef objects and would like to
examine TableDef.Fields.Count, but it's always zero. In the Object
Browser, I can examine lots of other properties with no problem. (Yes -
the tables have many fields defined.)

Here's a snippet of code I'm using:

Sub CompareOneTable(tblName As String)
    Dim tbl1 As TableDef
    Dim tbl2 As TableDef
    Debug.Print "********************"
    Debug.Print "Comparing table "; tblName
    Set tbl1 = dbMaster.TableDefs(tblName)
    If tbl1 Is Nothing Then
        MsgBox "Unable to retrieve " & tblName & "from Master DB!"
        GoTo Exit_CompareOneTable
    End If

    Set tbl2 = dbCopy.TableDefs(tblName)
    If tbl2 Is Nothing Then
        MsgBox "Unable to retrieve " & tblName & "from Copy DB"
        GoTo Exit_CompareOneTable
    End If
    Call CompareTableFields(tbl1, tbl2)

Exit_CompareOneTable:
    Debug.Print "********************"

    Set tbl1 = Nothing
    Set tbl2 = Nothing
End Sub
---
I have two Database objects, dbMaster and dbCopy, which contain the
objects I want to compare. I have no trouble enumerating the list of
tables in each. If I break on "Call CompareTableFields)", I can view
tbl1 and tbl2 and verify that Fields.Count is zero. The tables are not
linked. Can anyone help? BTW - I have the Access 97 Developer's
Handbook  and the VBA Developer's Handbook - both are excellent - but
they don't seem to offer any help on this one.

Thanks,
        Mark

--
Mark R. Rinfret, Portsmouth, RI - http://www.*-*-*.com/
UNIX & Internet Project Manager, Stanley Works, E. Greenwich, RI

Maybe you can post the CompareTableFields sub??  This part of the code seems
to be fine.  Are you trying it like this?

'********** Code Start **********
Sub aTest()
Dim dbLocal As Database, dbRemote As Database
Dim tdfLocal As TableDef, tdfRemote As TableDef
    Set dbLocal = CurrentDb
    Set dbRemote = DBEngine.OpenDatabase(CurrentDb.Name)
    dbLocal.TableDefs.Refresh
    dbRemote.TableDefs.Refresh
    Debug.Print "Local table count is " & dbLocal.TableDefs.Count
    Debug.Print "Field count in local table "
    For Each tdfLocal In dbLocal.TableDefs
        Debug.Print "    " & tdfLocal.Name & ":" & tdfLocal.Fields.Count
    Next
    Debug.Print "Remote table count is " & dbRemote.TableDefs.Count
    Debug.Print "Field count in Remote table "
    For Each tdfRemote In dbRemote.TableDefs
        Debug.Print "    " & tdfRemote.Name & ":" & tdfRemote.Fields.Count
    Next
    Set tdfLocal = Nothing
    Set tdfRemote = Nothing
    Set dbLocal = Nothing
    Set dbRemote = Nothing
End Sub
'********* Code End ***********

HTH
--
Dev Ashish (Just my $.001)
---------------
The Access Web ( http://home.att.net/~dashish )
---------------

:I am trying to write a utility function to compare two databases and
:I've hit a stumbling block. I get two TableDef objects and would like to
:examine TableDef.Fields.Count, but it's always zero. In the Object
:Browser, I can examine lots of other properties with no problem. (Yes -
:the tables have many fields defined.)
:
:Here's a snippet of code I'm using:
:
:Sub CompareOneTable(tblName As String)
:    Dim tbl1 As TableDef
:    Dim tbl2 As TableDef
:    Debug.Print "********************"
:    Debug.Print "Comparing table "; tblName
:    Set tbl1 = dbMaster.TableDefs(tblName)
:    If tbl1 Is Nothing Then
:        MsgBox "Unable to retrieve " & tblName & "from Master DB!"
:        GoTo Exit_CompareOneTable
:    End If
:
:    Set tbl2 = dbCopy.TableDefs(tblName)
:    If tbl2 Is Nothing Then
:        MsgBox "Unable to retrieve " & tblName & "from Copy DB"
:        GoTo Exit_CompareOneTable
:    End If
:    Call CompareTableFields(tbl1, tbl2)
:
:Exit_CompareOneTable:
:    Debug.Print "********************"
:
:    Set tbl1 = Nothing
:    Set tbl2 = Nothing
:End Sub
:---
:I have two Database objects, dbMaster and dbCopy, which contain the
:objects I want to compare. I have no trouble enumerating the list of
:tables in each. If I break on "Call CompareTableFields)", I can view
:tbl1 and tbl2 and verify that Fields.Count is zero. The tables are not
:linked. Can anyone help? BTW - I have the Access 97 Developer's
:Handbook  and the VBA Developer's Handbook - both are excellent - but
:they don't seem to offer any help on this one.
:
:Thanks,
: Mark
:
:--
:Mark R. Rinfret, Portsmouth, RI - http://www.markrinfret.com/
:UNIX & Internet Project Manager, Stanley Works, E. Greenwich, RI

I found the problem and fixed it, but it was tough. I overlooked and
failed to state the fact that I was attempting to compare two secured
databases (I'm using a non-default workgroup file). After stumbling
around for a while, I discovered that Access was simply refusing to
deliver the Fields collection because I didn't have "Read Design"
permissions. This was the easy part of the problem.

The tough part was discovering that I needed to set the
DBEngine.SystemDB property before running any DAO code. I added the
following line

    DBEngine.SystemDB = "C:\SWDev\MyDB\security.mdw"

to the very beginning of my CompareDatabases procedure, recompiled,
saved and exited Access. Upon restarting and rerunning the code, it ran
perfectly. I now get a valid Fields collection and Fields.Count is
non-zero. Eventually, I'll have to add some logic to determine if the
above is necessary/relevant, but for now I can get on with my project.

Thanks!
        Mark

--
Mark R. Rinfret, Portsmouth, RI - http://www.markrinfret.com/
UNIX & Internet Project Manager, Stanley Works, E. Greenwich, RI