Which schemas can I get?

[ajq96743]

I ran into this code today:

  Dim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\english.mdb"
        Dim olecon As New OleDbConnection(sConn)
        olecon.Open()
        Dim dt As DataTable = olecon.GetSchema("tables")
        olecon.Close()
        GridView1.DataSource = dt
        GridView1.DataBind()

I'm wondering, what other schema names can I supply to GetSchema in the above example, for MS-Access?

[June7]

Try any collection name: queries, forms, reports

What happens?

[ajq96743]

Try any collection name: queries, forms, reports

What happens?

Solved!

Even better than trial and error. If you call getschema without arguments, you get a datatable back with the collection names.
CollectionName: MetaDataCollections DataSourceInformation DataTypes Restrictions ReservedWords Columns Indexes Procedures Tables
Views

Now the next thing for me is to get MS-Access to show the DDL for specific collection members. e.g. If the "Tables" collection has a table called "foo", I would like to be able to get back:

create table foo (bar int, fum char(1), .....)

etc.

[June7]

Don't understand. If table name foo already exists how can you create it?

[ajq96743]

I don't want to create the table "foo". I want to know how to extract the table definition from MS-Access. Suppose I have a database called mydb.mdb that has a table "foo". What sorts of method calls should I make to MS-Access to get it to print the definition of table "foo" in standard SQL DDL?

[June7]

I don't understand the requirement. Tables don't have a query definition to retrieve. Does this help http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

[ajq96743]

e.g. open a database, go to query designer, then from the View menu select SQL View. In the query window that opens type:

create table foo (a int, b char(1))

Then hit the exclamation mark (Run) in the toolbar. Voila! Table foo is defined. In fact, you can do this without even using the interactive MS-Access application. Just talk to it using OLEDB from VB, C#, even Powershell.

Now, I actually want the reverse. Have MS-Access show me the DDL for a table already defined.

[June7]

The result of your instructions is a new table called [foo]. I also saved the SQL and have a new query object. This query has an SQL statement associated with it and can be retrieved with the QueryDefs collection. I do not believe a table has an SQL statement associated with it. Whatever Access is doing in the background that maintains the table structure involves more than an SQL action statement.

Exactly what is your reason for this requirement? What is it you really need to accomplish?

[ajq96743]

Could be. In any case, its good to see that Access correctly process DDL for tables. I'll have to experiment to see what else it handles (CREATE PROCEDURE? CREATE FUNCTION? CREATE VIEW? the list goes on...) I think though, that there is no way to do the reverse.

You see, I'm coming to Access after years in SQL Server. I'm used to writing my own SQL for both DDL and DML so I'm more comfortable with that approach. SQL Server will produce the DDL for a table definition (and pretty much everything else, too). It effectively decompiles the binary to produce source code (SQL DDL). I had hoped that Access would do the same for tables. I already know that it does it for queries.

[June7]

Might want to review http://allenbrowne.com/func-DDL.html