UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Do You Document And Tidy Your Databases?, Access 2016    
 
   
Digmen1
post Dec 22 2017, 11:31 PM
Post#1



Posts: 352
Joined: 14-April 02
From: New Zealand


Hello
I have lots of Access Databases that I use for my own personal topics.
HIstory, movies, expenditure, contacts etc.

One of my databases is very large and I would like to tidy it up.
It has lots of tables (20+), lots of forms (30+), lots of queries (80+) and lots of reports (60+)

I am thinking of setting up a table to keep track which forms, and reports and querries work together, so that I can see if some are redundant.

Do some of you have this problem?
And if so what are your techniques?

Or is it just silly old me?

--------------------
Regards
Digby
NZ
Go to the top of the page
 
DanielPineault
post Dec 23 2017, 06:44 AM
Post#2


UtterAccess VIP
Posts: 5,962
Joined: 30-June 11



Have you look at the built-in Documenter (Database Tools -> Database Documenter)?
You may also want to look over: https://github.com/strive4peace/Analyzer as it might offer some insight.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
projecttoday
post Dec 23 2017, 07:19 AM
Post#3


UtterAccess VIP
Posts: 9,827
Joined: 10-February 04
From: South Charleston, WV


Some databases do need to be tidied up and there's nothing silly about that.

The type of metric you suggest may very well help, but you may need to analyze what's going on with 20 tables, 30 forms, 80 queries, and 60 reports from a design standpoint.

It does sound like an excessive amount of objects but it's impossible to say for sure without more information.

Did you create this database? What is your skill level with database design? What does the database do?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
orange999
post Dec 23 2017, 09:35 AM
Post#4



Posts: 1,794
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


@Digby,

Here's an article that may help with setting up your database documentation. Overviews the what, who, where, when.....

--------------------
Good luck with your project!
Go to the top of the page
 
DanielPineault
post Dec 23 2017, 01:56 PM
Post#5


UtterAccess VIP
Posts: 5,962
Joined: 30-June 11



One more thing, always make a backup prior to trying to clean things up! That way if ever things get out of hand, you can easily restore things.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
jleach
post Dec 24 2017, 07:52 AM
Post#6


UtterAccess Editor
Posts: 9,922
Joined: 7-December 09
From: Staten Island, NY, USA


Project documentation is a big task, and there's usually multiple facets to it.

Database documention (the actual database itself). I usually go with a nice ERD diagram for this: it shows the tables, the keys, whether it's nullable, the indexes, etc. Access doesn't have a nice one built in, but on our SQL Server backends we use dbForge to do this as they have ERD blocks that contain all of the above. We need all that at a glance, otherwise we have to go digging when we want a reference to something. This is really nice because not only do we a get the relationship diagram to see how the tables are working together, but we have a quickref for all the table/field level information as well. dbForge also lets us throw sticky notes in the diagram, which is particularly useful for enum values. Here's an example:

Attached File  genrev_erd_public.png ( 164.04K )Number of downloads: 5



The issue here is that only gives us one layer. Depending on the complexity of the project we might do a functional documentation (describing what the system does, how it does it, in general terms), and sometimes workflow diagrams (example below). This helps us keep straight what needs to go where and also allows us to match things up to the "core" codebase fairly easily without having to fully document the codebase itself (the two compliment each other nicely).

Attached File  flowchart.png ( 1.8MB )Number of downloads: 5


Another thing we'll do is a flow of screens. Attached is an example of an app we're starting that I had handy, but there's no reason you can't do the same with Access forms as well. This is helpful to make sure all of the functionality workflows are being handled, and very helpful in allow us to find dead forms that don't need to be there anymore, etc. This is probably the most helpful from your case, as you can start at the home screen, map out everything you're using, and easily identify what you're not using. From there you can drop into your codebase and tables/queries to find ones that aren't being used.

Attached File  Screenshot_2017_12_24_07.50.05.png ( 76.33K )Number of downloads: 6

(pencil and blank paper is really good for this also!)


There's two general issues with documentation at any level:

1) It takes time to do it.
2) It takes time and discipline to keep it maintained.

Maintained documentation is a treat in any system. Usually the best intentions for good documentation don't make it past the initial/MVP release, if people ever wanted to start with it at all.

hth

--------------------
Go to the top of the page
 
Digmen1
post Dec 26 2017, 01:49 AM
Post#7



Posts: 352
Joined: 14-April 02
From: New Zealand


Thanks for all the replies.

Its a big job and it looks like many of you (us) do not keep our documentation up to date.

For my big database I think I will set up a few tables to keep track of all my queries and reports. So I can see which ones are being used, and which ones are now redundant.



--------------------
Regards
Digby
NZ
Go to the top of the page
 
jleach
post Dec 26 2017, 06:48 AM
Post#8


UtterAccess Editor
Posts: 9,922
Joined: 7-December 09
From: Staten Island, NY, USA


Just out of curiosity, why tables? Seems like a sub-optimal way to map out usage, but I guess if you wanted to programmatically track usage (put a counter increment in each Open even, for example), that'd be a good use for them.

Also, check the MSys tables, which already have lists of all your object: if you want to track additional information about your objects, you could make do an extension table (1:1) and keep from having to enter all of them yourself. You can't manipulate the existing system tables, but you can join them.

Cheers,

--------------------
Go to the top of the page
 
GroverParkGeorge
post Dec 26 2017, 09:15 AM
Post#9


UA Admin
Posts: 33,034
Joined: 20-June 02
From: Newcastle, WA


I think it's a bit of an exaggeration to suggest we don't keep documentation up to date. It's more along the lines of believing that the effort to do what you describe is not terribly productive.

If you are taking over responsibility for a particular accdb, you will probably get a lot of benefit from documentation it as your first step. And, perhaps there are other check points where an update to documentation would be useful, say when you undertake a major revision of an existing application. On the other hand, monthly, weekly or other frequent updates to documentation in day to day use seems like it might not actually be all that worthwhile.

As Jack pointed out, if you want to do a longitudinal study of usage, that is a different task and possibly worth doing if you have a specific plan to use that data. Perhaps you are looking for the most frequently used forms to decide which ones need to be modified first in an overhaul.

Otherwise, what would you do with that information?

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th July 2018 - 04:59 AM