Inherited Messy Access Database - lots of questions

[Judi]

I have inherited a database in Access 2003 and want to make it cleaner,
simpler to use, and generally better. I need to do a few things:

1) Find out which tables, reports and queries are still being used
2) Find out how these things are related, not just tables, but how things
are being referrenced by each other
3) Find redundancies in the information and fix them

Please keep in mind that I am a newbie who has just learned the basics
provided on the Microsoft Office tutorials... I don't know any programming
language.

My main question is how to find out how things are referrenced without
having to look up the dependencies on each one.

Please help me!!

 

[Brendan Reynolds]

I have inherited a database in Access 2003 and want to make it cleaner,
simpler to use, and generally better. I need to do a few things:

1) Find out which tables, reports and queries are still being used
2) Find out how these things are related, not just tables, but how things
are being referrenced by each other
3) Find redundancies in the information and fix them

Please keep in mind that I am a newbie who has just learned the basics
provided on the Microsoft Office tutorials... I don't know any programming
language.

My main question is how to find out how things are referrenced without
having to look up the dependencies on each one.

Please help me!!

Total Access Analyzer from FMS (www.fmsinc.com) can produce very
comprehensive reports. Find & Replace from Rick Fisher (www.rickworld.com)
includes a cross-reference feature (disabled in the shareware version,
enabled upon registration). It's not anywhere near as comprehensive as the
reports produced by the FMS tool, but its a lot cheaper.

 

[Judi]

I am not able to buy anything for this purpose, this company won't let me a)
but any programs and b) install any programs.

Is there no way within the program itself to do this??

 

[Jeff Boyce]

Judi

Jeff Conrad's site has a fairly extensive list of tools. Perhaps you can
find something you can use there ...

http://www.accessmvp.com/JConrad/accessjunkie/doctable.html

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Judi]

Thank you very much for your help and your time. I appreciate what you do here.

 

[Judi]

Thank you for your offer, but I won't be able to do that... I think I will
just keep learning this program myself. I can only learn more as I map this
out for myself.

Thank you for your time.

 

[Beetle]

Even if we ignore the fact that you shouldn't be trolling for work
in the forum, the OP clearly stated that;

I am not able to buy anything for this purpose, this company won't let me a)
but any programs and b) install any programs.

Now comes the part the part where I ask you to stop trolling, even though
I know I'm wasting my time by asking, since you ("Mark", "Steve",whatever)
have a long history of trolling, and obviously have no intention of stopping.

 

[Jeff Boyce]

These newsgroups are intended to provide volunteer (i.e., 'free') support.

By posting your offer to help "for a very reasonable fee", you are violating
the spirit of these newsgroups.

And if the inappropriateness of your offer doesn't matter, consider that
what you've proposed doesn't begin to cover what the original poster asked
for.

Please stop your spamming (from Wikipedia: "posting advertisements or
useless posts on a forum")

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Judi]

Or, if any one knows how to print the object dependencies sidebar, I'll do it
that way...

Thank you to all of you who are helping me.

<><

 

[Beetle]

Judi

You will find plenty of FREE help in this group when you have questions. Just
ignore "Mark".

 

[BruceM]

Not only are you a troll, you don't read very well. She doesn't want all of
the tables, only the ones that are in use. You are offering no more than is
already in the documenter. Also, she doesn't have a budget.
You already know it is inappropriate to solicit work here. Do you really
expect to escape notice when you do so?

 

[BruceM]

Have you taken a look at the Documenter? Tools >> Analyze >> Documenter. I
don't know if it will do what you need, but it can be a good starting place.

 

[Judi]

I did run it, but I think it's too much information for me. I'm a relative
newbie who has only gone through the most basic of educations on the subject.
I only opened the program for the first time a week ago.

I will take another look, maybe I can find the snippets I need in the vat
fabric of the Dcoumenter.

Thank you

 

[Steve]

Why don't you just *stay* away Steve ?? <Sigh>
Want us to hunt *All* your posts again ??

Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sellhis services.

To *anyone* here in this group:
Feel free to show the linkhttp://home.tiscali.nl/arracom/whoissteve.html
anytime this so-called Mark (=Steve Santus) advertises here.

Steve has been kicked out at Eartlink in the past.
He changed to SuperNews but was kicked out also I guess.

His current provider is GigaNews... Lets kick him out again !
Please complain at (e-mail address removed)
Read how to complain athttp://home.tiscali.nl/arracom/whoissteve.html

Arno R

This is not necessarily a quick way but the price is right

I'd basically start backwards.
Information coming out of Access is usually shown in the form of
Reports or Forms.

Using Reports as an example:

Open a report that you know you need, in design view. Click on the
square in the top left hand corner and click on Properties. This will
show you what Table or Query (a way of selecting and manipulating
data) is the source of the information.
You now know you need this table or query.
If it's a query, find and open the query in design view and see what
tables or queries it is based on. You now know you need all those.
Work your way through until you have identified all the components.

Also do a Relationships view and it will show you all Tables and how
they relate to each other.

Another approach (but see warning below) is to create a new blank
database. Starting backwards again, copy all reports you need to the
new database. When you try to open a report, it will tell you it is
missing a table or query. Copy that across and try again. Do the
same with forms and queries

Warning. A number of problems with this approach:
If a query relies on another query that hasnt been copied across,
missing references will be renamed and it wont work. Even copying the
missing query across later wont fix these renamed references

Secondly if you have macros or modules these wont be automatically
copied

Sorry its so laborious but as I said, price is right. Good luck

 

[Amy E. Baggott]

I'm actually trying to do the same thing, only with a database that I built,
but have been adding things to as I am asked to do more stuff. We are
planning to move it to SQL server, so I need to clean it up and document it.
What I ended up doing was to create a blank database, then import the tables
I know are being used. Then I started by importing the modules and trying to
run each procedure in each module (note that if a procedure has an "on error"
line in it, put an apostrophe in front of it for debugging). When it bailed,
I looked at the line where it failed to find out what I needed and imported
that. I repeated for each procedure until none of them bailed, then started
on the forms I knew were being used, then the reports. Since you are new,
you may need to survey your users and ask them to show you what they are
doing in the database to determine what elements you need to start with.
Keep notes as you go of what objects do what. Once you've got everything in
place so that nothing bails, you can start looking at your data to determine
where information may be being duplicated and start cleaning that up. Visio
(if you have it) is good for diagramming, but in a pinch, you can use the
flowchart template in Excel (it's just a bit clunkier). That's the stage I'm
at right now.

Once you have the database ready, you may have to use some import queries to
bring the updated data into your tables before you take it live. When you
do, save the old database somewhere you can find it againl; that way if
there's anything you left out, you can import it from the old database
without reinventing the wheel.

Hope this helps.

 

[Judi]

Thank you, this is a good idea, I will have to check with my boss to see if I
can do this.

I'm at the stage of asking users what they do in the Database... it's
tedious because everyone is too busy to talk to me.

Anyway, thanks to everyone who helped me out on this.

Judi <><

 

[John W. Vinson]

I'm at the stage of asking users what they do in the Database... it's
tedious because everyone is too busy to talk to me.

Try putting in a Startup form with a red background and a label in 48 point
type saying "This database will be upgraded, with or without your input. If
this concerns you call Judi at extension xxxx. Click the Continue button to
keep working... for the time being!"

<g>