How do I access the Description property of a Query/Table programmatically

  • Thread starter Thread starter Conan Kelly
  • Start date Start date
C

Conan Kelly

Hello all,

Do you know when you access the Properties pages of a table, query, form, etc... , you can enter a description.

Can I read/write this description programmatically?

I want to use this description as a test to decide if the query gets exported to another DB but I can't figure out how to get to it.

How can I get to it?

Thanks for any help anyone can provide,

Conan Kelly
 
Hello all,

Do you know when you access the Properties pages of a table, query, form, etc... , you can enter a description.

Can I read/write this description programmatically?

I want to use this description as a test to decide if the query gets exported to another DB but I can't figure out how to get to it.

How can I get to it?

Thanks for any help anyone can provide,

Conan Kelly
Click to expand...

It must be a school project. Someone asked the identical question
yesterday!

Public Sub GetDescriptionProperty()
' read the description property of a report
Dim db As DAO.Database
Dim doc As Document
Set db = CurrentDb
Dim prp As Property
For Each doc In db.Containers("reports").Documents
For Each prp In doc.Properties
If prp.Name = "Description" Then
Debug.Print doc.Name & " " & prp.Name & " " & prp.Value
End If
Next prp
Next doc
End Sub
 
It must be a school project. Someone asked the identical question
yesterday!

Public Sub GetDescriptionProperty()
' read the description property of a report
Dim db As DAO.Database
Dim doc As Document
Set db = CurrentDb
Dim prp As Property
For Each doc In db.Containers("reports").Documents
For Each prp In doc.Properties
If prp.Name = "Description" Then
Debug.Print doc.Name & " " & prp.Name & " " & prp.Value
End If
Next prp
Next doc
End Sub
Click to expand...

Keeping in mind that, at least for queries (maybe other objects, too) that if
there is no Description then there is no Description Property either and it will
throw an error. I don't remember which error number. Maybe 3021 or 3270?
Anyway, you'll need to trap for it and do a Resume Next kinda thing.

HTH,
RD
 
fredg,

Thanks for your feedback.

No this isn't for a class project. This for a project at work. It just must be a co-winkie-dink.

I changed your code to this:

Public Sub GetDescriptionProperty()
' read the description property of a report
Dim db As DAO.Database
Dim doc As Document
Set db = CurrentDb
Dim prp As Property
Dim ctrLoop As Container

For Each ctrLoop In db.Containers
Debug.Print "Properties of " & ctrLoop.Name & " container"
For Each doc In ctrLoop.Documents
Debug.Print " - " & doc.Name
For Each prp In doc.Properties
' If prp.Name = "Description" Then
Debug.Print " - " & prp.Name & " " & prp.Value
' End If
Next prp
Next doc
Next ctrLoop
End Sub


Cool, now I know how to use Debug and the Immediate window--VERY HELPFUL. But the Immediate window isn't big enough to retain all
of the results from this procedure (and NO, dragging the boarder to make the window bigger won't help. I scroll all the way to the
top and only the last 200 lines are retained in the Immediate window). How would I (more appropriately--YOU ;-) ) edit this
code to export to a text file instead of printing to the Immediate window.

Thanks again for all of your help,

Conan
 
Conan, I answered this q. in another group where you asked, it, but you
should be aware that if a QueryDef has no Description, Access can decide to
incorrectly report the description of an underlying table of the query, so
be aware of the fly in the ointment.

If you are interested in how to document the objects in an Access database,
Jeff Conrad has a good tool here:
http://home.bendbroadband.com/conradsystems/accessjunkie/csdtools.html
 
Allen,

Thanks for the info.

This is the first time I asked this question. Like fredg mentioned, someone else asked the exact same question yesterday (well 2
days ago now). I'm curious to see that question and the replies to it. What NG was that in? Do you remember the time of the OP
and the name of the OP'er?

Also, please check out my reply to fredg's reply on this thread. Your feedback will be much appreciated.

Thanks again,

Conan Kelly
 
fredg,

Thanks for your feedback.

No this isn't for a class project. This for a project at work. It just must be a co-winkie-dink.

I changed your code to this:

Public Sub GetDescriptionProperty()
' read the description property of a report
Dim db As DAO.Database
Dim doc As Document
Set db = CurrentDb
Dim prp As Property
Dim ctrLoop As Container

For Each ctrLoop In db.Containers
Debug.Print "Properties of " & ctrLoop.Name & " container"
For Each doc In ctrLoop.Documents
Debug.Print " - " & doc.Name
For Each prp In doc.Properties
' If prp.Name = "Description" Then
Debug.Print " - " & prp.Name & " " & prp.Value
' End If
Next prp
Next doc
Next ctrLoop
End Sub

Cool, now I know how to use Debug and the Immediate window--VERY HELPFUL. But the Immediate window isn't big enough to retain all
of the results from this procedure (and NO, dragging the boarder to make the window bigger won't help. I scroll all the way to the
top and only the last 200 lines are retained in the Immediate window). How would I (more appropriately--YOU ;-) ) edit this
code to export to a text file instead of printing to the Immediate window.

Thanks again for all of your help,

Conan
Click to expand...

Just a small change is needed.
Create a new Report.
Add a unbound text control to the Detail section.
Make sure you set the control's CanGrow property to Yes
Name this control 'ShowProperty'.

Set the Detail CanGrow property to Yes.

Add a Report Header.

Place the following code in the Report Header Format event:


Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)
' read the Description property of a report

Me![ShowProperty] = ""

Dim db As DAO.Database
Dim doc As Document
Set db = CurrentDb
Dim prp As Property
For Each doc In db.Containers("reports").Documents
For Each prp In doc.Properties
If prp.Name = "Description" Then
Me![ShowProperty] = Me![ShowProperty] & doc.Name & " " &
prp.Name & " " & prp.Value & vbNewLine
End If
Next prp
Next doc
End Sub
 
Back
Top