How-To: Use VBA code to fill in field name descriptions

[List Lurker]

[This followup was posted to microsoft.public.access.modulesdaovba and a copy was sent to the cited author.]

Hi All:

Am generating a plethora of subsets of some 300GB of data (yes, 300GB).

Being subsets, they all have the same 53 fields.

The client has asked to fill-in the field name deascriptions (in design
view) for all 53 fields.

Am much proficient in Excel, however, knowing that it's doable there,
I'm assuming there is way under Access.

I'm actually creating these Access MDBs from a SAS job, so what I'm
envisioning is to write a module in a text editor, and the open each
MDB, import the module, and run the code; i.e., it would be run from w/i
each individual MDB, such that the name of each MDB would not be
relevant (i.e,. the code would referece Active Table or something
equivalent to Excel's ActiveSheet).

I hope my plea is clear; thanks for any & all advice/help.

Later

 

[Allen Browne]

Yes, it is possible to programmatically set the Description property of each
field in each table.

You will need to loop through the TableDefs collection of CurrentDb,
visiting each Field in turn. Examine its Properties("Description"). The
property will not exist if it has never been set, so you need to
CreateProperty().

For an example of how to loop through the Fields of a TableDef and read the
Description, see:
http://allenbrowne.com/func-06.html

The following code shows how to set the property, creating it if necessary.
For example:
SetPropertyDAO(Currentdb.TableDefs("Table1").Fields("Field1"),
"Description", dbText, "My new description.")

Function SetPropertyDAO(obj As Object, strPropertyName As String, _
intType As Integer, varValue As Variant, Optional strErrMsg As String) As
Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.

If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
varValue)
End If
SetPropertyDAO = True

ExitHandler:
Exit Function

ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & _
" not set to " & varValue & ". Error " & Err.Number & " - " &
Err.Description & vbCrLf
Resume ExitHandler
End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

[This followup was posted to microsoft.public.access.modulesdaovba and a
copy was sent to the cited author.]

Hi All:

Am generating a plethora of subsets of some 300GB of data (yes, 300GB).

Being subsets, they all have the same 53 fields.

The client has asked to fill-in the field name deascriptions (in design
view) for all 53 fields.

Am much proficient in Excel, however, knowing that it's doable there,
I'm assuming there is way under Access.

I'm actually creating these Access MDBs from a SAS job, so what I'm
envisioning is to write a module in a text editor, and the open each
MDB, import the module, and run the code; i.e., it would be run from w/i
each individual MDB, such that the name of each MDB would not be
relevant (i.e,. the code would referece Active Table or something
equivalent to Excel's ActiveSheet).

I hope my plea is clear; thanks for any & all advice/help.

Later

 

[List Lurker]

Hi Allen:

Many thanks for such an erudite reply.

Couple of follow-up queries if I may, mainly b/c I think the structure
of my situation should afford an easier solution.

In particular, these numerous subsets of a 300GB dataset are all:

- virgin Access MDB's (again created as an export of SAS datasets)
- contain only a sinlge table, and as such, the "Description" property
will NOT have bee defined at all, for any fields ...
- all MDBs will have the exact same set of FIELDS (names)

Also, to make it easier coding, I plan on only providing DESCRIPTIONs
for selected FIELDs.

from your: http://allenbrowne.com/func-06.html

and the TableInfo() func:

I see that I can define my table(s) as:

Dim db As Database
Dim tdf As TableDef
Set db = DBEngine(0)(0)
Set tdf = db.TableDefs(5)

i.e., as the only table, it's the 6th one after all the system tables

(Note: the actual table name will vary across all of the muisc subsets)

Again, knowing that the DESCRIPTION property has NOT been set for any
fields in this table(s), can i simply go right to creating/defining the
DESCRIPTIONS for the selected FIELDs?

What I imagining is some sort of loop where as I pass through ALL the
fields in the one, know, data table, capture each field's name then
using some CASE logic, creat & assign the specific DESCRIPTION to the
appropriate FIELD.

e.g., something like:

For Each fld In tdf.Fields .....
fldNMTxt = Fld.Name
select case fldNMTxt "Paid_Date"
SetPropertyDAO ...
select case fldNMTxt "Co_Pay"
SetPropertyDAO ...


I know this wouldn't have any error-handling, and would be inelegant,
but is it doable?

If so and you're willing to provide a little more guidance, I'd be happy
to slug through further (of course fi you want to share a complete
Procedure, I wouldn't complain <lol>)

Again, thanks for any further help/advice,

Regards





[This followup was posted to microsoft.public.access.modulesdaovba and a
copy was sent to the cited author.]