How to access the metadata

[Benjamin Qin]

Hi there!

We know that we can get metadata in SQL Server by query "select * from
INFORMATION_SCHEMA.X", where X can be TABLES/VIEWS/COLUMNS.....

How can we do this kind things in MS Access? I mean the programming way.

Thank you!

 

[Arvin Meyer]

You can use DAO, and/or SQL similar to SQL-SVR:

Select * From MSysObjects;

Public Sub GetFieldNames(tblName As String)
'********************************************************************
' Name: GetFieldNames
' Purpose: Fill table with field names
'
' Inputs: tblName As String
'
' Author: Arvin Meyer
' Date: 6/19/1997
' Comment:
'
'********************************************************************
On Error Resume Next
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim flds As DAO.Fields
Dim fld As DAO.Field

Set db = CurrentDb
db.TableDefs.Delete ("tblFieldNames")
Set tdf = db.CreateTableDef("tblFieldNames")
With tdf
.Fields.Append .CreateField("FieldName", dbText)
End With
db.TableDefs.Append tdf

Set flds = db.TableDefs(tblName).Fields
Set rst = db.OpenRecordset("tblFieldNames")
For Each fld In flds
rst.AddNew
rst!FieldName = fld.Name
rst.Update
Next

Exit_GetFieldNames:
rst.Close
Set tdf = Nothing
Set flds = Nothing
Set rst = Nothing
Set fld = Nothing
Set db = Nothing
Exit Sub

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Benjamin Qin]

Thank you Arvin, I think it's good.

You can use DAO, and/or SQL similar to SQL-SVR:

Select * From MSysObjects;

Public Sub GetFieldNames(tblName As String)
'********************************************************************
' Name: GetFieldNames
' Purpose: Fill table with field names
'
' Inputs: tblName As String
'
' Author: Arvin Meyer
' Date: 6/19/1997
' Comment:
'
'********************************************************************
On Error Resume Next
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim flds As DAO.Fields
Dim fld As DAO.Field

Set db = CurrentDb
db.TableDefs.Delete ("tblFieldNames")
Set tdf = db.CreateTableDef("tblFieldNames")
With tdf
.Fields.Append .CreateField("FieldName", dbText)
End With
db.TableDefs.Append tdf

Set flds = db.TableDefs(tblName).Fields
Set rst = db.OpenRecordset("tblFieldNames")
For Each fld In flds
rst.AddNew
rst!FieldName = fld.Name
rst.Update
Next

Exit_GetFieldNames:
rst.Close
Set tdf = Nothing
Set flds = Nothing
Set rst = Nothing
Set fld = Nothing
Set db = Nothing
Exit Sub

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access