where to find column name and properties in system tables in acces

[costpointconsultant]

I have a need to find information on the column names and properties from
within the objects in the dbase. I have unhidden the system objects (am
using access 2003) abd found msysobjects and other tables. I have noted
objects like queries and tables, but don't see where I might find the column
names and properties like length, field type, etc. In sql server this info
is contained in information_schema.columns and oracle it's in all_tab_columns
table. Is there a place in ms access to find this info dynamically? Thank
you

 

[Allen Browne]

Access does expose the column names and their properties like that.

You can get them programmatically like this:
http://allenbrowne.com/func-06.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

in message
news:[email protected]...

 

[costpointconsultant]

thank you Allen-thank you very much! very useful. I will check out your
other tips too. Take care--

 

[costpointconsultant]

I tried figuring out how to use the code referenced and I can't seem to
figure out what I need to do. I do a lot of query manipulation of linked and
local tables, but am not proficient with many other areas of access like
forms, modules, reports. I created the function and referenced it in a query
with a table input for one column and a second column with the function and
the table i wanted to describe, and it gives me the error code if i type a
table that doesn't exist, so i know it's using the code, but i get a null
value in that cell. i realize there will be many rows returned to a table
that has multiple columns, but am not quite sure how to use this code to get
a list for a table. I know it has a print statement, so i guess it must
print to somewhere. this will be valuable. more valuable would be to be
able to get rows in a result set or a table that have this info-so anyone who
has some guidance on these two things to use this would be helpful. sorry
i'm such a greenhorn! thanks

 

[Allen Browne]

The code prints its results to the Immediate Window.
Press Ctrl+G to open the Immediate Window.
Then in that window, enter:
? TableInfo("Table1")
to see the results for Table1.

It would be possible to re-write the code so that it writes its results into
a temporary table if you need to view it in a query, but this would require
some understanding of programmatically manipulating recordsets.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

in message

 

[costpointconsultant]

Allen-once again, thank you for your response and taking the time. This did
work for me. Access fascinates me, but I have so little time due to work to
get into the things like programmtical recordsets, forms, etc. If I could
only learn more on this I would be able to do some remarkable things with my
work. But this is a start, thank you.