?
Solved

Script to read table Def

Posted on 2011-09-11
6
Medium Priority
?
317 Views
Last Modified: 2012-05-12
Hi Experts

Does any one know how to do a script (or has one) that will loop through every field of a given table and retrieve its field name, type and size?

by size, i mean:
  - if type text, size = 15
  - if type = number, size = long
   - if type = yes/no, size = true/false

etc...

Ideally, i'd store this info in a 4-field table (table, name, type, size), and use in a report to print and document.,

Any help will be appreciated
0
Comment
Question by:APD Toronto
6 Comments
 
LVL 47

Accepted Solution

by:
aikimark earned 2000 total points
ID: 36520443
Dim td As DAO.Tabledef
Dim fld As DAO.Field
For Each fld In DbEngine(0)(0).TableDefs("MyTablename").Fields
  Debug.Print fld.Name, fld.Type, fld.Size 
Next

Open in new window

0
 
LVL 77

Expert Comment

by:peter57r
ID: 36521271
You appear to be duplicating what is already available in the database documentor tool
0
 

Author Comment

by:APD Toronto
ID: 36523249
alkimark, where can I get what the type #s are?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
LVL 47

Expert Comment

by:aikimark
ID: 36523426
open up your immediate window and type
?DataTypeEnum.

Open in new window


When you type the period, you should see, courtesy of intellisense, a list of the enum names and values for database field type.

0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 36523510
For a somewhat more elegant report on fields, see my Extras Plus add-in (http://www.helenfeddema.com/Files/code20.zip), a free download from my Website.  One of its menu add-ins creates a nicely formatted report listing all fields in all tables in the current database, either alphabetically or by order in the table:

Table-Fields-Report.jpg
0
 
LVL 75
ID: 36524331
Allen Browne has a good list here:

http://allenbrowne.com/func-06.html

mx
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Implementing simple internal controls in the Microsoft Access application.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question