By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,609 Members | 3,812 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,609 IT Pros & Developers. It's quick & easy.

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

P: 8
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
  5.  
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
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,494
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

P: 8
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
  3.  
  4. Set cn = CurrentProject.AccessConnection
  5. Set rs = cn.OpenSchema(ADODB.SchemaEnum.adSchemaColumns, Array(Empty,Empty,Empty,"tablename"))
  6.  
  7. Debug.Print rs!COLUMN_NAME
  8.  
  9.  
The following link has a list of all the fields available to the OpenSchema calls.

OpenSchema
Nov 18 '10 #3

Expert 100+
P: 107
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'
  3. AND SCHEMA_ID =
  4.     (SELECT SCHEMA_ID
  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

NeoPa
Expert Mod 15k+
P: 31,494
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

P: 8
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

NeoPa
Expert Mod 15k+
P: 31,494
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.