Results 1 to 2 of 2
  1. #1
    pkthogit is offline Novice
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    1

    Post Access query to return list of field names of a table where data type is Text

    Hi all,



    Some times, this question seems to me as a silly doubt.........anyway, I have decided to ask this ....

    Im in a situation where in, I need to write a query to list out all field names/column names against each table based on conditions.

    Friends, I have an access *.mdb file which is having almost 120 tables. I need to prepare a report with all table names and the field names of each table where field is of type text. Im just interested in definition of tables and query.

    I can go to design view of each table and list out text field names. But, each table will have atleast 20 fields and it is very laborious to list out all field names from almost 120 tables.

    To be consice, Im trying something like below.

    SELECT [ALL COLUMN_NAMES/FIELD_NAMES] from TABLE_NAME where (data type of FIELD_NAME is Text)

    Please suggest a solution or please shout if Im wrong anywhere.

  2. #2
    Robertt is offline Novice
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    12
    I'll help you get started...

    Most databases store this kind of data in hidden tables. This is also true of Access. Assuming your are using Access 2007, right click on the top of the navigation bar and select "Navigation Options". Turn on "Show System Objects". In your list of tables, you will find several gray system tables.

    Take a look at MSysIMEXColumns and you will see fieldName and dataType. At first glance, it looks like 10 is Text datatype, so you could try

    select * from MSysIMEXColumns where datatype=10

    You will probably also have to join this up with MSysObjects so you can limit it to specific tables rather than everything in the system.

    Have fun, I did something similar years ago to build my own query designer window before Access made it so easy.

    HTH,

    Robert

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-15-2009, 01:47 PM
  2. creating field names in table without typing
    By GHanover in forum Database Design
    Replies: 2
    Last Post: 12-14-2009, 05:13 AM
  3. copy data from text file into table in access
    By miziri in forum Programming
    Replies: 3
    Last Post: 08-12-2009, 03:02 PM
  4. Replies: 5
    Last Post: 03-31-2009, 09:16 PM
  5. Quick way to stuff field names into text field
    By kfinpgh in forum Programming
    Replies: 0
    Last Post: 01-04-2007, 01:13 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums