Table documentation

[mdecuir]

I am trying to find information about the fields, indexes and properties of all of the tables stored in an Access 2003 database. The database documenter provides all of the information I am after, however, I really would like to get the information it provides in a table format.

I have taken a look at the system tables, but the system tables I can find do not contain nearly as much information as I am looking for. (ie the fields in a table and their properties)

I have started trying to generate the tables I am after through vba, but just getting started on this process is proving to be a very complicated and time consuming process.

My hope is that tables exist which contain the information available through vba, however, I have been unable to find them.

Does anyone know if such tables exist and if so, what they are called and where I could find them? If not, do you know of any more effective methods of collecting this information into tables than iterating through the properties of each table via vba?

 

[vbaInet]

Welcome to AWF

You would need VBA for getting those properties. You would have to go through the definitions (to be precise, the TableDefs) in the CurrentDb object to get that sort of information in code and for each loop you INSERT into a table.

 

[mdecuir]

Thanks for the quick response.

That is the answer I was hoping was not coming, but nice to at least hear conclusively that I was on the right path.

I have been reading these forums for at least 6 months or so, but this is the first time I came across a problem where I could not find a satisfactory answer in documentation or my search through these forums.

I guess I just have to head back to writing code to iterate through all of these properties.

 

[vbaInet]

I think it's more of a security measure not to have all the properties and indexes in a table where it can be accessed. Remember you can import tables and anyone with mailicious intent could easily get the properties with great ease.

Your table would end up as a flat file so I suppose it would be best to save the table name, the colum name and index property each in a variable, then insert the values of these variables into the table.

Have fun coding

 

[mdecuir]

It would be fantastic even if a read-only version of those properties existed.

I am trying to break things down as cleanly as I can for generating these tables. I will gladly post the scripts that I come up with once I have them finished. The big challenge I am running into is with how much normalization I need to apply to the output tables.

The properties collection for a tabledef, field, or index object seems to contain a variable number of objects, with variable data types. As such, I see two possible approaches for the table structure with neither leaving me very happy.

I can either have the object, property, data type and value of the property as one table and attempt to map them all of the values to a single datatype (which creates problems of its own), or I can attempt to have a single entry for each object and have a different field for each property with the appropriate data type and try to find a way of dealing with the fact that I don't know all of the possible properties that will exist.

 

[vbaInet]

It would be fantastic even if a read-only version of those properties existed.

Show System Objects in Navigation Options and look through the tables, you wouldn't find any useful information. Although there are some system tables that would show the tables but not much information.

I am trying to break things down as cleanly as I can for generating these tables. I will gladly post the scripts that I come up with once I have them finished. The big challenge I am running into is with how much normalization I need to apply to the output tables.

Just have one table and repeat the table name and field names data for each field's properties. You can always create a query to extract only relevant bits of information or use code based on the query.

The properties collection for a tabledef, field, or index object seems to contain a variable number of objects, with variable data types. As such, I see two possible approaches for the table structure with neither leaving me very happy.

You can map it using another function that have as it's paramters the field property and value to be looked up. You use a SELECT CASE statement and if it's for example an Integer which I think is 1 (not sure) you just return the name Integer via the function.

I can either have the object, property, data type and value of the property as one table and attempt to map them all of the values to a single datatype (which creates problems of its own), or I can attempt to have a single entry for each object and have a different field for each property with the appropriate data type and try to find a way of dealing with the fact that I don't know all of the possible properties that will exist.

I think you should just concentrate on relevant properties that mean something to you.

 

[mdecuir]

Thanks again for all of the advice

I found the system objects before and they had a small amount of the information I wanted, but not nearly everything I was after.

I likely will have to switch to just the properties that mean something to me, but at the moment I am not entirely sure what really has meaning to me. From the sound of things, I will need to sit down and think hard about what I am really trying to figure out from these properties.

The one property that probably does not exist I am hoping I can find is the date that the data in the table was last modified or even better, accessed. The database has a lot of objects that were made for some purpose and abandoned or never finished and no one has ever gone and figured out what is actually used and what isn't.

My ultimate goal is to do this cleanup and document what remains, but I am not sure of the path to take and so I am just trying to find or build tools that strike me as likely being useful in accomplishing that goal.

 

[vbaInet]

I would say before writing code to insert into a table, create a function to print some of the properties to the Immediate Window using Debug.Print with vbTab so you have it each property on one line. By that you can tell which properties you need. Thinking about it, with vbTab you may even be able to copy from the Immediate Window onto Excel or a table.

I don't think the date per record is saved.

Here's what I found by doing a quick google search. It may interest you:

http://www.fmsinc.com/Products/analyzer/compare.htm

 

[mdecuir]

I actually found that product on a google search earlier. The unfortunate thing from my end is that I am at best a long ways off from convincing my company that spending any money on this beyond my spare time is worth the investment.

I hadn't noticed the download trial link when I first saw the page, however, so I may have to fiddle with that and hopefully convince my company to spend some money on useful tools.

Thanks for the link.

 

[vbaInet]

You're welcome. Good luck on the convincing part

 

[jdraw]

Thanks again for all of the advice

I found the system objects before and they had a small amount of the information I wanted, but not nearly everything I was after.

I likely will have to switch to just the properties that mean something to me, but at the moment I am not entirely sure what really has meaning to me. From the sound of things, I will need to sit down and think hard about what I am really trying to figure out from these properties.

The one property that probably does not exist I am hoping I can find is the date that the data in the table was last modified or even better, accessed. The database has a lot of objects that were made for some purpose and abandoned or never finished and no one has ever gone and figured out what is actually used and what isn't.

My ultimate goal is to do this cleanup and document what remains, but I am not sure of the path to take and so I am just trying to find or build tools that strike me as likely being useful in accomplishing that goal.

mdecuir,

I have 2 routines that I use frequently with Acc2003. One is a table reader/documenter that creates a table called "Data_dictionary". The other is a Index reviewer that records information about Table indexes into a table called "Tables_indexes".
These procedures delete and recreate the tables dynamically and populate them completely each time they are executed.

They are quite basic, but may assist you until a decision on the commercial product is made. The procedures may give you some base from which to adjust them to better suit your requirements.

You can download these at
http://www.4shared.com/file/244311775/b33aeac9/DataDictionary_Module.html

'---------------------------------------------------------------------------------------
' Procedure : DocumentTables
' DateTime : 2006-06-09 09:29
' Author : drawbrij
' Purpose : To create documentation for all tables in this database
' where tableName <>MSys*
'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


'---------------------------------------------------------------------------------------
' Procedure : fjListIndexesToTable
' Author : drawbrij
' Date : 2/13/2009
' Purpose : To put the indexes of all tables in this database into a table.
' The table is Table_indexes. It is created dynamically when this procedure
' is executed. The table structure is as follows:
' table_name varchar(250),
' Index_name varchar(255),
' field_name varchar(250),
' primary_key varchar(10)
' unique_values varchar(10),
' ignoreNulls varchar(10));
'---------------------------------------------------------------------------------------