Any tips on working with a database that you did not create?

  • Thread starter Thread starter KrispyData
  • Start date Start date
K

KrispyData

What is the best way to understand the tables,queries, relationships,etc. I
am new to Access and taking over an entire database.
 
KrispyData said:
What is the best way to understand the tables,queries, relationships,etc. I
am new to Access and taking over an entire database.
Click to expand...

This can be an absolute disaster - cost me a job once. I was given what
looked like a "nearly finished" database which apparently represented a
great deal of work. In fact it had been thrown together using wizards
(look out for the wizards) based on a wholly wrong table structure. I
was new to Access too, and intimidated by the burden of expectations.
Here are some of the things I should have done:

Get up to speed on Access, first. You can get really good online video
training at lynda.com for Access 2003 or 2007, and there are numerous
books. Concentrate on tables first/most - understand what
"normalisation" is (but don't get too bogged down when people start
listing "normal forms"). Have a look at this video (a sampler from
lynda.com):
http://www.lynda.com/home/Player.aspx?lpk4=31001

Have a look at the Northwind sample database (if installed, it's under
Help). I'm still learning things from that!

Back up your database first (there's a handy command on the file menu).
Work on copies. Check to see if the database has "linked tables" - a
common (and desirable) way of building a database is to put the tables
in one file and all the queries, forms and reports in another, and link
the two. Look up "Linked Table Manager" in Help. Have a look at the
"Relationships" window - don't be afraid to move things around for
clarity (you're working on a fresh copy each time, right?).
Double-click on the connecting lines to see the relationship's properties.

Look through the queries and spot whether any of them are anything other
than SELECT queries (you can have UPDATE, INSERT, MAKE-TABLE queries for
example).

Look next at the forms. Bring up the Properties sheet and look for the
Record Source (usually a query, but can be a table, or it may be
"unbound"). Look at the buttons and other controls - look out for
"Event Procedures" which can run macros or Code Modules (better) which
might be doing all sorts of unexpected things. Every form can have a
"module" behind it - don't miss these. Usually written in VBA, but can
be .NET languages (although I've never used these in Access). Are there
subforms?

Look at the reports. Look at the Record Source to see what data they
are presenting. Are there Grouping levels? Subreports? How are the
reports invoked?

Are there any of the more Access-specific facilities in use? Security
workgroups? A Switchboard? Replication?

Then try to understand what real-world activities are being supported by
the database as a whole. Ordering something? Booking a room? Try to
follow these processes through.

Then, crucially, don't be afraid to change things, and don't be afraid
to think the unthinkable - that what you've inherited has design flaws.
Design your own set of tables, and set up relationships between them.
Compare with what has been inherited. Can you build your own versions
of key forms on top of your own tables and queries? Your own reports?
The various wizards give you a great start - it was years before I
created a report in Design View without running a Wizard and fiddling
with the results, and I still often run the Wizard first to get me
started. Use these wizards to see what you can do yourself from
scratch, and then go back and see what your predecessor did.

Finally, manage expectations. Can you get some classroom training, or
at least time "off" to study? Make it known that Access is a
sophisticated and complex product that can't be mastered overnight, and
that taking over someone else's work can be a much harder route than
building progressively bigger things from scratch. If you can't get
some understanding of this, consider walking away.

However, this could be a great opportunity. Access is wonderfully
powerful once you've "got it", and it's addictive. You can build really
useful things relatively quickly once you've got it under your belt, and
you may find you're taking over from a real pro. Don't assume that, though!

Ask here for specifics when you need them. This newsgroup has the best
collection of real experts (patient, helpful ones) you'll find anywhere.
Clear questions get clear answers...

Good luck,

Phil, London
 
Make a copy of the database so if you screw up you dont trash the original.
There is no shortcut to learning Access. To expect a beginner to maintain a
complex database is unrealistic. Of course it depends on how complex it is.
Do you know VB and SQL?
I'd learm how to use the VB debugger as it can be a huge help in tracing
problems.
Learn how to use the Access Help system which is a mountain of helpful
information.
Learn how to use the Object browser.
Look at the recent thread in 'Programming' for beginner tips and resources.
Good luck!
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Whether you do the work yourself, or end up hiring someone to help, there
are four learning curves you'll need to work your way up...

1. Normalization -- Access 'expects' well-normalized data
2. Tips/Tricks -- Access works differently that Excel or FileMaker or ...
3. User Interface -- <Click here for Yes> -- <Did you really mean Yes?> --
<OK, I'll do it if you say 'Yes' again>
4. Application development

Knowing something about all of these will help make your project a success.
Knowing nothing about any of these (and not bothering to learn) will ensure
that your project takes a long time, ends up way over budget, and satisfies
no one. Pay now or pay later!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
That would be "works differently THAN Excel or ..."

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Everyone, Thank You. Comments were very encouraging and frightening at the
same time!

Phil, thanks for your section about managing expectations!

You all rock!
 
KrispyData said:
Everyone, Thank You. Comments were very encouraging and frightening at the
same time!

Phil, thanks for your section about managing expectations!

You all rock!
Click to expand...

I expect we'll be hearing from you again shortly...

Phil
 
A little fear is a good thing!

If you have the time, by all means, start climbing up the curves. And we'll
expect to hear back here how it's going.

If you don't have the time to DIY, make sure the person you hire has climbed
those!

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Philip Herlihy said:
This can be an absolute disaster - cost me a job once.
Click to expand...

Excellent reply. Blogged at
http://msmvps.com/blogs/access/arch...and-the-tables-queries-relationships-etc.aspx

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
KrispyData said:
What is the best way to understand the tables,queries, relationships,etc. I
am new to Access and taking over an entire database.
Click to expand...

To add to the other suggestions. If there are no tables in the
relationships window of the data MDB then it's highly unlikely that
the person knew what they were doing.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
These newsgroups are a "volunteer" effort, and the expectation is that you
can ask for and receive free ideas, without being subject to solicitation.

If someone uses these newsgroups to troll for work, ask yourself if you wish
to hire a person who doesn't respect and follow the guidelines laid out for
participation in these groups?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Steve said:
I provide help with Access applications for a reasonable fee. If you want
help with your database, contact me.

Steve
Click to expand...

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the Christmas
holidays to show Stevie's "expertise" in Word.


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)
Yes, you are right but a database is the correct tool to use not a
spreadsheet.
Click to expand...


Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...




John... Visio MVP
 
Bottom line is that it's usually harder to take over somebody else's work
than to create it yourself. So, combining that with being new is a rough
combination. If the old work is junk (for example, with a poor table
structure or no data definitions) then you might not have the ability to
identify it as junk or the confidence to tell everybody else that it is.
 
Philip Herlihy said:
Thanks! An expert is someone who has already made most of the available
mistakes. My productivity in that direction is undiminished!
Click to expand...

<chuckle>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Back
Top