Taking over an existing database project is much like designing one from scratch, except some of the work is done for you. Unfortunately, some of that work may be wrong, so you can't necessarily rely on it.
First of all, are there relationships set up in the Relationship Window? Hopefully there are. This will give you a map to see how the tables are related.
Write all the relationships in pairs of sentences: See Entity Relationship Diagramming for more information.
1:M
Each Customer can have one or more Orders.
Each Order can be for one and only one Customer.
M:M
Each Student can be in one or more Classes
Each Class can have one or more Students
Ask yourself (or your customer) if each and every one of these sentences is true. I will often go through this process with the customer. As they learn the database structure in this non-technical way, they will begin to spot relationship errors on their own.
Don't be constrained by the existing product. It's quite possible that the original database design is wrong. Don't assume the previous developer knew what he or she was doing. Even if he did, chances are good that if it's been in production for more than a couple of years, the business rules have changed. This is where the customer having a grasp of the database design process can be very useful.
If there are no relationships, you will have to infer these relationships based on the queries. As you discover table relationships, you should create them in the Relationships Window. If they're not there already, it is doubtful that you will be able to turn Referential Integrity ON, but at the very least, you will begin documenting the relationships.
Next, I would start with the reports. Open a report and check the RecordSource property see which query it uses. Then open that query, see what it is based on and so forth. When I have done this, I've used a separate piece of paper for each report and query tree. Then I will actually draw out the tree structure. I'll also create a list of all the queries and underneath each, list the queries or objects (form or reports) that it directly applies to. This can help if a single query is a base for many others. You may be able to find a tool to do this for you, but the discovery process is very educational.
Do the same with the forms.
Next, look through the Code Modules (including the code behind forms and reports). Often code is used to create queries or uses saved queries to create recordsets.
If DAO is used to CREATE the query, it might look something like this:
strSQL = "SELECT * FROM MyTable"
Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)
or
Set qdf = db.CreateQueryDef("qryMyQuery", "SELECT * FROM MyTable")
The first method is preferable and it is worthwhile to convert any using the second method into the first. The reason for this is that you will also want to know what queries are being created in code. If you create the SQL as a separate string, you can use the Debug.Print line to have the code evaluate the SQL string and display the SQL code in the Immediate Window. Like this:
strSQL = "SELECT * FROM MyTable"
Debug.Print strSQL
Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)
Complex SQL statements are often difficult to read in VBA code, so this is a useful debugging technique as well.
If a query is just being USED in code (not created in code) it might look like this:
Set qdf = db.QueryDefs("qryMyQuery")
or
Set rs= db.OpenRecordset("qryMyQuery")
or in ADO
rs.Open "qryMyQuery', cnn, adOpenKeyset, adLockOptimistic
There are a lot of variations on this. However, the important thing is that the query (or table for that matter) is being used by the code. Make a note of that as well. You can use the Find and Replace feature to find each query name. You can set it to search through all of the code modules.
Also, some Forms and Reports have SQL statements in their RecordSource properties, rather than saved queries. You should also make a note of the elements that make up these.
Once you've done all the reports, forms, and code, see which queries are not accounted for. Chances are they are not used anywhere. Many mature database projects have unused objects lying around. DO NOT delete these. Instead, I favor renaming them with an XXX prefix, which tells me this is OK to delete at some future time. Sometimes I will also add the date to the name so I know how long it's been since I "deleted" it. If you run into a problem, you can always name it back.
Lastly, I would try to make some kind of method out of all of this madness. Try to develop some sort of rational naming structure for your queries. There is no single correct way to do this. Often if a series of queries is used ONLY by one report, I will name them after the report and indicate which level they are in. However, for queries that are used as a base for multiple other queries, this doesn't work so well. I've never come up with anything that worked in all cases. And it doesn't matter. Just try the best you can to do a rational job of renaming them. CAUTION: DO THIS ON PAPER FIRST!
Once you have renamed your queries on paper in some rational manner, get your hands on a renaming tool. Rick Fisher has a good shareware add-in called "Find and Replace". You can find it here: http://www.rickworld.com/download.html. (It is worth registering for the extra features, though). This product (and others like Speed Ferret) allow you to rename an object throughout the entire database including forms, queries, reports, macros, and code.
By the time you get done with all this, you will know this database like the back of your hand.
No comments:
Post a Comment