Solved Getting the count of tables in an external database

[KitaYama]

How can I get the count of user defined tables in an external database?

    Dim dbs As DAO.Database
    Dim rs As DAO.Recordset
    Dim sql as string

    Set dbs = OpenDatabase(PathToMyTarget)
    Debug.Print dbs.Name
    Debug.Print dbs.QueryDefs.Count
    Debug.Print dbs.TableDefs.Count

    sql = "SELECT * FROM MSysObjects WHERE MSysObjects.Type=6 AND Left([Name],1)<>'~'"
    Set rs = dbs.OpenRecordset(sql)

The above gives me the following error on
Set rs= ......

thanks

 

[The_Doc_Man]

It seems from that error that the database was secured by password? Did it let you do the Debug.Print for the dbs.xxxDefs.Count statements? If so, perhaps you could try a "FOR EACH tblvariable IN dbs.TableDefs" loop. Slower, but might work OK.

 

[KitaYama]

Database is not password protected. Debug shows correct result.

I have a loop through TableDef that works perfect. Before going through the loop I just wanted to have a count to show it in a progressbar.

Thanks for trying to help.

 

[isladogs]

One method I often use is to link to the external MSysObjects table then do a DCount of all objects with Type1, 4 or 6.
Filter to exclude system tables if required.
That approach is used in several of my apps including

Table Analyzer & Viewer

The professional version of this utility allows you to quickly analyze the tables and their fields in any Access database. For each table, the table type, number of fields and records are displayed. You can also determine the size of each table, view its data and the table design. In addition...

www.isladogs.co.uk

 

[Josef P.]

Find the difference:

   Dim dbs As DAO.Database
   Dim rs As DAO.Recordset
   Dim sql as string
   Set dbs = DBEngine.OpenDatabase(PathToMyTarget)

   Debug.Print dbs.Name
   Debug.Print dbs.QueryDefs.Count
   Debug.Print dbs.TableDefs.Count

   sql = "SELECT * FROM MSysObjects WHERE MSysObjects.Type=6 AND Left([Name],1)<>'~'"
   Set rs = dbs.OpenRecordset(sql)

 

[KitaYama]

Find the difference:

   Dim dbs As DAO.Database
   Dim rs As DAO.Recordset
   Dim sql as string
   Set dbs = DBEngine.OpenDatabase(PathToMyTarget)

   Debug.Print dbs.Name
   Debug.Print dbs.QueryDefs.Count
   Debug.Print dbs.TableDefs.Count

   sql = "SELECT * FROM MSysObjects WHERE MSysObjects.Type=6 AND Left([Name],1)<>'~'"
   Set rs = dbs.OpenRecordset(sql)

While I still don't understand why my method didn't work, but as long as I have a working method, I'm satisfied.

Million thanks.

 

[Josef P.]

It will also work like this:

   Static DbEngineCalled As Boolean
  
   Dim x As Variant
   Dim dbs As DAO.Database
   Dim rs As DAO.Recordset
   Dim sql As String
  
   If Not DbEngineCalled Then
      DbEngineCalled = True
      x = Application.DBEngine.Version
   End If
  
   Set dbs = OpenDatabase(PathToMyTarget)

   Debug.Print dbs.Name
   Debug.Print dbs.QueryDefs.Count
   Debug.Print dbs.TableDefs.Count

   sql = "SELECT * FROM MSysObjects WHERE MSysObjects.Type=6 AND Left([Name],1)<>'~'"
   Set rs = dbs.OpenRecordset(sql)