Dynamically Access 2007 Get Column Names (+other info) for table

[Jane Alford]

Is there any way to dynamically get information about a table in Access 2007?

I know I can do this with SQLServer.

I'm linking to SQLServer tables from Access 2007.

The SQLServer 2005 SELECT statement is:

SELECT [name],[system_type_id],[max_length],[precision],[scale],[is_nullable],[is_identity],[column_id] 
FROM sys.Columns 
WHERE object_id =object_id('[<table name>]') 
ORDER BY column_id
 

I want to do the same using SQL that Access understands!

Any ideas.

I've heard of GetSchema, but I can't figure out how to use it, the ADODB connection certainly doesn't know about it.

[NeoPa]

There is a collection within a database called TableDefs. Each table within the database has an entry available from there.

Within a TableDef there is a collection of Fields which contains all the fields.

[Jane Alford]

I've sort of worked it out. Access doesn't give you as much information as SQLServer does, but it does give you some...

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
 
Set cn = CurrentProject.AccessConnection
Set rs = cn.OpenSchema(ADODB.SchemaEnum.adSchemaColumns, Array(Empty,Empty,Empty,"tablename"))
 
Debug.Print rs!COLUMN_NAME
 
 

The following link has a list of all the fields available to the OpenSchema calls.

OpenSchema

[Steven Kogan]

Since you are connecting to SQL Server tables you should be able to leverage sys.Columns.

Link to sys.Columns, and then use Jet-SQL to query it.

The problem comes from the function object_id('[<table name>]'), which would be tricky to get to from Access.

The SQL to get the object_id from the table name & schema is:

SELECT OBJECT_ID FROM sys.objects
WHERE name = 'Employee'
AND SCHEMA_ID =
    (SELECT SCHEMA_ID
    FROM sys.schemas
    WHERE name = 'HumanResources')

So if you link to sys.schema and sys.objects (the linked table names won't accept periods), then you should be able to write the type of queries you'd like.

Another option would be using a pass-through query, in which case you wouldn't need to modify your SQL at all.

[NeoPa]

Jane, If you're interested in Access information (as opposed to general table or database info from remote systems) then ADO would be preferable to ADODB, as it was designed specifically with Jet (and therefore Access) in mind. I would expect an ADODB interface to be limited when dealing with Jet specifics.

On a separate point, you didn't respond to my earlier post (#2). Did you find it didn't provide you with the information you required? I cannot think of anything it doesn't have for you.

[Jane Alford]

No it didn't help. Access doesn't appear to have TableDefs.

The reply I added about OpenSchema is the way Access seems to want to do things.

I would love to be able to use an SQLOLEDB connection directly to the Server, but linking the tables into Access is the way the Client wants this done, so I'm limited with the possibilities...

Thanks for all the suggestions. I've got enough information to force the round peg into the square hole now...

[NeoPa]

Jane Alford:
Access doesn't appear to have TableDefs.

What are you talking about? TableDefs are one of the most fundamental parts of an Access database. If you have any tables in the table section of your database then you have TableDefs (and even if you don't see any then there are some hidden and system ones there anyway).

On the other hand, if for some reason you weren't able to reference them in your code, then we have a much simpler issue to deal with.

What do you see if you run this code line in your Immediate Pane (See Debugging in VBA for more on that)?

Call MsgBox(CurrentDb.TableDefs.Count)