Need advice on understanding a 3rd party database

[sync]

I have to create reports from an Access database used in a commercial application. The backend uses Access but the front end does not.

The vendor does not document the data or provide any support for that. The database has over one hundred tables and thousands of fields.

I'm thinking I'll create small transactions in the application and then study the database to see what has changed. But this seems overwhelming considering how many tables and fields there are. So I'm looking for advice on how to approach this.

I have a FoxPro background but I'm new to Access.

 

[Pat Hartman]

This isn't an Access problem. The problem would be the same if the tables were DB2. Hopefully, relationships are defined between tables and the table and column names are meaningful.

Start by looking at the relationship diagram.

You might want to consider investing in Total Access Analyzer. It is a pretty good documentation tool although since all you have are tables, it won't be as useful as it might be if you were documenting a complete Access application. (www.fmsinc.com)

There are several posts here (within the past month I think) that contain the code for reading the tabledefs collection if you want to try documenting the database yourself.

 

[sync]

What I'm looking for is a tool like Total Access Detective which will show me where data is being changed. But they don't have a trial and I can't tell from their online demo if it meets my needs.

I tried CDB Comparator, which is great. But it's $250 and I can't justify the cost.

I also tried CompareDataWiz and Compare Database but they both fall short.

So I'm looking for another program or a way to do this on my own.

 

[Pat Hartman]

What I'm looking for is a tool like Total Access Detective which will show me where data is being changed.

There is no way to determine this by simply examining tables. You would need to analyze the application. Tables don't change data. Applications change data.

Are relationships defined?
Can you identify which tables are related based on their primary and foreign key names?
Are the column names descriptive or cryptic?

 

[sync]

There is no way to determine this by simply examining tables. You would need to analyze the application. Tables don't change data. Applications change data.

My idea is to save a copy of the database. Then I would perform an operation in the application. Then I would use a tool to compare the two instances of the database to see what has changed. It's the best way I can think of to figure this out.