Fielddescription action query (1 Viewer)

RonaldF

New member
Local time
Today, 20:42
Joined
May 12, 2016
Messages
7
Dear experts,

My first post in the forum, exciting!

For documenting purposes, among other properties, I am trying to retrieve the description (if any) of a queryfield in VBA. To be clear: I am not looking for the description of the query itself, but for the description of the fields/columns in the query (in design view you can see this in when you click in a field. On the right side of the screen the properties of the field appears, with description on the top)

With select queries, I succeeded via the fields collection of the querydef object. But for action queries there seems not to be a fieldscollection (it is empty), so I am not able to retrieve the description. I am not sure if I do it the wrong way or that it simply does not have a fieldscollection the same way a select query (or table) does.

By the way, I am working in Access 2016, desktop version.

Does anybody knows a way to retrieve the description (and other properties) of fields in an action query in VBA?

I hope you experts can help me out!

Kind regards,
Ron
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:42
Joined
Jan 23, 2006
Messages
15,364
Here is some code you can try:
It will create a table called data_dictionary and populate it with info from all of your tables. It will pick up descriptions where they exist.
Put these 2 procedures into a new module. Name it uniquely eg Data_dictionary_stuff

Run the DocumentTables routine and it will rebuild the table with all current info and present a message when it is finished.

Good luck.
Code:
Option Compare Database
Option Explicit

'---------------------------------------------------------------------------------------
' Procedure : DocumentTables
' DateTime  : 2006-06-09 09:29
' Author    : jack
' Purpose   : To create documentation for all tables in this database
'             where tableName <>MSys*  or TableName doesn't start with "~"
'
'**** Note: This routine builds a new table (data_dictionary) from scratch on each execution ****
'
'Fields that are recorded
'   table_name varchar(250)
'   table_description varchar(255)
'   field_name varchar(250)
'   field_description varchar(255)
'   ordinal_position NUMBER,
'   data_type varchar(15),"
'   length varchar(5)
'   default varchar(30)
'
'
' ' Last Modified:
'
' Inputs: N/A
' Dependency:   Calls Function FieldType
'---------------------------------------------------------------------------------------
'
Public Sub DocumentTables()
      'Requires function FieldType



          Dim db As DAO.Database
          Dim rs As DAO.Recordset
          Dim tdf As DAO.TableDef
          Dim fld As DAO.Field
          Dim strSQL_Drop As String
          Dim strSQL_Create As String
          '==
          Dim idxLoop As index

10        On Error Resume Next
          '===
          'SQL to Delete existing copy of this table
20    strSQL_Drop = "DROP TABLE data_dictionary;"
30        On Error GoTo Error_DocumentTables
          'SQL to Create the data_dictionary table
40    strSQL_Create = "CREATE TABLE data_dictionary" & _
                    "(EntryID AUTOINCREMENT PRIMARY KEY,table_name varchar(250),table_description varchar(255)," & _
                    "field_name varchar(250),field_description varchar(255)," & _
                    "ordinal_position NUMBER, data_type varchar(15)," & _
                    "length varchar(5), default varchar(30));"

50    Set db = CurrentDb()

60    db.Execute strSQL_Drop, dbFailOnError
70    DoEvents
80    db.Execute strSQL_Create, dbFailOnError
90    DoEvents
100   Set rs = db.OpenRecordset("data_dictionary")

110   With rs
120     For Each tdf In db.TableDefs
130         If Left(tdf.name, 4) <> "Msys" _
               And Left(tdf.name, 1) <> "~" Then

140             For Each fld In tdf.Fields
150                 .AddNew
160                 !table_name = tdf.name
170                 !table_description = tdf.Properties("description")
180                 !field_name = fld.name
190                 !field_description = fld.Properties("description")
200                 !ordinal_position = fld.OrdinalPosition
210                 !data_type = FieldType(fld.Type)
220                 !Length = fld.Size
230                 !Default = fld.DefaultValue

240                 .Update
250             Next
260         End If
270     Next
280   End With

290   MsgBox "Tables have been documented", vbInformation, "TABLES DOCUMENTED"

300   rs.Close
310   db.Close

Exit_Error_DocumentTables:

320   Set tdf = Nothing
330   Set rs = Nothing
340   Set db = Nothing

350   Exit Sub

Error_DocumentTables:

360   Select Case Err.number

          Case 3376

370     Resume Next    'Ignore error if table not found
380   Case 3270    'Property Not Found

390     Resume Next
400   Case Else

410     MsgBox Err.number & ": " & Err.Description
420     Resume Exit_Error_DocumentTables

430   End Select

End Sub


Code:
'---------------------------------------------------------------------------------------
' Procedure : FieldType
' Author    : Jack
' Created   : 3/18/2008
' Purpose   : To identify fieldtypes in Access.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Private Function FieldType(v_fldtype As Integer) As String

10  On Error GoTo Error_FieldType

20  Select Case v_fldtype
    Case dbBoolean
30      FieldType = "Boolean"
40  Case dbByte
50      FieldType = "Byte"
60  Case dbInteger
70      FieldType = "Integer"
80  Case dbLong
90      FieldType = "Long"
100 Case dbCurrency
110     FieldType = "Currency"
120 Case dbSingle
130     FieldType = "Single"
140 Case dbDouble
150     FieldType = "Double"
160 Case dbDate
170     FieldType = "Date"
180 Case dbText
190     FieldType = "Text"
200 Case dbLongBinary
210     FieldType = "LongBinary"
220 Case dbMemo
230     FieldType = "Memo"
240 Case dbGUID
250     FieldType = "GUID"
260 End Select

Exit_Error_Fieldtype:
270 Exit Function

Error_FieldType:
280 MsgBox Err.number & ": " & Err.Description
290 Resume Exit_Error_Fieldtype

End Function
 

isladogs

MVP / VIP
Local time
Today, 19:42
Joined
Jan 14, 2017
Messages
18,186
I haven't tested jdraw's code but my feeling is there are no field descriptions for action query fields.

Another way you can test without using code is by using the Database Documenter in Database Tools ribbon.
If you run this on a table or select query you will get the field descriptions where these have been set.

However if you try using any action query, far less information is supplied.
I'm fairly sure it just stores the action & not the underlying field descriptions
 

RonaldF

New member
Local time
Today, 20:42
Joined
May 12, 2016
Messages
7
Thank you so much for your very quick reply, Jack.

Unfortunately for tables as well as select queries, I already did almost exactly as you supposed.

My problem is with action queries, where I am not able to loop through the fields collection, since it seems to be empty.

Regards, Ron
 

RonaldF

New member
Local time
Today, 20:42
Joined
May 12, 2016
Messages
7
Dear Colin,
Thanks for your reply. This was exactly what I was afraid of. I hoped there was another way I was not familiar with.

Regards Ron
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:42
Joined
Jan 23, 2006
Messages
15,364
Ok, I was thinking table fields. And I realize the code was done many years ago, and not all enums have been included.

I agree with Colin, I think Access allows description of the query, but not individual fields of the query.
I suppose you could work from field names and relate to table fields as the best approximation.

I tried fields collection of an action query and it shows a fields.count = 0???
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:42
Joined
May 7, 2009
Messages
19,169
You can't with action query even using ADOX.
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:42
Joined
Jan 23, 2006
Messages
15,364
I remember working with some one on UA looking for query fields and aliases.
Don't know if it's helpful.

Here is the code;

Code:
'---------------------------------------------------------------------------------------
' Procedure : arge
' Author    : mellon
' Date      : 21-Jan-2018
' Purpose   :Trying to find fields and aliases used in queries.
'
'---------------------------------------------------------------------------------------
'
Sub arge()
    Dim db As DAO.Database
10  On Error GoTo arge_Error

20  Set db = CurrentDb
    Dim qdef As DAO.QueryDef
    Dim fld As DAO.Field
30  For Each qdef In db.QueryDefs

40      If qdef.name Like "a*" Then
50          Debug.Print qdef.name
60          For Each fld In qdef.fields
70              If fld.Properties("sourcefield").value <> fld.name Then
80                  Debug.Print vbTab & "table:" & fld.Properties("SourceTable").value & " ---Field:" & fld.Properties("sourcefield").value & "    Alias: " & fld.name
90              End If
100         Next fld
110     End If
120 Next qdef

arge_Exit:
130 Exit Sub

arge_Error:
140 MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure arge of Module UtterAccessRelated"
150 Resume arge_Exit
End Sub
 

RonaldF

New member
Local time
Today, 20:42
Joined
May 12, 2016
Messages
7
Thank you all for your replies. Looks like I have to live -;) with the fact that it is not possible to get the field description of action queries in vba
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:42
Joined
Feb 28, 2001
Messages
27,001
This is a corollary of the Old Programmer's Rule #2 - Access can't tell you anything you didn't tell it first. Is there even a place for a description for the fields of an action query? I only know of a couple of ways to build them - query grid or direct SQL - and neither one has a place for a per-field comment, or at least if so I don't recall seeing it. No place to store it would mean no place to read it.
 

RonaldF

New member
Local time
Today, 20:42
Joined
May 12, 2016
Messages
7
That's exactly why i thought it should be possible to get the description of a queryfield in an actionquery in VBA: it ís possible to put in a description in the query grid (see attached picture). On the right you see the properties window ('Eigenschappenvenster' in Dutch). Right on top there is the place to wright down a description for the field. Description in Dutch is 'Beschrijving'.
 

Attachments

  • UpdateQuery.zip
    127.3 KB · Views: 50

isladogs

MVP / VIP
Local time
Today, 19:42
Joined
Jan 14, 2017
Messages
18,186
That's indeed true and if you use the database documenter the description is being saved ... somewhere.
In my previous answer, I forgot that the DD will only list properties which exist.

But whilst setting a table or field description is often useful, I'm not sure I see the point of this info in an action query.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:42
Joined
Feb 28, 2001
Messages
27,001
OK, I have researched this a bit more. In list of the basic "Fields" properties exposed by Object Browser, there is no description field.

Traipsing around in the definitions, though, there is apparently the possibility of other properties being definable. I can see in my Ac2010 on my home system that there is, indeed, a slot for descriptions that seems to be associated with a field rather than the query as a whole. But using Object Browser, I can't see the slot where it would be retained.

As ridders points out, some properties are displayed if and only if they exist. This is of course due to the Access "sparse data" paradigms. They don't even have a slot for stuff they weren't going to define. They leave it to the app designer to add in whatever extra value is to be added.

I therefore think that part of your solution must be that for each field in the query, you would have to run through (enumerate) a list of propertes using a For Each type of loop to discover where such a property had been entered for each field.

Here is the catch: If any property such as "Descr" or "Description" does not exist, you will take a trap while probing for that property. Therefore you need to scan for it as though it were a dynamic property (which doesn't appear far from the truth).

Therefore, this might have to be treated as a nested For Each ... loop, where the outer loop is perhaps each query, the middle nest is each field in the query, and the inner nest would be each property of the field. The inner nest would be the thing that compares the property name to either "Descr" or "Description" (the two most common property names I could find via Object Browser). What else you do in each level of nesting is up to you.
 

isladogs

MVP / VIP
Local time
Today, 19:42
Joined
Jan 14, 2017
Messages
18,186
One possible complication that I forgot to mention in my last reply.
When I assigned a description to a field in an update query, I found the same description was added by Access to the table row for that field only.
That seemed odd so I checked the table the query was updating.
The description wasn't added to the field or table itself.

However the description was still there when I reopened the update query.
But here's the rub. It had been added to both field and table descriptions for each field in the query.
I then checked the DD again.
No properties were listed for any fields in the update query. No description or anything else.

In summary, I fear the description may be a chimera after all.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:42
Joined
Feb 28, 2001
Messages
27,001
Ridders, I also noticed when I was experimenting that there appeared to be a "bleed-through" of field properties from the table to a query. But as it happens, the example I had for my test example was a leftover from an older project of mine. The issue was really that it LOOKED like the description was coming through from the table. Except that the Field structure doesn't contain "Description" data. That is actually quite odd because now I have to wonder where it is kept for tables - and I know for a fact that you can have such things for fields in tables. But that means I don't really know the structure of a field as well as I thought I did.
 

static

Registered User.
Local time
Today, 19:42
Joined
Nov 2, 2015
Messages
823
It's in lvprop in msysobjects which is binary so you'd need to work out the format to do anything with it.

e.g.

Code:
Private Sub Command0_Click()
    Debug.Print getproptxt("table1")
    Debug.Print getproptxt("query3")
End Sub

Function getproptxt(obj As String) As String
    s = CurrentDb.OpenRecordset("select lvprop from msysobjects where name='" & obj & "'")(0)

    For Each c In s

        Select Case c
        Case 0
        Case 32, 97 To 122, 65 To 90, 48 To 57
            txtout = txtout & Chr(c)
        Case Else
            If Len(txtout) > 2 Then
                getproptxt = getproptxt & txtout & vbNewLine
                txtout = ""
            End If
        End Select
    Next
End Function
 

isladogs

MVP / VIP
Local time
Today, 19:42
Joined
Jan 14, 2017
Messages
18,186
Hi static

I've often wondered what the 4 lv fields in msysobjects were for

I tested your code on a table and select query to which I had added descriptions.
It certainly listed them but as part of a long list of properties.
For example for a table with 4 fields and descriptions on 3 of them:
I've marked the descriptions in RED

Code:
[B]?getproptxt("Table2")[/B]
MR2
GUID
ValidationRule
ValidationText
Orientation
Filter
OrderBy
OrderByOn
NameMap
DefaultView8DisplayViewsOnSharePointSite
TotalsRow
FilterOnLoad
OrderByOnLoad
HideNewField
BackTint
BackShade
ThemeFontIndex8AlternateBackThemeColorIndex
AlternateBackTint
AlternateBackShade0ReadOnlyWhenDisconnectedBDatasheetGridlinesThemeColorIndex8DatasheetForeThemeColorIndex
ColumnWidth
ColumnOrder
ColumnHidden
Description
Format
InputMask
Caption
DefaultValue
Required
AllowZeroLength
DisplayControl
IMEMode
IMESentenceMode
UnicodeCompression
SmartTags
TextAlign
AggregateType
Expression
ResultType
CurrencyLCID
DecimalPlaces
PublishToWeb
Amount
QEi
2m 
[COLOR="Red"]Amount field[/COLOR]
Color
JA 
m [COLOR="red"]Colour field[/COLOR]
Gender
aN 
m [COLOR="red"]Gender field[/COLOR]
IDP
BCa
jDu1
2Ua
jDu1
L6Table2
PBC
ajD
u1ID
aNa
jDu1Gender
JAa
jDu1Color
QEi
2aj
Du1Amount
BBB

A select query also gave a long list of properties including the descriptions.
So it finds those properties but buried amongst lots else
Not sure I'd want to use that method for those database objects

I then tried on an update query as the OP specifically asked about those.
As mentioned before, adding a description to an update query field also adds it to the table and the Update to value!
Also I had found after reopening the query all unassigned fields had been assigned the same property.
Anyway I ran your code but it did not find the descriptions added

However I noticed that both queries also had an entry in the MSysObjects LvExtra field. So I adapted your code to test that.
Result: no output for update query, no out put for select query based on MSysObjects, single value 'N50' for select query based on a table

The MSyObjects select query (only) also had an entry for Lv field ... so I tested that for completeness....

The output doesn't mean a thing to me...

Code:
?getproptxt("Query2")
BdS
DxM
xo 
MtM
MMM
MMM
M4Md
MDM
MMM
MML
MlMl
MlMl
MlMl
MlM
MMM
MM4
MdM
   
   
 MSysObjects
Name
MSysObjects
MSysObjects
Flags
MSysObjects
Type
MSysObjects
LvMSysObjects
LvExtra
MSysObjects
LvModule
MSysObjects
LvProp
MMD
MMt
MMM
MMM
MM4
MdM
MSysObjects
MlM
MTMt
MQuery2
MDM4
MMM
MDM
MLM
MTM
MML
MdM
DMM
MMM
MLM
MMM
4Md
MMM
MSysObjects
MlM
MtM
MM4
MMd
MMM
MMM
MMM
MLM
DMM
MMM
 tM4
MTMt
MMM
MMM
MMM
MMM
MMM
MMM
MMM
MMM
MMM
MMM
MMM
MMM
MMM
MMM
MMM
MMM
MMM
MMM
MMM
MMM
MMM
MMM
4MM
 4M
M 4
MM 
04M
M04
MM0
4MM
04M
MMz
MMM
lMl
MMM
MMM
MLM
MML
MMM
lMM
MML
MMM
DMd
MlMt
MMM
MlM
MMM
LMM
MMMSysObjects
MMParentIdName
IdM
MvM
MMM
qMM
MMM
MMM
MMM
MMM
MMM
MMM
MMM
MMM
MMM
MMM
MMMSysObjects
04M
M4M
MMl
MLM
MMM
MMM
MMMSysObjects
LvProp
MhM
MMM
MMd
MLM
4MM
MtM
MdM

As to whether this has helped anybody, I'm very doubtful.
Certainly I'm no wiser than before
 

static

Registered User.
Local time
Today, 19:42
Joined
Nov 2, 2015
Messages
823
I just wrote a simple function to extract text values.

There is probably some meaningful structure in there but I don't know what is/can't be bothered to work it out.

It's been a while since I used Access. As far as I recall, there are cases where table properties can be lost - when exporting to another db? - so I never used them.

I'd probably create my own table (tablename,fieldname,description) and look up the description rather than setting Access specific properties.
 

RonaldF

New member
Local time
Today, 20:42
Joined
May 12, 2016
Messages
7
Dear all,
Sorry i didn't reply earlier, I was away for a few days.

Wow, you all certainly are experts! And therefor way above my head ;-). Thanks for your input. A lot to think about.
Allthough I did not completely understand it, the code from Static worked fine (I can see the description i needed), but to be honest, I can not see a reliable way to parse it out in any circumstance; I would need a clou in what order the properties are exposed (or propertynames). I will dive into that later on.
Meanwhile, I decided to do it in a different way for the time being: I will place the fielddescription in the querydescription and parse it out there. It is not what i want, but satisfying for the moment.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:42
Joined
Feb 28, 2001
Messages
27,001
RonaldF, you will find that with Access, it is FREQUENTLY the case that you have to ADD what you want to see later because Access just doesn't have that property by default. I am not surprised at your workaround. In fact, I sort of expected something like that.

Welcome to the reality of Access, where you can do almost anything for a price - that price being LOTS of skull sweat. Hope you don't have incipient arthritis in your finger knuckles 'cause you might be doing a lot of typing.
 

Users who are viewing this thread

Top Bottom