Solved

Get the Description of a table

Posted on 2006-11-17
21
260 Views
Last Modified: 2008-02-01
Hi experts,

I have an Access 2000. I created a table called table1

table1
field:  field1
type: yes/no
description: "Employee Started Last Year"

field:  field2
type: text
description: "Employee Name"

So, what I am trying to achieve is that - do a make query, and only display - Descriptions of each fields instead of their fields.

For example:

Employee Started Last Year        Emploee Name
Yes                                          Bill
No                                           Carl

Can I able to do that?

I want to do this in Access 2000.
0
Comment
Question by:neonlights
  • 10
  • 7
  • 3
  • +1
21 Comments
 
LVL 4

Expert Comment

by:DoubleJ92
Comment Utility
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.
0
 

Author Comment

by:neonlights
Comment Utility
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
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 200 total points
Comment Utility
I just *know* I've posted this somewhere on EE before... but will the search find it for me by function name?  Noooo
Anyway... this will fill a new table with values for you.
You can then easily open the table and view - or whatever...
Shouldn't need any references at all.

Sub ListAllTableFields()
On Error Resume Next

    Dim db As Database
    Dim tdf As Object 'TableDef
    Dim fld As Object 'dao.Field
    Dim rst As Object 'dao.Recordset
   
    Set db = CurrentDb
   
    With CurrentProject.Connection
        .Execute ("DROP TABLE tblTempTableFieldList")
        .Execute ("CREATE TABLE tblTempTableFieldList " & _
        "(TableName varChar(40), FieldCount Integer, FieldName varChar(40), FieldType varChar(40), FieldSize varChar(40), FieldDesc varChar(40))")
    End With
   
    If err Then
        MsgBox "Error creating temp table", vbInformation
        Exit Sub
    End If
   
    Set rst = db.OpenRecordset("tblTempTableFieldList", dbOpenDynaset)
   
    With rst
        For Each tdf In db.TableDefs
            If Left(tdf.Name, 4) <> "MSys" Then
                For Each fld In tdf.Fields
                    .AddNew
                        !TableName = tdf.Name
                        !FieldCount = tdf.Fields.count
                        !FieldName = fld.Name
                        !FieldType = fFieldType(fld.Type)
                        !FieldSize = fld.Size
                        !FieldDesc = fld.Properties("Description")
                    .Update
                Next
            End If
        Next
        .Close
    End With
   
    Set fld = Nothing
    Set tdf = Nothing
    Set rst = Nothing
    Set db = Nothing
   
End Sub
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 300 total points
Comment Utility
or you can use this to pass the table name

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
0
 

Author Comment

by:neonlights
Comment Utility
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
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
VBA, you can place the codes in a module
0
 

Author Comment

by:neonlights
Comment Utility
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.

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
GetFldDes("Table1")
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
(The idea behind mine is that you would just call
ListAllTableFields
and it will create a table for you with the details in there).
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:neonlights
Comment Utility
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
0
 

Author Comment

by:neonlights
Comment Utility
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
0
 

Author Comment

by:neonlights
Comment Utility
Got it..

Thanks.. get back to you shortly
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
you have to add to your references Microsoft DAO x.x Object library

also where do you want to see the results?
0
 

Author Comment

by:neonlights
Comment Utility
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
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
no. you can not do that  in the query.
you have to set it manually, or thru codes.
0
 

Author Comment

by:neonlights
Comment Utility
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,
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
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?
0
 

Author Comment

by:neonlights
Comment Utility
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.
0
 

Author Comment

by:neonlights
Comment Utility
Cool. Finally I got to working.

Thanks all for your suggestions/solutions

Cheers!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

744 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