DB Object Definitions

[Peter Hibbs]

Access 2000 - A2007.

I am trying to write a general purpose function which will return a
list of database objects in a string. The function definition looks
something like this :-

Public Function FetchObjectList(vType As Long) As String

The vType variable would hold a value to define which type of object I
need returning. For example -

Value 1 would return a list of local table names (i.e. tables in FE
file) but excluding any System tables.
Value 2 would return a list of linked tables (i.e. tables in BE file)
but excluding any System tables.
Value 3 would return all action queries (i.e. Make-Table, Append,
Update & Delete).
Value 4 would return a list of Macros.

Actually the value of vType is not important, any value will do if it
makes any difference to the code.

The list should be returned as a ; delimited string, i.e.
tblClients;tblContacts;tblNotes or qryClients;qryContacts etc.

I have found code to iterate through the list of tables and queries
but I am having some trouble in identifying the various types of
tables, queries, etc. Does anyone have any code to do this.

Peter Hibbs.

 

[Cory Dove]

Access 2000 - A2007.

I am trying to write a general purpose function which will return a
list of database objects in a string. The function definition looks
something like this :-

Public Function FetchObjectList(vType As Long) As String

The vType variable would hold a value to define which type of object I
need returning. For example -

Value 1 would return a list of local table names (i.e. tables in FE
file) but excluding any System tables.
Value 2 would return a list of linked tables (i.e. tables in BE file)
but excluding any System tables.
Value 3 would return all action queries (i.e. Make-Table, Append,
Update & Delete).
Value 4 would return a list of Macros.

Actually the value of vType is not important, any value will do if it
makes any difference to the code.

The list should be returned as a ; delimited string, i.e.
tblClients;tblContacts;tblNotes or qryClients;qryContacts etc.

I have found code to iterate through the list of tables and queries
but I am having some trouble in identifying the various types of
tables, queries, etc. Does anyone have any code to do this.

Peter Hibbs.

 

[Dirk Goldgar]

Access 2000 - A2007.

I am trying to write a general purpose function which will return a
list of database objects in a string. The function definition looks
something like this :-

Public Function FetchObjectList(vType As Long) As String

The vType variable would hold a value to define which type of object I
need returning. For example -

Value 1 would return a list of local table names (i.e. tables in FE
file) but excluding any System tables.
Value 2 would return a list of linked tables (i.e. tables in BE file)
but excluding any System tables.
Value 3 would return all action queries (i.e. Make-Table, Append,
Update & Delete).
Value 4 would return a list of Macros.

Actually the value of vType is not important, any value will do if it
makes any difference to the code.

The list should be returned as a ; delimited string, i.e.
tblClients;tblContacts;tblNotes or qryClients;qryContacts etc.

I have found code to iterate through the list of tables and queries
but I am having some trouble in identifying the various types of
tables, queries, etc. Does anyone have any code to do this.

With DAO, you can interpret the .Type property of the QueryDef object to see
what kind of query it represents. There's a set of defined constants under
the QueryDefType enumerated type (which you can find in the Object Browser)
that you can use as bit masks to test a querydef's Type property to see what
kind of querydef it is.

For example, the dbQAction constant has hex value F0, so

If (myqdf.Type And dbQAction) <> 0 Then

' This is not a simple select query. HOWEVER, there are some
' types of queries that such as union, crosstab and SQL pass-through
' queries, that fall into this category, and must be further
differentiated.

End If

I think I've seen some code posted somewhere that does all this type
checking -- try a web search for "QueryDef Type" or something like that.

Note, by the way, that Access will have created hidden querydefs, with names
beginning "~sq", to represent in-line SQL statements used as recordsources
and rowsources. You will probably want to exclude these from your list.

To distinguish linked tables, you can check the .Connect property of the
TableDef object. If it's not empty (""), then the tabledef refers to a
linked table. To exclude system tables, you can check the first 4
characters of the tabledef's name -- if the name begins with "MSys", it's an
Access system table.

 

[Peter Hibbs]

Dirk,

Thanks for the info, I will follow it up tomorrow.

Peter.