Table of Field Names and associated mnemonics

[WilDeliver]

Apologies if previously asked; I have looked, but not found a (probably
obvious) solution:

Problem: I want to set up a table in a database which lists all the fields
in the database so I can map them to a mnemonic, category code, etc. For
example:

FieldName LocatedIn Mnemonic
OtherCodeNumber
tblFirstFieldName1 tblFirst FFN1
tblFirst101
tblFirstFieldName2 tblFirst FFN2
tblFirst102
tblSecondFieldName1 tblSecond SFN1
tblSecond101
tblSecondFieldName2 tblSecond SFN2
tblSecond102
tblThird

etc

How do I set up such a table so that it lists the FieldName from the names
of each of the other tables (vs. typing them in individually). Can this be
dynamic, such that if I change a Field Name it automatically updates in this
table?

Thank You

 

[Duane Hookom]

Take a look at Jeff Conrad's utility
http://home.bendbroadband.com/conradsystems/accessjunkie/csdtools.html.

 

[WilDeliver]

Thanks for the reference, which I've been using for documentation. However ...
I think I wasn't clear about what I needed. Sorry, I'll try again.

IS dept has named the variables we are tracking with a mnemonic ("DATAOLD")
based on their own (idiosyncratic, proprietary, non-Windows) system of
naming. I am developing an Access app to do stuff with those variables, but
I use a different (more logical) naming system.

I was hoping to store their name in my system so I could easily match them
and print out a mapping of my names to their names. e.g.:

fldSite-Name SITENAME
fldSite-City LOCATIONSITE
fldSite-DataLead EMPLOYEENUMBER

So, do I create a table listing all the mnemonics and insert it as a foreign
key in every table? Nah, that doesn't sound right. I'm so confused. Help
appreciated.
====================================================

 

[Duane Hookom]

Can't you just use a documenter to create a list of fields. Use this list to
create a table. Add a filed for the mnemonic.

BTW: I would never place a hyphen in a field (or other object name).
fldSite-Name reads to me like fldSite minus Name. I have recently read a
posting in a news group where this caused problems.

 

[WilDeliver]

Duane:

Thanks for your suggestions.

Unless I'm missing a setting somewhere, the Access documenter, like the
estimable CSD documenter produces a printed report of fields, etc., but not
an Access table which I can then modify. It's true that this can be printed
and then used to type the data into a table. However, this is (1) manual vs.
live (2) prone to errors in typing, etc. (3) not flexible when things need to
be added or changed.

I was hoping to produce a table which listed the fields in each of the other
tables in the database. I could then add a field for the mnemonics, change
them, update them, etc. Perhaps there is an add-in which does this.
Alternatively, it could probably be done in VBA, although I was hoping not to
get into programming (more a lack of time than interest and willingness).
Maybe I need to create an Excel spreadsheet and use it as a data source; I
have to investigate further, but I think this can be a "live" source of data,
which would facilitate changes and updating.

BTW, I see the issue with hypenated field names and have switched to
underscores as a separator (fldSite_Name rather than fldSite-Name). Easier
to read, too.

Thanks again,
Gary

 

[Duane Hookom]

The Access documenter does produce a table (doc_tblObjects). All you have to
do is find and link to it. The connection/link on my PC is "C:\Documents and
Settings\Duane\Application
Data\Microsoft\Access\ACWZUSRT.MDT;TABLE=doc_tblObjects".

Once you have a link to it, you can create a query
SELECT doc_tblObjects.Name AS TableName,
doc_tblObjects_1.Name AS FieldName,
doc_tblObjects_1.Extra2 AS FieldType,
doc_tblObjects_1.Extra3 AS FieldSize
FROM doc_tblObjects AS doc_tblObjects_1
INNER JOIN doc_tblObjects ON doc_tblObjects_1.ParentID =
doc_tblObjects.ID
WHERE doc_tblObjects_1.TypeID=11;

You can use this query to append to a local table or whatever.

 

[WilDeliver]

Duane: Here’s what I have found out.

1. Yes, you are correct and wizardly. The ACWZUSRT.MDT table is, indeed
created in just the place you said.

2. Searching Microsoft.com for ACWZUSRT does not produce anything at all.
Searching for MDT does lead to a number of responses which do not seem at a
quick glance to be that helpful.

3. At http://www.mcse.ms/archive148-2004-10-1137050.html I found out that
the ACWZUSRT.MDT file is a wizard application and a template to hold the
user's data when certain built-in functionality is used, such as the
Documenter.

4. At http://www.mcse.ms/archive152-2004-2-422577.html one Duane Hookom had
a response on just this very issue.

I tried to link from my database to ACWZUSRT.MDT via File/Get External
Data/Import and …/Link Tables, but it doesn’t show up. The MDT file
extension is not available in the dialog box. However, I clicked directly on
ACWZUSRT.MDT in Windows Explorer and it opened up another instance of Access
and allowed me to export the doc_tblObjects table to my own database.

The SQL statement worked just as you predicted. From the help file, I found
that in creating a join, Microsoft Access appends "_1" to the table name in
the second field list, a hint into HOW it works. At this point, I don’t know
enough SQL yet to truly understand it, much less to create it myself, but for
now I’m just happy that it works and I can move onward.

So in summary, I’ve got my table and can add my MNEMONIC field. It’s not
exactly “live†since I’d have to run the Documenter again and export that
file if (when) I make additions or changes. Alternatively, I can just
correct it by hand in my own DB. Either way, it’s a vast improvement over
doing it by hand. That’s 90% for now.

Thanks very much.

Gary

 

[Duane Hookom]

"I tried to link from my database to ACWZUSRT.MDT via File/Get External
Data/Import and ./Link Tables, but it doesn't show up. The MDT file
extension is not available in the dialog box. "

You can type
*.MDT
into the file name box and the "file of type" entry will be ignored.

 

[Jeff Conrad]

in message:

Unless I'm missing a setting somewhere, the Access documenter, like the
estimable CSD documenter...

Well thank you very much.

...produces a printed report of fields, etc., but not
an Access table which I can then modify.

Not entirely true. More further down.

It's true that this can be printed
and then used to type the data into a table. However, this is (1) manual vs.
live (2) prone to errors in typing, etc. (3) not flexible when things need to
be added or changed.

I was hoping to produce a table which listed the fields in each of the other
tables in the database. I could then add a field for the mnemonics, change
them, update them, etc. Perhaps there is an add-in which does this.
Alternatively, it could probably be done in VBA, although I was hoping not to
get into programming (more a lack of time than interest and willingness).
Maybe I need to create an Excel spreadsheet and use it as a data source; I
have to investigate further, but I think this can be a "live" source of data,
which would facilitate changes and updating.

Actually my add-in does produce a table of the information. You actually
have a couple of options if you are interested.

1. Run the add-in and select tables only on the first tab. When the report
opens up, hit the "Analyze with Excel" button on your toolbar. An Excel
file of the information will be created. It won't be beautiful, but you can
clean it up if you desire.

2. Run the add-in report as before. Then close the add-in. Now create a table
link by browsing to the CSDTools.MDA file (the location will vary depending
upon OS and Access version so you might want to run a search). Link
to this table:

zCSDTableQueryFields

All the information is there to do what you please. You can make a query with
just the information you need and then create a new local table based on
that query.

Hope that helps,