Could anyone offer some help for DAO in Excel?

  • Thread starter Thread starter Mike Mertes
  • Start date Start date
M

Mike Mertes

I was wondering if someone could give me some information about programming
for Microsoft Data Access Objects. Namely, I've noticed that when I open a
database via 'DBEngine.Workspaces(0).OpenDatabase(Path)', if I enumerate the
tables that are automatically appended to the TableDefs collection (For Each
TblDef in Database.TableDefs : debug.print TblDef.Name : Next TblDef) I get
unexpected results. '?TableDef(0).Name' has an expected result: The name of
the first table in my MSAccess database. Also in accordance with
expectation, the results for ?TableDef(1, 2, 3, and 4) follow suit. Here is
the unusual part: Naturally I assume the next Table in the TableDefs
collection will be the next successive table in my database, but it's not.
Instead I get these Tables:

TableDef(5).Name = MSysAccessObjects
TableDef(6).Name = MSysACEs
TableDef(7).Name = MSysObjects
TableDef(8).Name = MSysQueries
TableDef(9).Name = MSysRelationships

follwed by TableDef(10 & 11), the last two tables in my database.

Why are these [access object collections?] represented as Tables in the
TableDefs collection, and why do they appear right in the middle of the list
(effectively splitting it into halves, and being an annoyance!) ...and, are
there any properties that dictate this behavior?

Thanks again,
-Mike :D
 
Mike

Those are system tables that Access uses to store data about your queries,
etc. Go into access and Tools - Options and check View System Objects (or
something like that) and you'll see them. If you open up MSysQueries, for
example, you can get an idea of what is being stored.

To eliminate them from your enumaration, check the Attributes property:

For Each td In db.TableDefs
If td.Attributes = 0 Then
Debug.Print td.Name
End If
Next td
 
Ah! Thank you! :)

I can also see now that the reason they are listed as such is because all
those system tables start with the letter 'M,' and the tables are added to
the TableDefs collection alphabetically. (As I have them listed in Access.)

Dick Kusleika said:
Mike

Those are system tables that Access uses to store data about your queries,
etc. Go into access and Tools - Options and check View System Objects (or
something like that) and you'll see them. If you open up MSysQueries, for
example, you can get an idea of what is being stored.

To eliminate them from your enumaration, check the Attributes property:

For Each td In db.TableDefs
If td.Attributes = 0 Then
Debug.Print td.Name
End If
Next td

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

Mike Mertes said:
I was wondering if someone could give me some information about programming
for Microsoft Data Access Objects. Namely, I've noticed that when I open a
database via 'DBEngine.Workspaces(0).OpenDatabase(Path)', if I enumerate the
tables that are automatically appended to the TableDefs collection (For Each
TblDef in Database.TableDefs : debug.print TblDef.Name : Next TblDef) I get
unexpected results. '?TableDef(0).Name' has an expected result: The name of
the first table in my MSAccess database. Also in accordance with
expectation, the results for ?TableDef(1, 2, 3, and 4) follow suit. Here is
the unusual part: Naturally I assume the next Table in the TableDefs
collection will be the next successive table in my database, but it's not.
Instead I get these Tables:

TableDef(5).Name = MSysAccessObjects
TableDef(6).Name = MSysACEs
TableDef(7).Name = MSysObjects
TableDef(8).Name = MSysQueries
TableDef(9).Name = MSysRelationships

follwed by TableDef(10 & 11), the last two tables in my database.

Why are these [access object collections?] represented as Tables in the
TableDefs collection, and why do they appear right in the middle of the list
(effectively splitting it into halves, and being an annoyance!) ...and, are
there any properties that dictate this behavior?

Thanks again,
-Mike :D
Click to expand...
Click to expand...
 
Mike

Well I'm glad I didn't give you my theory (on the order). I figured they
were added sequentially, that is, you make 3 tables, then a query, then 1
more table, it would look like this

Table1
Table2
Table3
MSysQueries
Table4

Of course now that you told me how it is, that theory is just stupid.

There are also system tables that start with USys, although I don't know how
or why those get created. Just be on the look out for them.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

Mike Mertes said:
Ah! Thank you! :)

I can also see now that the reason they are listed as such is because all
those system tables start with the letter 'M,' and the tables are added to
the TableDefs collection alphabetically. (As I have them listed in Access.)

Dick Kusleika said:
Mike

Those are system tables that Access uses to store data about your queries,
etc. Go into access and Tools - Options and check View System Objects (or
something like that) and you'll see them. If you open up MSysQueries, for
example, you can get an idea of what is being stored.

To eliminate them from your enumaration, check the Attributes property:

For Each td In db.TableDefs
If td.Attributes = 0 Then
Debug.Print td.Name
End If
Next td

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.
Click to expand...
open
a
database via 'DBEngine.Workspaces(0).OpenDatabase(Path)', if I
Click to expand...
enumerate
the
tables that are automatically appended to the TableDefs collection
Click to expand...
(For
Each
TblDef in Database.TableDefs : debug.print TblDef.Name : Next TblDef)
Click to expand...
I
get
unexpected results. '?TableDef(0).Name' has an expected result: The
Click to expand...
name
of
the first table in my MSAccess database. Also in accordance with
expectation, the results for ?TableDef(1, 2, 3, and 4) follow suit.
Click to expand...
Here
is
the unusual part: Naturally I assume the next Table in the TableDefs
collection will be the next successive table in my database, but it's not.
Instead I get these Tables:

TableDef(5).Name = MSysAccessObjects
TableDef(6).Name = MSysACEs
TableDef(7).Name = MSysObjects
TableDef(8).Name = MSysQueries
TableDef(9).Name = MSysRelationships

follwed by TableDef(10 & 11), the last two tables in my database.

Why are these [access object collections?] represented as Tables in the
TableDefs collection, and why do they appear right in the middle of
Click to expand...
the
list
(effectively splitting it into halves, and being an annoyance!)
Click to expand...
....and,
are
there any properties that dictate this behavior?

Thanks again,
-Mike :D
Click to expand...
Click to expand...
Click to expand...
 
Back
Top