Solved

Get the Description of a table

Posted on 2006-11-17
21
290 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
ID: 17967485
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
ID: 17967519
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17967586
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 200 total points
ID: 17967600
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 300 total points
ID: 17967606
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
ID: 17968364
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17968389
VBA, you can place the codes in a module
0
 

Author Comment

by:neonlights
ID: 17968427
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17968439
GetFldDes("Table1")
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17968481
(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
 

Author Comment

by:neonlights
ID: 17968498
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
ID: 17968567
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
ID: 17968580
Got it..

Thanks.. get back to you shortly
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17968805
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
ID: 17973428
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17973826
no. you can not do that  in the query.
you have to set it manually, or thru codes.
0
 

Author Comment

by:neonlights
ID: 17975138
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17975315
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
ID: 17975334
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
ID: 17975917
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
ID: 18054401
Cool. Finally I got to working.

Thanks all for your suggestions/solutions

Cheers!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

831 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