Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Schema Name? (A97 & Axp)

    How do I determine the Schema name of a database? Is it the name of the database?

    Also how do I find the properties of a Schema.

    I searched Access help.

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

    Re: Schema Name? (A97 & Axp)

    Are you trying to work with XML data? I don't think Access 97 offers much in the way of XML support.
    Or do you mean something else?

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Schema Name? (A97 & Axp)

    Access 97 offer NOTHING in the way of XML support. You had to write it all the hard way. A2k was the first version that had anything to do with XML.
    Charlotte

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Schema Name? (A97 & Axp)

    No, I am not trying to work with XML data. I am reading a book on SQL and they refer to the property Schema.
    Evidently DB2, Oracle, MySQL, & SQL server have a Schema property.
    So, I thought that Access probably had a Schema property also.

    Schema refers to the collection of objects in a database.

    For example the following query will list all the tables in a SQL database with a Schema name of 'SMEAGOL'.

    select table_name
    from information_schema.tables
    where table_schema = 'SMEAGOL'

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

    Re: Schema Name? (A97 & Axp)

    The Connection object in ADO has an OpenSchema method. In Access 2000 and higher you could do something like this:

    Public Sub OpenSchemaX()
    Dim cnn1 As ADODB.Connection
    Dim rstSchema As ADODB.Recordset

    Set cnn1 = CurrentProject.Connection
    Set rstSchema = cnn1.OpenSchema(adSchemaTables)

    Do Until rstSchema.EOF
    Debug.Print "Table name: " & _
    rstSchema!TABLE_NAME & vbCr & _
    "Table type: " & rstSchema!TABLE_TYPE & vbCr
    rstSchema.MoveNext
    Loop

    rstSchema.Close
    Set rstSchema = Nothing
    Set cnn1 = Nothing
    End Sub

    This won't work in Access 97 since it has no CurrentProject object.

    BTW Access stores metadata in system tables. You can view them if you tick the check box "System Objects" in the View tab of Tools | Options...
    Info about database objects such as tables can be found in the MSysObjects table. If you search for MSysObjects in this forum, you'll find some examples where this table is used.

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Schema Name? (A97 & Axp)

    Thank you Hans. Your help is appreciated. I will look into the system tables. I forgot about them.

Posting Permissions

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