Access Database Analyser? Pref. Free & Standalone

  Radix Lecti 08:16 04 Apr 2007
Locked

Hi all, I've been tasked with getting my head around a monster departmental finance database, but it's so complex, it could take me an age get any level of understanding!

I'm looking for a freeware (ideally standalone) tool, which can analyse the database and output some form of visual model if it's workings and relationships etc.

I've tried to use the built in Documenter, but it's not working on this DB, and even when it does work, it's not at all comprehensive.

I know these tools are out there, but my search isn't going well :(

Your suggestions would be most welcome.

P.s. I don't have access to MS Visio Pro, which can apparently do this.

  silverous 09:25 04 Apr 2007

There might be something out there but if this is key to the company I think you'd be better off spending a modest amount of money on something up to the job.

If there are less than 25 entities the free version of this tool looks like it might do it:

click here

I googled "reverse engineer access database" - have a look through the results.

  Radix Lecti 09:42 04 Apr 2007

Thanks for that Silverous.

My problem is, that there's no budget for this, and most commercial tools require installation.

Our systems are locked down though, and any software has to be audited, tested and approved before they will even consider adding it to the company wide approved software list.

I will keep searching.

  silverous 10:45 04 Apr 2007

Sounds like you are stuck then - if the systems are locked down how are you going to install anything!?

I think a pen and paper, some coffee and some clear time are needed!

  Radix Lecti 11:08 04 Apr 2007

Well, if I could find a standalone tool (that doesn't require instal) that would be good.

Doing this manually, based on the work a colleague did on a database a quarter of the size) is going to take approximately 3 weeks. I have a week to do this and document everything, gain a full understanding of how everything's working, and implement changes.

I think I'm gonna need more than coffee!

Anyhoo, thanks so much for your time and suggestions. Much appreciated.

  silverous 11:11 04 Apr 2007

How about copying the database and taking it home / using a laptop? Don't tell me....that break's company policy/no mechanism to get the file home?!

I guess you don't really want to be doing this at home either.....

Sounds like you are in a difficult situation, wish I could help, good luck with it !

  Radix Lecti 11:43 04 Apr 2007

Bang on! Violation of Data Protection Policy.

And bang on again, I don't want to be diong this at home :)

  skeletal 17:07 04 Apr 2007

If the db is anywhere near as complex as some of mine, you have a close to impossible job on your hands and you have my great sympathy! One that I did for a customer some time ago had over 400 pages of description; and you have been asked to work stuff like this out in a week?!!

It should, of course, be fully documented by the designer; where is all that info??!!

However, to try to help a little...

I am surprised the documenter is not working; perhaps you can investigate that some more? What does it not do?

Failing you using the documenter, are you using Access 2003?

You may not realise it but this has a very handy feature of “Object dependencies”. For every Table/Query/Form etc, right click and near the bottom of the list you will see “Object dependencies”. Click on that and the side bar will show everything that object “depends on me” or “objects I depend on”.

You could go through all of those, and together with looking at relationships (relationships button) to start building a picture.

Is there any VBA involved? This is what makes Access so powerful, but to unravel other’s code is REALLY hard unless it is well documented.

Similarly with the “poor mans” code: macros.

Skeletal

  interzone55 17:24 04 Apr 2007

added to my postings to follow this thread...

  Radix Lecti 07:57 05 Apr 2007

Thanks skeletal.

Yes, it really is a mammoth job at hand.

I started working my way through it manually yesterday and now I think my brain is melting.

Unfortunately, the designer(s) didn't even consider documenting their design (as it was done in several bursts of hurriedness (is that a word?), and so even working out what each field IS, is proving to be tough enough!

I'm not sure why documenter isn't working, but I've tested it on a few machines and have had other colleagues do the same. They're all gettign the same result too. I've raised a ticket with our help desk, but the estimated fix time is between 3 and 5 weeks (ha!).

I'm using 2003, but for some reason it won't run properly because the database is still linking to some other DB's residing on an ex-colleagues peronal drive, and I can't gain access to them. This causes the dependencies tool to fall over every time I run it! Doh!

I have, on the up side, found a java tool called SchemaSpy, but after having worked out it's command line switches, it won't run!

As a final rotten cherry on the top of this root vegetable cake, there's plenty of VBA sitting behind this beast... and guess what?! Very few, mostly zero comments.

Same goes for the macros.

Looks like I'm doomed to suffer the tedious breaking down of a very boring Colossus.

Ah well, I'll let you know if I find another way.

  skeletal 09:50 05 Apr 2007

Oh dear!

If you have linked tables, you will have to get access to your colleague’s computer to see their structure; that is unless there is a copy somewhere else, even with obsolete data in them. If such a thing exists you could use the link table manager to switch to the new location.

Out of interest, I thought I’d fire up an old db I did that has linked files (linked incorrectly). It obviously didn’t work, but I was still able to use documenter, it just had less detail about the non existent tables.

When you say you tested documenter on a few machines, did you mean you tried to look at the rogue db on several machines, or that you used a working db on several machines? Where I’m heading is that is it the db that’s causing the problem, or has your company not installed documenter on any machine (although I thought it installed automatically to be honest). Are all the latest patches installed (my company often does not upgrade Office for some reason).

If you are allowed, you could email me the db (zip it first because my Outlook stops mdb files) and I could see what it does on my machine…no promises obviously!

I can’t comment on any other software to “look at” Access dbs as I have only ever used its built in functions.

Sorry if some of the above seems obvious, but you are being asked to do the impossible IMHO; perhaps a long chat with your boss may be in order!

Skeletal

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

Halloween Photoshop & Illustrator tutorials: 20 step-by-step guides to creating spook-tacular…

iPhone X news: Release date, price, new features & specs

Comment créer, modifier et réinitialiser un compte Apple ?