TableDef.Fields.Count is always zero - why? 
Author Message
 TableDef.Fields.Count is always zero - why?

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



Wed, 31 Jan 2001 03:00:00 GMT  
 TableDef.Fields.Count is always zero - why?
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



Thu, 01 Feb 2001 03:00:00 GMT  
 TableDef.Fields.Count is always zero - why?

Quote:

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

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



Thu, 01 Feb 2001 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. TableDef/Set all text fields to allow zero length

2. Problem with TableDef.Fields.Count property. Please Help

3. Problem with TableDef.Fields.Count property. Please Help

4. DisplayType of Dist. List members is always zero

5. Microsoft Outlook Express 5 and Netscape, the AttachmentCount property always returns zero

6. GetCursorPos -- y coordinate always zero

7. INTEGER TO TEXT CONVERSION ALWAYS ADDS ZEROS

8. Howto always show two desimals even when last is zero

9. GetCursorPos -- y coordinate always zero

10. COM-Variable returns always Zero instead NULL

11. Count Zero records as 0

12. Count without zeros

 

 
Powered by phpBB® Forum Software