472,373 Members | 1,943 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,373 software developers and data experts.

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 9089
32,511 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,511 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,511 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

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

Similar topics

by: Joe | last post by:
Hi All, I am new to using the Access DB and I need some help if someone is able to give it to me. What I want to do is get the names of the columns of certain tables. Not the data in the table...
by: John T. McCraw | last post by:
How can I retrieve just the column names from an Access table. I don't need the data, just the column names.
by: steve | last post by:
hi, how can i loop and get the field names of the columns in a dataset\s table? I want the names of the columns (i.e. the field names) not the actual data. TIA
by: Mike S | last post by:
Hi all, A (possibly dumb) question, but I've had no luck finding a definitive answer to it. Suppose I have two tables, Employees and Employers, which both have a column named "Id": Employees...
by: christianlott1 | last post by:
I want to provide users with an interface to create a custom merge (all in Access, not Word). User will put in a set of brackets ("<>") in a memo field and when they click the merge button it will...
by: Lastknight | last post by:
hi all, How to get fieldnames of a table using postgresql? Can someone help me to overcome this issue? Like if i have table called "employee" i need to fetch the...
by: beautifulcarcass | last post by:
Hi, in this school project im making, im having a problem if i could display the column names from a table on a MYSQL database to a webpage through PHP with a loop is there a function to display...
by: beautifulcarcass | last post by:
Hi, in this school project im making, im having a problem if i could display the column names from a table on a MYSQL database to a webpage through PHP with a loop is there a function to display...
by: dtshedd | last post by:
SQL newbie here so appreciate your patience and help Windows XP Home, Microsoft Access 2002, ActivePerl 820 (corresponds to Perl version 5.8.8), Abyss Web Server X1 (v 2.4) An archived post on...
by: koti688 | last post by:
how to get the column names and the values of them in select statment. I have a packaze like this named ADB.pm package ADB; use DBI; @ISA = ('Exporter');
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
by: jack2019x | last post by:
hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.
by: DizelArs | last post by:
Hi all) Faced with a problem, element.click() event doesn't work in Safari browser. Tried various tricks like emulating touch event through a function: let clickEvent = new Event('click', {...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.