Solved

Script to read table Def

Posted on 2011-09-11
6
281 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 45

Accepted Solution

by:
aikimark earned 500 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 45

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

815 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now