Overview - relationship diagram

[graveyard]

Hi people, one question. I know there is an option in access called "relationship" in which you can create the links of all the tables and queries etc

May i know is it possible for access to auto display the overall relationship flow chart and all the linkages btw tables, queries etc instead of us manually clicking and linking?

i have several database with kind of many tables, queries and it will really save me time if access is able to display out the entire "blueprint" layout instead of me manually creating the blueprint to explain to my colleagues who will be the end users.

Thanks!

 

[Jeanette Cunningham]

Check out this link to see if it does what you want.
http://www.allenbrowne.com/AppRelReport.html

 

[graveyard]

Check out this link to see if it does what you want.
http://www.allenbrowne.com/AppRelReport.html

hi jeanette, thank you for the link, Ive tried it but was returned a message (Compile error: Method or data method not found) when i tried the DEBUG command and compile the database. It came out at following part of code:

If fld.ValidationRule <> vbNullString Then
strOut = strOut & "V"
End If 'Default Value?
If fld.DefaultValue <> vbNullString Then
strOut = strOut & "D"

..........................


Could you pls shed some light on this. Thanks

 

[Jeanette Cunningham]

Here are the gotchas for that method.
Notes:

• In Access 2007 and later, this utility does not draw the tables correctly if they contain complex data types (attachments or multi-value fields.)
• Access 2007 cannot show the relationships for an ACCDB that contains tables with calculated fields, and this utility does not solve that incompatibility.

Do you have any of the field types listed above in the database?

 

[Jeanette Cunningham]

I just copied the code from Allen's website into an A2010 accdb.
I had no trouble compiling the code.
Check that you did copy the complete set of functions/ subs that Allen gave for the relationship diagram.

Are you using this code in an accdb format database?

If you still get the debug error, try deleting the module.
Create a new module, get Allen's code from his website again and paste into the new module.
If you still get the debug error, post back with the references you have set for the database.

 

[graveyard]

I just copied the code from Allen's website into an A2010 accdb.
I had no trouble compiling the code.
Check that you did copy the complete set of functions/ subs that Allen gave for the relationship diagram.

Are you using this code in an accdb format database?

If you still get the debug error, try deleting the module.
Create a new module, get Allen's code from his website again and paste into the new module.
If you still get the debug error, post back with the references you have set for the database.

hi jeanette, yes i am afraid i am still having the debug error. i am using the code in accdc format and in access 2007. i think the error is with below part (fld.Required)...

If fld.Required Then 'Required?
strOut = strOut & "R"
End If 'Validation Rule?
If fld.ValidationRule <> vbNullString Then
strOut = strOut & "V"
End If 'Default Value?
If fld.DefaultValue <> vbNullString Then
strOut = strOut & "D"

Sorry i am not sure wads the references for the database but my queries does contain quite a few calculated fields which cant be taken out of the database. Does this mean i cant use this code anymore?

 

[Jeanette Cunningham]

I can't reproduce the error you are getting. It all works fine for me.
When you get the debug error, that means that the code is not compiling (just for the record <smile>).

To find what references are set, in the code module, select Tools | References.
The references set will show at the top of the list with a check in the checkbox.
Are there any marked missing in your list?
Are there are references you are using that are not in the list below?

Here are the usual references for an accdb in A2007.
Visual Basic For Applications
Microsoft Access 12.0 Object Library
Microsoft Office 12.0 Access database engine Object Library

Calculated fields in a query would not cause a problem.
Is the backend database an access database (if you have a split database)?
Maybe you have some other type of linked table?

 

[graveyard]

I can't reproduce the error you are getting. It all works fine for me.
When you get the debug error, that means that the code is not compiling (just for the record <smile>).

To find what references are set, in the code module, select Tools | References.
The references set will show at the top of the list with a check in the checkbox.
Are there any marked missing in your list?
Are there are references you are using that are not in the list below?

Here are the usual references for an accdb in A2007.
Visual Basic For Applications
Microsoft Access 12.0 Object Library
Microsoft Office 12.0 Access database engine Object Library

Calculated fields in a query would not cause a problem.
Is the backend database an access database (if you have a split database)?
Maybe you have some other type of linked table?

hi jeannette, i have looked for the references and all those you have mentioned are ticked, so think no issue with that.

yes the backend database is an access database and no split database, the tables, queries are together in one single file

i have several linked excels, text files to a folder which i will update new data on a weekly basis so this feeds through the queries and reports, these will affect?

Sorry if it is not too much to ask for, could you kindly post a sample file with the codes in so i can roughly see wads the end results like? and prob i can compare that with my database to see what could be the reason why i am having debug errors and not you ...

 

[Jeanette Cunningham]

Try this. Make a copy of your database.
Working on the copy, delete the linked excels and text files.
Do a compact and repair.
Do a compile.
Run the relshp code.

If still getting the debug error, try the code in a brand new database.
In the brand new database, import just the access tables from your database where it won't work.
Now import the code module for the relship diagram.
Create a new blank form, put a button in. On the button's click event call the code to run the relshp report.

I have attached a screen print of the relshp report I get when I run the code in one of my apps.

 

[graveyard]

hello jeanette, thanks for your patience. i have done as per what you said but basically i am getting all the tables, queries without links (pls see attached) and there is a message that reads : Relationship Report (Adjusted). Skipped [table name], skipped ... (pls see attached)
and the report is as per screenshot, nothign shows up.

could you advise what could be wrong ?

 

[Jeanette Cunningham]

The code skips over any tables that are not in a relationship with any other tables.

I looked at your screenshot and I am not surprised that those tables were skipped. They are not the usual tables we include in relationships. Do you have any tables in the database that are related to other tables by primary keys and foreign keys?

You also must carefully check every page of the report generated, as some of the pages can be blank in between other pages that show relationships.

 

[graveyard]

hi jeannette, yes i do have tables that are related to one another. Not via primary keys for most tables but other fields that are not unique. i linked them in form of queries. pls see again screenshot - no links for all the tables, queries and same goes for the report view. I have looked through the report view and it is as per screenshot - no links at all

 

[Jeanette Cunningham]

hi jeannette, yes i do have tables that are related to one another. Not via primary keys for most tables but other fields that are not unique. i linked them in form of queries. pls see again screenshot - no links for all the tables, queries and same goes for the report view. I have looked through the report view and it is as per screenshot - no links at all

The code I suggested is made to pick up the primary key relationships between tables as you see when you open the relationships window in access.
It will not show the temporary relationships created in queries.

I'm sorry this has been such a frustrating exercise - as we have found - what I call 'relationships' and what you call 'relationships' are different animals completely.

Unfortunately I don't know of any way to automatically print a report showing the things that you call 'relationships' in your database.

 

[graveyard]

The code I suggested is made to pick up the primary key relationships between tables as you see when you open the relationships window in access.
It will not show the temporary relationships created in queries.

I'm sorry this has been such a frustrating exercise - as we have found - what I call 'relationships' and what you call 'relationships' are different animals completely.

Unfortunately I don't know of any way to automatically print a report showing the things that you call 'relationships' in your database.

hi jeanette, looks like i have been mistaken. no problem - thank you for your clarification on the code. though it doesnt apply to my problem, this is good knowledge and i will be able to use this next time to show the tables link in access

Appreciate your advice - cheers!