469,285 Members | 2,524 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,285 developers. It's quick & easy.

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

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:

Expand|Select|Wrap|Line Numbers
  1. SELECT [name],[system_type_id],[max_length],[precision],[scale],[is_nullable],[is_identity],[column_id] 
  2. FROM sys.Columns 
  3. WHERE object_id =object_id('[<table name>]') 
  4. 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.
Nov 18 '10 #1
6 8815
32,173 Expert Mod 16PB
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.
Nov 18 '10 #2
I've sort of worked it out. Access doesn't give you as much information as SQLServer does, but it does give you some...

Expand|Select|Wrap|Line Numbers
  1. Dim cn As ADODB.Connection
  2. Dim rs As ADODB.Recordset
  4. Set cn = CurrentProject.AccessConnection
  5. Set rs = cn.OpenSchema(ADODB.SchemaEnum.adSchemaColumns, Array(Empty,Empty,Empty,"tablename"))
  7. Debug.Print rs!COLUMN_NAME
The following link has a list of all the fields available to the OpenSchema calls.

Nov 18 '10 #3
Steven Kogan
107 Expert 100+
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT OBJECT_ID FROM sys.objects
  2. WHERE name = 'Employee'
  5.     FROM sys.schemas
  6.     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.
Nov 18 '10 #4
32,173 Expert Mod 16PB
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.
Nov 18 '10 #5
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...
Nov 19 '10 #6
32,173 Expert Mod 16PB
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)?
Expand|Select|Wrap|Line Numbers
  1. Call MsgBox(CurrentDb.TableDefs.Count)
Nov 19 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by John T. McCraw | last post: by
1 post views Thread by christianlott1 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.