Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Object (XP)

    Hi,

    i am trying to build a database that will analyse other database to give broad details such as a count of tables, queries, forms, reports etc. This is in order to provide an estimate on how complex the database may be to convert or amalgamate with others.

    How would i create an object in VB that is the other database(the one being analysed) and then interogate it? I can create an object that is the current database but cant see how how give the object the file path to a target mdb file.

    any thoughts welcomed.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Access Object (XP)

    If you set a reference to the Microsoft DAO 3.6 Object Library in Tools | References... (in the Visual Basic Editor), you can open a database object without opening it in the interface. Here is an example:

    Sub ListForms(strDatabaseName As String)
    Dim dbs As DAO.Database
    Dim doc As DAO.Document
    Set dbs = DBEngine.OpenDatabase(strDatabaseName)
    ' Replace this with your code
    Debug.Print "Number of forms: " & dbs.Containers!Forms.Documents.Count
    For Each doc In dbs.Containers!Forms.Documents
    ' Replace this with your code
    Debug.Print doc.Name
    Next doc
    Set doc = Nothing
    dbs.Close
    Set dbs = Nothing
    End Sub

    Available containers are:

    DataAccessPages
    Databases
    Forms
    Modules
    Relationships
    Reports
    Scripts (=macros)
    SysRel
    Tables

    A Database object also has a TableDefs collection and a QueryDefs collection.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Object (XP)

    Excellent Hans, thanks. why is there a difference between the Tables collection count and the TableDefs count? the first returns 224 and the other 33.

    there are actually 26 tables and 7 system ones (are there actually always 7 system ones? I can count these out if it is constant)

    cheers

    Fatherjack

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Access Object (XP)

    I think the Tables container includes not only the "real" tables, but also all stored queries and queries generated internally by Access (record sources for forms and reports, row sources for list boxes, combo boxes etc.). So you'd better use the TableDefs collection.

    The name of system tables starts with MSys. Their number can vary - I have 6 of them in one database I checked, and 8 in another, so a test on the first 4 characters of the name is better.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,613
    Thanks
    3
    Thanked 58 Times in 58 Posts

    Re: Access Object (XP)

    You might also want to look at the Access 2003 Conversion Toolkit - it also works with Access 2002. It does a bit more in-depth analysis, looking for some basic issues of compatibility and old Access Basic constructs. Unfortunately, it's a rather huge (53MB) download - but if you have lots of databases to convert, it is quite useful.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •