Print a list of table Field names

[access2010]

We are preparing to upgrade some of our MsAccess 2003 Databases and would like to review the field information being used in 2 fields.

Could I please receive a suggestion on how to print two objects within the table
Field Names
Data Types

Thank you. Crystal

 

[theDBguy]

Have you tried the Database Documenter?

Database Tools > Database Documenter

Click on the Options button to specify which attributes you want to include in the report.

 

[June7]

Review:
http://allenbrowne.com/func-06.html
https://www.utteraccess.com/topics/2066511/posts/2827647

 

[The_Doc_Man]

You can "roll your own" as shown in June7's two links, or you can use the database documenter as noted by theDBguy. Easiest would be the built-in documenter. Nothing to write, just click to select options. Personally I would do the documenter unless there is some outside issue that is driving your attention elsewhere.

 

[June7]

TMK, the documenter only goes to printer. So if you don't want to chew up paper, roll your own.

Or maybe first set default printer to MS PrintToPDF and maybe it will output to PDF.

 

[access2010]

Have you tried the Database Documenter?

Database Tools > Database Documenter

Click on the Options button to specify which attributes you want to include in the report.

Yes, TheDBguy. We did try your suggestion, but we received more information than we wanted.

 

[access2010]

Review:
http://allenbrowne.com/func-06.html
https://www.utteraccess.com/topics/2066511/posts/2827647

Thank you Jun7, I will give your suggestion a try

 

[access2010]

You can "roll your own" as shown in June7's two links, or you can use the database documenter as noted by theDBguy. Easiest would be the built-in documenter. Nothing to write, just click to select options. Personally I would do the documenter unless there is some outside issue that is driving your attention elsewhere.

Thanks Doc Man for your suggestion, but all we want to see are two fields.

 

[theDBguy]

Yes, TheDBguy. We did try your suggestion, but we received more information than we wanted.

That's even after you selected only the attributes you wanted in the the report from the Options dialog window?

 

[June7]

Documenter doesn't allow that level of selection. Get all fields. But can limit to Name, DataType, Size so maybe not so bad to pull all fields.

I just tested Documenter. I get a report so not direct to printer.

Even with limited output, a 6-field table produced 3-page report with a lot of details I didn't want. Oh, that included Properties. Change selection to "Names, DataTypes, Sizes" and the output is one short page listing all fields. Actually rather nice.

Should be able to open mdb in current Access version. I am using 2020.

Seems like the last time I tried this was with Access 2007 and went straight to printer and no selection options so used a ton of paper.

 

[DHookom]

Use the documenter which updates a table in the folder C:\Users\[Your Profile]\AppData\Roaming\Microsoft\Access\ACWZUSR12.ACCDU Since you have an MDB file, the extension will be different. You can search for file name by displaying hidden files in your Windows Explorer. You can link to the doc_tblObjects and create a query like:

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;

This will provide a datasheet like:

 

[June7]

I tried setting link to the accdu file but External Data Wizard does not show this file extension so the file does not show. I did confirm the file exists.
I used IN with the SQL and saved this query object.

SELECT * FROM doc_tblObjects IN 'C:\Users\Administrator\AppData\Roaming\Microsoft\Access\ACWZUSR12.ACCDU'

Then built another query using that as source so I could do the self-join and alias field names and filter.

SELECT Props_1.Name AS TableName, Props.Name AS FieldName, Props.Extra2 AS DataType, Props.Extra3 AS [Size]
FROM Props AS Props_1 INNER JOIN Props ON Props_1.ID = Props.ParentID WHERE Props.TypeID=11;

I suppose some VBA could set the table link.

Tested with a single-user non-split db that has local tables as well as linked SQLServer. Works on both.
I expect filepath will be different for each user of multi-user split db.

 

[DHookom]

When searching for the ACCDU file just enter *.* in the file box and all files will be displayed.

 

[June7]

Exactly which "file box" do you mean? I am navigating from External Data Wizard and I don't see where I can enter *.*.

However, I tried Wizard again and now I see the file. Odd I didn't see it before. I had already run the Documenter.

Either way, same output.

 

[Uncle Gizmo]

I've used Adams code here for several of my projects:-

Get Table Information

This was kinda fun to write, so I thought I'd post it here. The function creates a table to display information about user-created tables. It also uses a separate function to convert data type values to readable strings. It is not all-inclusive, but it converts most of the types you will...

www.access-programmers.co.uk

Turns out he is useful sometimes!!!

 

[DHookom]

Exactly which "file box" do you mean? I am navigating from External Data Wizard and I don't see where I can enter *.*.

However, I tried Wizard again and now I see the file. Odd I didn't see it before. I had already run the Documenter.

Either way, same output.

Apparently you found the file but for the record:

 

[June7]

I tried that and it did not work, even got error message so figured that was not the box.
I have closed and reopened database. Trying to link table and again it does not show. And again I confirmed in Windows Explorer the file is there. Oh wait, have to click Open button then it shows and can choose. Click Open again.
Got it.

 

[DHookom]

#June7, I assume you got the desired results. If not, please provide more feedback regarding your results and desired results.