discovering table and field names

[MarkB]

How can I get a list of the names of tables (and queries too, if possible)
in an access db? In Oracle I'd do something like "select * from tab" to
query the db catalog. If I use DAO, I can iterate over the db.Tables
collection, but I don't see how to do this as a query in Access.

I'm writing a small applet in .NET, and would like to use ADO.NET to get a
list of tables. Offer the list to the user, where they can select the
desired tables. Then I want to get the field names of the selected tables,
as well as the relationships between the selected tables.

How does one achieve this against Access?

Thanks

 

[Allen Browne]

You can read these objects from the MSysObjects table.

List of tables:
SELECT MsysObjects.Name FROM MsysObjects
WHERE (([Type] = 1) AND ([Name] Not Like "~*") AND ([Name] Not Like
"MSys*"))
ORDER BY MsysObjects.Name;

Include type 6 if you want linked tables, 4 for ODBC linked tables, and 5
for queries.

You can probably get a list of relations by querying the MSysRelationships
table. The grbit field indicates the attributes of the relation - bitfield
values that are members of RelationAttributeEnum.

 

[MarkB]

Thanks Allen, that's very helpful.

I see I can get a list of table and query names from MsysObjects and
relationships from (of all things MsysRelationships. However, I've
looked through all the other system tables (Msys*), but I still don't see
how I can get the a list of field names (and possibly data types?) for a
given table. Any pointers on that?

TIA ...

You can read these objects from the MSysObjects table.

List of tables:
SELECT MsysObjects.Name FROM MsysObjects
WHERE (([Type] = 1) AND ([Name] Not Like "~*") AND ([Name] Not Like
"MSys*"))
ORDER BY MsysObjects.Name;

Include type 6 if you want linked tables, 4 for ODBC linked tables, and 5
for queries.

You can probably get a list of relations by querying the MSysRelationships
table. The grbit field indicates the attributes of the relation - bitfield
values that are members of RelationAttributeEnum.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How can I get a list of the names of tables (and queries too, if
possible) in an access db? In Oracle I'd do something like "select * from
tab" to query the db catalog. If I use DAO, I can iterate over the
db.Tables collection, but I don't see how to do this as a query in
Access.

I'm writing a small applet in .NET, and would like to use ADO.NET to get
a list of tables. Offer the list to the user, where they can select the
desired tables. Then I want to get the field names of the selected
tables, as well as the relationships between the selected tables.

How does one achieve this against Access?

 

[Allen Browne]

Not sure you can get the field names directly from the system tables.

Can you add a reference to the Microsoft DAO library? If so you can iterate
the Fields of the TableDef to get their Name, Type, and other properties.
Sample code:
http://allenbrowne.com/func-06.html

You could also open a recordset based on "SELECT * FROM MyTable;", and then
examine the Fields of the Recordset.

If you are not familar with DAO (the native Access library for doing this
kind of thing), the basic object model is diagrammed here:
http://allenbrowne.com/ser-04.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen, that's very helpful.

I see I can get a list of table and query names from MsysObjects and
relationships from (of all things MsysRelationships. However, I've
looked through all the other system tables (Msys*), but I still don't see
how I can get the a list of field names (and possibly data types?) for a
given table. Any pointers on that?

TIA ...

You can read these objects from the MSysObjects table.

List of tables:
SELECT MsysObjects.Name FROM MsysObjects
WHERE (([Type] = 1) AND ([Name] Not Like "~*") AND ([Name] Not Like
"MSys*"))
ORDER BY MsysObjects.Name;

Include type 6 if you want linked tables, 4 for ODBC linked tables, and 5
for queries.

You can probably get a list of relations by querying the
MSysRelationships table. The grbit field indicates the attributes of the
relation - bitfield values that are members of RelationAttributeEnum.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How can I get a list of the names of tables (and queries too, if
possible) in an access db? In Oracle I'd do something like "select *
from tab" to query the db catalog. If I use DAO, I can iterate over the
db.Tables collection, but I don't see how to do this as a query in
Access.

I'm writing a small applet in .NET, and would like to use ADO.NET to get
a list of tables. Offer the list to the user, where they can select the
desired tables. Then I want to get the field names of the selected
tables, as well as the relationships between the selected tables.

How does one achieve this against Access?

 

[Jeff Conrad]

You can use my CSD Tools Add-in to get a nice report like this:

http://home.bendbroadband.com/conradsystems/accessjunkie/csdtools.html

That is Version 1.0
Version 2.0 set to be released soon.
--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
http://www.access.qbuilt.com/html/articles.html

in message:

 

[MarkB]

Yup, I've done this via DAO before, but that is supposed to "yesterday's
data access technology" - and my DAO code looks very much like yours

I was hoping I could get table names, and field names and types via SQL
(like Oracle and SQLServer).

I thought that this sort of stuff is part of the SQL2 standard (circa 1998
or some such). I'm surprised Access would not support it.

I am building a graphic query front end to a system and, well, I guess I'll
just keep experimenting ...

Anyway, thanks very much for your helpful comments and insight.

Mark

Not sure you can get the field names directly from the system tables.

Can you add a reference to the Microsoft DAO library? If so you can
iterate the Fields of the TableDef to get their Name, Type, and other
properties. Sample code:
http://allenbrowne.com/func-06.html

You could also open a recordset based on "SELECT * FROM MyTable;", and
then examine the Fields of the Recordset.

If you are not familar with DAO (the native Access library for doing this
kind of thing), the basic object model is diagrammed here:
http://allenbrowne.com/ser-04.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen, that's very helpful.

I see I can get a list of table and query names from MsysObjects and
relationships from (of all things MsysRelationships. However, I've
looked through all the other system tables (Msys*), but I still don't see
how I can get the a list of field names (and possibly data types?) for a
given table. Any pointers on that?

TIA ...

You can read these objects from the MSysObjects table.

List of tables:
SELECT MsysObjects.Name FROM MsysObjects
WHERE (([Type] = 1) AND ([Name] Not Like "~*") AND ([Name] Not Like
"MSys*"))
ORDER BY MsysObjects.Name;

Include type 6 if you want linked tables, 4 for ODBC linked tables, and
5 for queries.

You can probably get a list of relations by querying the
MSysRelationships table. The grbit field indicates the attributes of the
relation - bitfield values that are members of RelationAttributeEnum.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How can I get a list of the names of tables (and queries too, if
possible) in an access db? In Oracle I'd do something like "select *
from tab" to query the db catalog. If I use DAO, I can iterate over
the db.Tables collection, but I don't see how to do this as a query in
Access.

I'm writing a small applet in .NET, and would like to use ADO.NET to
get a list of tables. Offer the list to the user, where they can select
the desired tables. Then I want to get the field names of the selected
tables, as well as the relationships between the selected tables.

How does one achieve this against Access?

 

[MarkB]

Thanks Jeff, but I have to do this programmatically ...

 

[Allen Browne]

When MS introduced A2000, they did try to persuade users to go for ADO
rather than DAO, so that's probably the hype you heard.

What they did not tell you was that Access itself still uses DAO. For
example, if you try to run an ADO-specific query from the Query design
window (SQL View), it fails because Access is using DAO. It always made best
sense to me to use the native Access library.

So, 5 years later, DAO is very much alive and kicking, and the DAO 3.6
reference is back by default in Access 2003, while ADO is effectively dead
(replaced by the very different ADO.NET.)

We know from Erik Rudder's blog at http://blogs.msdn.com/access/ that the
next version of Access will have its own private version of the JET engine.
I expect, therefore, that the object model will be DAO, or an extension
thereof.

So you won't get stuck using DAO.

The OpenRecordset thing is an alternative way to get the field info:

Function ShowFieldsRS(strTable)
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim strSql As String

strSql = "SELECT " & strTable & ".* FROM " & strTable & " WHERE
(False);"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
For Each fld In rs.Fields
Debug.Print fld.Name, FieldTypeName(fld.Type)
Next
rs.Close
Set rs = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yup, I've done this via DAO before, but that is supposed to "yesterday's
data access technology" - and my DAO code looks very much like yours

I was hoping I could get table names, and field names and types via SQL
(like Oracle and SQLServer).

I thought that this sort of stuff is part of the SQL2 standard (circa 1998
or some such). I'm surprised Access would not support it.

I am building a graphic query front end to a system and, well, I guess
I'll just keep experimenting ...

Anyway, thanks very much for your helpful comments and insight.

Mark

Not sure you can get the field names directly from the system tables.

Can you add a reference to the Microsoft DAO library? If so you can
iterate the Fields of the TableDef to get their Name, Type, and other
properties. Sample code:
http://allenbrowne.com/func-06.html

You could also open a recordset based on "SELECT * FROM MyTable;", and
then examine the Fields of the Recordset.

If you are not familar with DAO (the native Access library for doing this
kind of thing), the basic object model is diagrammed here:
http://allenbrowne.com/ser-04.html

Thanks Allen, that's very helpful.

I see I can get a list of table and query names from MsysObjects and
relationships from (of all things MsysRelationships. However, I've
looked through all the other system tables (Msys*), but I still don't
see how I can get the a list of field names (and possibly data types?)
for a given table. Any pointers on that?

TIA ...

You can read these objects from the MSysObjects table.

List of tables:
SELECT MsysObjects.Name FROM MsysObjects
WHERE (([Type] = 1) AND ([Name] Not Like "~*") AND ([Name] Not Like
"MSys*"))
ORDER BY MsysObjects.Name;

Include type 6 if you want linked tables, 4 for ODBC linked tables, and
5 for queries.

You can probably get a list of relations by querying the
MSysRelationships table. The grbit field indicates the attributes of
the relation - bitfield values that are members of
RelationAttributeEnum.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How can I get a list of the names of tables (and queries too, if
possible) in an access db? In Oracle I'd do something like "select *
from tab" to query the db catalog. If I use DAO, I can iterate over
the db.Tables collection, but I don't see how to do this as a query in
Access.

I'm writing a small applet in .NET, and would like to use ADO.NET to
get a list of tables. Offer the list to the user, where they can
select the desired tables. Then I want to get the field names of the
selected tables, as well as the relationships between the selected
tables.

How does one achieve this against Access?

 

[MarkB]

My graphic query front end is in .NET, so I am using ADO.NET, which is
indeed very different, but allows me to use SQL readily. Perhaps I'll just
make an exception to use DAO (AKA "Jet") if the data source selected is
Access.

Thanks again for your help.

When MS introduced A2000, they did try to persuade users to go for ADO
rather than DAO, so that's probably the hype you heard.

What they did not tell you was that Access itself still uses DAO. For
example, if you try to run an ADO-specific query from the Query design
window (SQL View), it fails because Access is using DAO. It always made
best sense to me to use the native Access library.

So, 5 years later, DAO is very much alive and kicking, and the DAO 3.6
reference is back by default in Access 2003, while ADO is effectively dead
(replaced by the very different ADO.NET.)

We know from Erik Rudder's blog at http://blogs.msdn.com/access/ that the
next version of Access will have its own private version of the JET
engine. I expect, therefore, that the object model will be DAO, or an
extension thereof.

So you won't get stuck using DAO.

The OpenRecordset thing is an alternative way to get the field info:

Function ShowFieldsRS(strTable)
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim strSql As String

strSql = "SELECT " & strTable & ".* FROM " & strTable & " WHERE
(False);"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
For Each fld In rs.Fields
Debug.Print fld.Name, FieldTypeName(fld.Type)
Next
rs.Close
Set rs = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yup, I've done this via DAO before, but that is supposed to "yesterday's
data access technology" - and my DAO code looks very much like yours

I was hoping I could get table names, and field names and types via SQL
(like Oracle and SQLServer).

I thought that this sort of stuff is part of the SQL2 standard (circa
1998 or some such). I'm surprised Access would not support it.

I am building a graphic query front end to a system and, well, I guess
I'll just keep experimenting ...

Anyway, thanks very much for your helpful comments and insight.

Mark

Not sure you can get the field names directly from the system tables.

Can you add a reference to the Microsoft DAO library? If so you can
iterate the Fields of the TableDef to get their Name, Type, and other
properties. Sample code:
http://allenbrowne.com/func-06.html

You could also open a recordset based on "SELECT * FROM MyTable;", and
then examine the Fields of the Recordset.

If you are not familar with DAO (the native Access library for doing
this kind of thing), the basic object model is diagrammed here:
http://allenbrowne.com/ser-04.html


Thanks Allen, that's very helpful.

I see I can get a list of table and query names from MsysObjects and
relationships from (of all things MsysRelationships. However, I've
looked through all the other system tables (Msys*), but I still don't
see how I can get the a list of field names (and possibly data types?)
for a given table. Any pointers on that?

TIA ...

You can read these objects from the MSysObjects table.

List of tables:
SELECT MsysObjects.Name FROM MsysObjects
WHERE (([Type] = 1) AND ([Name] Not Like "~*") AND ([Name] Not Like
"MSys*"))
ORDER BY MsysObjects.Name;

Include type 6 if you want linked tables, 4 for ODBC linked tables,
and 5 for queries.

You can probably get a list of relations by querying the
MSysRelationships table. The grbit field indicates the attributes of
the relation - bitfield values that are members of
RelationAttributeEnum.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How can I get a list of the names of tables (and queries too, if
possible) in an access db? In Oracle I'd do something like "select *
from tab" to query the db catalog. If I use DAO, I can iterate over
the db.Tables collection, but I don't see how to do this as a query
in Access.

I'm writing a small applet in .NET, and would like to use ADO.NET to
get a list of tables. Offer the list to the user, where they can
select the desired tables. Then I want to get the field names of the
selected tables, as well as the relationships between the selected
tables.

How does one achieve this against Access?

 

[Brendan Reynolds]

Here's something I posted a while back in response to a similar question ...

One way to get this information in a .NET app would be to use the FillSchema
method of the DataSet object ...

using System;
using System.Data;
using System.Data.OleDb;
namespace ConsoleApplication16
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;"
+ @"Data Source=c:\dsdata\northwind.mdb;"
+ @"Persist Security Info=False";
OleDbConnection connection = new System.Data.OleDb
.OleDbConnection(connectionString);
OleDbCommand selectCommand = new System.Data.OleDb
.OleDbCommand("SELECT * FROM Categories", connection);
OleDbDataAdapter dataAdapter = new System.Data.OleDb
.OleDbDataAdapter(selectCommand);
DataSet dataSet = new System.Data.DataSet();
dataAdapter.FillSchema(dataSet,System.Data.SchemaType.Source);
DataColumnCollection columns = dataSet.Tables[0].Columns;
foreach (System.Data.DataColumn dataColumn in columns)
{
Console.WriteLine(dataColumn.ColumnName);
}
Console.ReadLine();
}
}
}

--
Brendan Reynolds

My graphic query front end is in .NET, so I am using ADO.NET, which is
indeed very different, but allows me to use SQL readily. Perhaps I'll just
make an exception to use DAO (AKA "Jet") if the data source selected is
Access.

Thanks again for your help.

When MS introduced A2000, they did try to persuade users to go for ADO
rather than DAO, so that's probably the hype you heard.

What they did not tell you was that Access itself still uses DAO. For
example, if you try to run an ADO-specific query from the Query design
window (SQL View), it fails because Access is using DAO. It always made
best sense to me to use the native Access library.

So, 5 years later, DAO is very much alive and kicking, and the DAO 3.6
reference is back by default in Access 2003, while ADO is effectively
dead (replaced by the very different ADO.NET.)

We know from Erik Rudder's blog at http://blogs.msdn.com/access/ that the
next version of Access will have its own private version of the JET
engine. I expect, therefore, that the object model will be DAO, or an
extension thereof.

So you won't get stuck using DAO.

The OpenRecordset thing is an alternative way to get the field info:

Function ShowFieldsRS(strTable)
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim strSql As String

strSql = "SELECT " & strTable & ".* FROM " & strTable & " WHERE
(False);"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
For Each fld In rs.Fields
Debug.Print fld.Name, FieldTypeName(fld.Type)
Next
rs.Close
Set rs = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yup, I've done this via DAO before, but that is supposed to "yesterday's
data access technology" - and my DAO code looks very much like yours

I was hoping I could get table names, and field names and types via SQL
(like Oracle and SQLServer).

I thought that this sort of stuff is part of the SQL2 standard (circa
1998 or some such). I'm surprised Access would not support it.

I am building a graphic query front end to a system and, well, I guess
I'll just keep experimenting ...

Anyway, thanks very much for your helpful comments and insight.

Mark

Not sure you can get the field names directly from the system tables.

Can you add a reference to the Microsoft DAO library? If so you can
iterate the Fields of the TableDef to get their Name, Type, and other
properties. Sample code:
http://allenbrowne.com/func-06.html

You could also open a recordset based on "SELECT * FROM MyTable;", and
then examine the Fields of the Recordset.

If you are not familar with DAO (the native Access library for doing
this kind of thing), the basic object model is diagrammed here:
http://allenbrowne.com/ser-04.html


Thanks Allen, that's very helpful.

I see I can get a list of table and query names from MsysObjects and
relationships from (of all things MsysRelationships. However, I've
looked through all the other system tables (Msys*), but I still don't
see how I can get the a list of field names (and possibly data types?)
for a given table. Any pointers on that?

TIA ...

You can read these objects from the MSysObjects table.

List of tables:
SELECT MsysObjects.Name FROM MsysObjects
WHERE (([Type] = 1) AND ([Name] Not Like "~*") AND ([Name] Not Like
"MSys*"))
ORDER BY MsysObjects.Name;

Include type 6 if you want linked tables, 4 for ODBC linked tables,
and 5 for queries.

You can probably get a list of relations by querying the
MSysRelationships table. The grbit field indicates the attributes of
the relation - bitfield values that are members of
RelationAttributeEnum.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How can I get a list of the names of tables (and queries too, if
possible) in an access db? In Oracle I'd do something like "select *
from tab" to query the db catalog. If I use DAO, I can iterate over
the db.Tables collection, but I don't see how to do this as a query
in Access.

I'm writing a small applet in .NET, and would like to use ADO.NET to
get a list of tables. Offer the list to the user, where they can
select the desired tables. Then I want to get the field names of the
selected tables, as well as the relationships between the selected
tables.

How does one achieve this against Access?

 

[MarkB]

Interestingly, when I issue:

SELECT Name FROM MsysObjects WHERE ([Type] = 1) AND ([Name] Not Like "~*")
AND ([Name] Not Like "MSys*") ORDER BY Name;

or even
SELECT * FROM MsysObjects

in an ODBC connection for ADO.NET against the NorthWindTraders.mdb, I get a
priv error:
ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be
read; no read permission on 'msysobjects'.

But
select * from msysrelationships
and
select * from customers

both work OK.

Here's my ODBC connection string:
PageTimeout=5;FIL=MS
Access;MaxScanRows=8;DefaultDir=R:\Access-DBs;FILEDSN=C:\Program
Files\Common Files\ODBC\Data
Sources\Access-MBB.dsn;DriverId=25;DBQ=R:\Access-DBs\Northwind.mdb;UserCommitSync=Yes;Exclusive=0;UID=admin;Driver={Driver
do Microsoft Access
(*.mdb)};MaxBufferSize=2048;Threads=3;SafeTransactions=0;ReadOnly=1


What am I missing? What do I need have to do to query msysobjects?

When MS introduced A2000, they did try to persuade users to go for ADO
rather than DAO, so that's probably the hype you heard.

What they did not tell you was that Access itself still uses DAO. For
example, if you try to run an ADO-specific query from the Query design
window (SQL View), it fails because Access is using DAO. It always made
best sense to me to use the native Access library.

So, 5 years later, DAO is very much alive and kicking, and the DAO 3.6
reference is back by default in Access 2003, while ADO is effectively dead
(replaced by the very different ADO.NET.)

We know from Erik Rudder's blog at http://blogs.msdn.com/access/ that the
next version of Access will have its own private version of the JET
engine. I expect, therefore, that the object model will be DAO, or an
extension thereof.

So you won't get stuck using DAO.

The OpenRecordset thing is an alternative way to get the field info:

Function ShowFieldsRS(strTable)
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim strSql As String

strSql = "SELECT " & strTable & ".* FROM " & strTable & " WHERE
(False);"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
For Each fld In rs.Fields
Debug.Print fld.Name, FieldTypeName(fld.Type)
Next
rs.Close
Set rs = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yup, I've done this via DAO before, but that is supposed to "yesterday's
data access technology" - and my DAO code looks very much like yours

I was hoping I could get table names, and field names and types via SQL
(like Oracle and SQLServer).

I thought that this sort of stuff is part of the SQL2 standard (circa
1998 or some such). I'm surprised Access would not support it.

I am building a graphic query front end to a system and, well, I guess
I'll just keep experimenting ...

Anyway, thanks very much for your helpful comments and insight.

Mark

Not sure you can get the field names directly from the system tables.

Can you add a reference to the Microsoft DAO library? If so you can
iterate the Fields of the TableDef to get their Name, Type, and other
properties. Sample code:
http://allenbrowne.com/func-06.html

You could also open a recordset based on "SELECT * FROM MyTable;", and
then examine the Fields of the Recordset.

If you are not familar with DAO (the native Access library for doing
this kind of thing), the basic object model is diagrammed here:
http://allenbrowne.com/ser-04.html


Thanks Allen, that's very helpful.

I see I can get a list of table and query names from MsysObjects and
relationships from (of all things MsysRelationships. However, I've
looked through all the other system tables (Msys*), but I still don't
see how I can get the a list of field names (and possibly data types?)
for a given table. Any pointers on that?

TIA ...

You can read these objects from the MSysObjects table.

List of tables:
SELECT MsysObjects.Name FROM MsysObjects
WHERE (([Type] = 1) AND ([Name] Not Like "~*") AND ([Name] Not Like
"MSys*"))
ORDER BY MsysObjects.Name;

Include type 6 if you want linked tables, 4 for ODBC linked tables,
and 5 for queries.

You can probably get a list of relations by querying the
MSysRelationships table. The grbit field indicates the attributes of
the relation - bitfield values that are members of
RelationAttributeEnum.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How can I get a list of the names of tables (and queries too, if
possible) in an access db? In Oracle I'd do something like "select *
from tab" to query the db catalog. If I use DAO, I can iterate over
the db.Tables collection, but I don't see how to do this as a query
in Access.

I'm writing a small applet in .NET, and would like to use ADO.NET to
get a list of tables. Offer the list to the user, where they can
select the desired tables. Then I want to get the field names of the
selected tables, as well as the relationships between the selected
tables.

How does one achieve this against Access?

 

[Douglas J. Steele]

Try using using the OpenSchema method.

http://support.microsoft.com/?id=186246


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Interestingly, when I issue:

SELECT Name FROM MsysObjects WHERE ([Type] = 1) AND ([Name] Not Like "~*")
AND ([Name] Not Like "MSys*") ORDER BY Name;

or even
SELECT * FROM MsysObjects

in an ODBC connection for ADO.NET against the NorthWindTraders.mdb, I get
a priv error:
ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Record(s) cannot
be read; no read permission on 'msysobjects'.

But
select * from msysrelationships
and
select * from customers

both work OK.

Here's my ODBC connection string:
PageTimeout=5;FIL=MS
Access;MaxScanRows=8;DefaultDir=R:\Access-DBs;FILEDSN=C:\Program
Files\Common Files\ODBC\Data
Sources\Access-MBB.dsn;DriverId=25;DBQ=R:\Access-DBs\Northwind.mdb;UserCommitSync=Yes;Exclusive=0;UID=admin;Driver={Driver
do Microsoft Access
(*.mdb)};MaxBufferSize=2048;Threads=3;SafeTransactions=0;ReadOnly=1


What am I missing? What do I need have to do to query msysobjects?

When MS introduced A2000, they did try to persuade users to go for ADO
rather than DAO, so that's probably the hype you heard.

What they did not tell you was that Access itself still uses DAO. For
example, if you try to run an ADO-specific query from the Query design
window (SQL View), it fails because Access is using DAO. It always made
best sense to me to use the native Access library.

So, 5 years later, DAO is very much alive and kicking, and the DAO 3.6
reference is back by default in Access 2003, while ADO is effectively
dead (replaced by the very different ADO.NET.)

We know from Erik Rudder's blog at http://blogs.msdn.com/access/ that the
next version of Access will have its own private version of the JET
engine. I expect, therefore, that the object model will be DAO, or an
extension thereof.

So you won't get stuck using DAO.

The OpenRecordset thing is an alternative way to get the field info:

Function ShowFieldsRS(strTable)
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim strSql As String

strSql = "SELECT " & strTable & ".* FROM " & strTable & " WHERE
(False);"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
For Each fld In rs.Fields
Debug.Print fld.Name, FieldTypeName(fld.Type)
Next
rs.Close
Set rs = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yup, I've done this via DAO before, but that is supposed to "yesterday's
data access technology" - and my DAO code looks very much like yours

I was hoping I could get table names, and field names and types via SQL
(like Oracle and SQLServer).

I thought that this sort of stuff is part of the SQL2 standard (circa
1998 or some such). I'm surprised Access would not support it.

I am building a graphic query front end to a system and, well, I guess
I'll just keep experimenting ...

Anyway, thanks very much for your helpful comments and insight.

Mark

Not sure you can get the field names directly from the system tables.

Can you add a reference to the Microsoft DAO library? If so you can
iterate the Fields of the TableDef to get their Name, Type, and other
properties. Sample code:
http://allenbrowne.com/func-06.html

You could also open a recordset based on "SELECT * FROM MyTable;", and
then examine the Fields of the Recordset.

If you are not familar with DAO (the native Access library for doing
this kind of thing), the basic object model is diagrammed here:
http://allenbrowne.com/ser-04.html


Thanks Allen, that's very helpful.

I see I can get a list of table and query names from MsysObjects and
relationships from (of all things MsysRelationships. However, I've
looked through all the other system tables (Msys*), but I still don't
see how I can get the a list of field names (and possibly data types?)
for a given table. Any pointers on that?

TIA ...

You can read these objects from the MSysObjects table.

List of tables:
SELECT MsysObjects.Name FROM MsysObjects
WHERE (([Type] = 1) AND ([Name] Not Like "~*") AND ([Name] Not Like
"MSys*"))
ORDER BY MsysObjects.Name;

Include type 6 if you want linked tables, 4 for ODBC linked tables,
and 5 for queries.

You can probably get a list of relations by querying the
MSysRelationships table. The grbit field indicates the attributes of
the relation - bitfield values that are members of
RelationAttributeEnum.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How can I get a list of the names of tables (and queries too, if
possible) in an access db? In Oracle I'd do something like "select *
from tab" to query the db catalog. If I use DAO, I can iterate over
the db.Tables collection, but I don't see how to do this as a query
in Access.

I'm writing a small applet in .NET, and would like to use ADO.NET to
get a list of tables. Offer the list to the user, where they can
select the desired tables. Then I want to get the field names of the
selected tables, as well as the relationships between the selected
tables.

How does one achieve this against Access?

 

[MarkB]

Thanks Doug,

That was very helpful ... especially the link in the article that lead to
another article describing how to get schema info in ADO.NET

Interestingly, I can get results for this query:
select * from msysrelationships

from Northwind.mdb, but not from some other Access databases. It fails with
this error:
Record(s) cannot be read; no read permission on 'msysrelationships'.

Can anyone shed some light on this? Why would I be denied read access to
some system tables? What do I have to do to obtain access to such system
tables?

Note that there is no security on these databases; I can just open them in
Access. Looking at Tools-Security, there seems no way to effect permissions
on system tables (even when I have the MDB opened "exclusively")

And for ADO.NET, I'm logging in as user "Admin" with no password, and I can
do other queries.

I'd appreciate any help ...

Mark

Try using using the OpenSchema method.

http://support.microsoft.com/?id=186246


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Interestingly, when I issue:

SELECT Name FROM MsysObjects WHERE ([Type] = 1) AND ([Name] Not Like
"~*") AND ([Name] Not Like "MSys*") ORDER BY Name;

or even
SELECT * FROM MsysObjects

in an ODBC connection for ADO.NET against the NorthWindTraders.mdb, I get
a priv error:
ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Record(s) cannot
be read; no read permission on 'msysobjects'.

But
select * from msysrelationships
and
select * from customers

both work OK.

Here's my ODBC connection string:
PageTimeout=5;FIL=MS
Access;MaxScanRows=8;DefaultDir=R:\Access-DBs;FILEDSN=C:\Program
Files\Common Files\ODBC\Data
Sources\Access-MBB.dsn;DriverId=25;DBQ=R:\Access-DBs\Northwind.mdb;UserCommitSync=Yes;Exclusive=0;UID=admin;Driver={Driver
do Microsoft Access
(*.mdb)};MaxBufferSize=2048;Threads=3;SafeTransactions=0;ReadOnly=1


What am I missing? What do I need have to do to query msysobjects?

When MS introduced A2000, they did try to persuade users to go for ADO
rather than DAO, so that's probably the hype you heard.

What they did not tell you was that Access itself still uses DAO. For
example, if you try to run an ADO-specific query from the Query design
window (SQL View), it fails because Access is using DAO. It always made
best sense to me to use the native Access library.

So, 5 years later, DAO is very much alive and kicking, and the DAO 3.6
reference is back by default in Access 2003, while ADO is effectively
dead (replaced by the very different ADO.NET.)

We know from Erik Rudder's blog at http://blogs.msdn.com/access/ that
the next version of Access will have its own private version of the JET
engine. I expect, therefore, that the object model will be DAO, or an
extension thereof.

So you won't get stuck using DAO.

The OpenRecordset thing is an alternative way to get the field info:

Function ShowFieldsRS(strTable)
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim strSql As String

strSql = "SELECT " & strTable & ".* FROM " & strTable & " WHERE
(False);"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
For Each fld In rs.Fields
Debug.Print fld.Name, FieldTypeName(fld.Type)
Next
rs.Close
Set rs = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yup, I've done this via DAO before, but that is supposed to
"yesterday's data access technology" - and my DAO code looks very much
like yours

I was hoping I could get table names, and field names and types via SQL
(like Oracle and SQLServer).

I thought that this sort of stuff is part of the SQL2 standard (circa
1998 or some such). I'm surprised Access would not support it.

I am building a graphic query front end to a system and, well, I guess
I'll just keep experimenting ...

Anyway, thanks very much for your helpful comments and insight.

Mark

Not sure you can get the field names directly from the system tables.

Can you add a reference to the Microsoft DAO library? If so you can
iterate the Fields of the TableDef to get their Name, Type, and other
properties. Sample code:
http://allenbrowne.com/func-06.html

You could also open a recordset based on "SELECT * FROM MyTable;", and
then examine the Fields of the Recordset.

If you are not familar with DAO (the native Access library for doing
this kind of thing), the basic object model is diagrammed here:
http://allenbrowne.com/ser-04.html


Thanks Allen, that's very helpful.

I see I can get a list of table and query names from MsysObjects and
relationships from (of all things MsysRelationships. However,
I've looked through all the other system tables (Msys*), but I still
don't see how I can get the a list of field names (and possibly data
types?) for a given table. Any pointers on that?

TIA ...

You can read these objects from the MSysObjects table.

List of tables:
SELECT MsysObjects.Name FROM MsysObjects
WHERE (([Type] = 1) AND ([Name] Not Like "~*") AND ([Name] Not Like
"MSys*"))
ORDER BY MsysObjects.Name;

Include type 6 if you want linked tables, 4 for ODBC linked tables,
and 5 for queries.

You can probably get a list of relations by querying the
MSysRelationships table. The grbit field indicates the attributes of
the relation - bitfield values that are members of
RelationAttributeEnum.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How can I get a list of the names of tables (and queries too, if
possible) in an access db? In Oracle I'd do something like "select
* from tab" to query the db catalog. If I use DAO, I can iterate
over the db.Tables collection, but I don't see how to do this as a
query in Access.

I'm writing a small applet in .NET, and would like to use ADO.NET
to get a list of tables. Offer the list to the user, where they can
select the desired tables. Then I want to get the field names of
the selected tables, as well as the relationships between the
selected tables.

How does one achieve this against Access?