Get the Description of a table

When you make your query type this for the field:

Employee Started Last Year: [field1]

Employee Name: [field2]

That will create the alias you want.

DoubleJ92.. thanks for your suggestion....

I am sorry if I was not clear. but, I want to do this automatically from the table1. Use the Table1 each field's description

Thanks

try this

Sub GetFldDes()
On Error Resume Next
Dim tbl As DAO.TableDef, fld As DAO.Field
For Each tbl In CurrentDb.TableDefs
  For Each fld In tbl.Fields
    If fld.Properties("Description") <> vbNullString Then
        Debug.Print fld.Name & ";" & fld.Properties("Description")
    End If
  Next
Next
End Sub

HI Capricorn1,

Thanks for your suggestion. I am wondering if I do this code in the vb or the access. VBA or VB.. If I do that in my vb, then, I will have to use my db connetion .. ?

Thanks

and LPurvis .. same.. vb or vba ( never did much on vba.. please give me some direction on how to do.. . .thanks

VBA, you can place the codes in a module

Thanks for your quick response.. I did that in a vba - in a module.

I created a new new module:
Module 1
and pasted your code. now, how do I pass call the GetFldDes(Table1)?

Thanks again.

GetFldDes("Table1")

(The idea behind mine is that you would just call
ListAllTableFields
and it will create a table for you with the details in there).

Thanks.. Capricorn1 again..

This is what I did so far..

Created the Module1 with the code:

Sub GetFldDes(sTable As String)
On Error Resume Next
Dim tbl As DAO.TableDef, fld As DAO.Field
For Each tbl In CurrentDb.TableDefs
  If tbl.Name = sTable Then
  For Each fld In tbl.Fields
    If fld.Properties("Description") <> vbNullString Then
        Debug.Print fld.Name & ";" & fld.Properties("Description")
    End If
  Next
  End If
Next

Then, I created a macro: RunCode and I went to search for the GetFldDes - can not find it..

I am kind of lost.. so, where do I call the GetFldDes("Table1").. in the Module - ?

Thanks again Capricorn1

OK..

This is what I have now:

Sub GetFldDes(sTable As String)
On Error Resume Next
Dim tbl As DAO.TableDef, fld As DAO.Field ********
For Each tbl In CurrentDb.TableDefs
  If tbl.Name = sTable Then
  For Each fld In tbl.Fields
    If fld.Properties("Description") <> vbNullString Then
        Debug.Print fld.Name & ";" & fld.Properties("Description")
    End If
  Next
  End If
Next

Sub Main()
GetFldDes ("Table1")
End Sub

When I run this code: getting error message:  at ***** User defined type not defined.

Do I have to add any ref or components?

Thanks again

Got it..

Thanks.. get back to you shortly

you have to add to your references Microsoft DAO x.x Object library

also where do you want to see the results?

I got that to work.

I added DAO and it is working.

Thanks.

I am trying to do here is:

Let's say I have a table1 and field1 and field2

I would like to make a query "Make Table Query" using table1.

SELECT Table1.Field1, Table1.Field2 INTO Table2
FROM Table1;

Now, from this query, I would like to add a "Description" to the Table2 fields. So, if I open the design view of Table2, I can view, those fields description.

Field Name    Data Type         Description
field1            Text                 This is field1
field2            Text                 This is field2

How can I achieve this in my query.?

Thanks again

no. you can not do that  in the query.
you have to set it manually, or thru codes.

Thanks Capricorn1.

How do I do that thru code for my example?

I really need to find a way to do that. I have over 100 tables, and I can do a some sort of procedure to give description to all the fields.

Please let me know.

Thanks,

neonlights ,
i don't think it worth writing the codes to describe all the fields in the table.
is just the same as placing the description manually

I might as well comment again here - for what it's worth.
If Cap means that iterating through every field in every table only to give it a standard description based on the field name - seems pointless, then I agree.
Like all the code that has gone before - it would work and isn't difficult... but what will it achieve?

Thanks for both answers. I am very sorry LPurvis, I did not responded to you earlier.

I know what you guys are saying. Here is the situation.

I have 100 or so tables. Everynight, we do MakeTable Query to bring the data from Oracle database to Access 2000 for reporting purposes to those 100s tables.

We also created a Access 2000 driven vb program. In this program, users can create their own reports with where fields..everything is going good.. no problem.

But, there is a one thing - in the reporting window, user see all the tables - in a tree view, and when they click on the treeview + - they see all the fields in that table.. so, user just click on the fields to add to their report. Sometimes, the fields too technical. What we are trying to do is that - use table field's description to show mouse over in my form.

You see.. one of the solution would be - create a extra table to put all those fields and their description. But, I think this solution would be the last resource.

Any suggestions?

Thanks both.

Cool. Finally I got to working.

Thanks all for your suggestions/solutions

Cheers!