Retrieval of schema information in access database

Question

I am writing an application which connects to an access database using an OleDb connection. Is it possible to connect to an access database which I have not created myself and retrieve information about the schema of the database, all i need are the name of all the tables in the database? I was looking for a SQL statement such as SELECT * FROM SchemaTable Does Access have a standard internal table describing the database? If none of this is possible is there a database that does e.g. SQL Server dbs? I have looked at many examples of ADO.Net applications to find this done, but all of the examples I have looked at seem to require a pre-existing knowledge of the names of the tables that are being queried, before any data access can start.

raphulluk · Answer

My sincerest apologies to Peter T Jones I made and assumption and completely jumped the gun with my interpretation of his response. Thank you for your help. K_M

p.jones · Answer

I never said cleverer or higher tech - I meant further along in process of coding and testing. I came close to answering you can go with OleDbGetSchema to get access to catalog but it is not necessarily best way its up to you. And help says its implementation is patchy across target databases. I think you'll find with what I have done you will go around the Visual Studio help system you'll still have considerable design work and when you have finished you will have lots of testing and a good few surprises. I have to say you have made an assumption yourself here. The ER Model database and associated class sets up a dataset exactly to match processing requirements of a program so you can enter multiple models per program including navigation paths. Eventually the generic dataset instantiation class will be used to support process modelling which has to be linked to a vestigial data model and user interfaces in turn are interfaces to processes not directly to tables. Most data in big systems is generated by processes not users. You shouldn't assume too much KM.

raphulluk · Answer

Many thanks THEPARAGON I managed to work it out in the end and that is the exact syntax I was looking for it works fine now.. I just forgot to post my solution. Peter T Jones - I am doing similar thing as yourself but i am much more advanced with working code. You assume you are better with working code without looking at any of mine and you didn't answer my question! Plus everthing you have written can be found in an introductory tutorial!

compuwiz02 · Answer

I don't mean to be redundant; however to my understanding you are attempting to pull a list of Tables in an Access Database using an OLE DB connection am I correct? The OleDbConnection class is equipped with a method called GetOleDbSchemaTable. Which allows you to query the tables in a Database and returns the schema as a DataTable.

p.jones · Answer

I am doing similar thing as yourself but i am much more advanced with working code. You need your own Access database. It contains entity relationship model namely Schema table to list each database Table table to list each table Column table to list each unique column Relation table to list relations between tables. Then you use 'link' tables to link Table to Column and to link Relation to Column. You only need unique numeric identifier as primary key of each table and then string names for each thing. Now you can get going coding up creation of dataset from datatables and then creation of data relations between them. When you do dataAdapter.FillSchema that will get all the data dictionary info from Access and configure the datatable. Then you use dataAdapter.Fill to bring in the data from the database to the datatable. Not releasing my actual code though. 8-)

raphulluk · Answer

I want to be able to select at a table using Sql: SELECT * FROM but I want my application to be able to connect to any access database and be able to retrieve any table from that database. I have already written the code to allow the application to connect to any access database. Now I want to query the database for the tables it cotains and then give the user of my application a list of possible tables to retrieve and then display one of these tables by binding to a datagrid.... However I wish to do this without any knowledge of what is contain in the database so that the user never has to open up access... Is this possible?

crend_king · Answer

You mean there is a database you now do not know anything about it. Now you want to know What table(s) is in it right? So your problem is generally we know the name of table then write Sql such as SELECT * FROM WHERE ... to get data. Hm...