Schema Name? (A97 & Axp)

Topic

New Reply

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.

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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’

Reply | Quote

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.

Reply | Quote

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

Reply | Quote