Database Redesign

[Paul H]

As I stated in another thread, I have to fix and upgrade some databases. Here is my introduction.

[The oddest part of this new assignment is the heavy use Access throughout the department, maybe the whole company. These databases are all in versions 2003 and earlier and rely almost universally on macros, which I hate. They are reluctant to go VBA, but that's where I'm going to have to take them if they want much development. Also these databases have been developed by dozens of different users over a dozen years, most of whom never heard of naming conventions. Queries are named with sentences in some cases. Here's one "Delete Qry older than oldest date on stmt NOT CURRENTLY USED", here's another "Match Stmt to Qry FOR AGING export (45+)". Renaming these to something sensible and debugging the macros will not be fun, but there it is.

So now you have the overview. The one particular database I am starting with has the most potential for future development, but has these atrocious query and table names. It seems like the place to begin. I have name autocorrect turned on, but I am running into problems right away. The biggest table is named "ChkQry1". Yes it is a table. I ran Object dependencies to get a list of all related queries. I then changed it to something like tblCheck, reran the Object Dependencies and only about 2 of 20 queries survived the change over. The rest still are looking for chkQry1. I know I can open the dangling queries and pop the SQL into wordpad, do a find and replace and then pop it back into the query. I'm not sure there is a more practical approach to dealing with this. I anticipate a lot of debugging, especially in the macro that have these odd query names embedded in them. I may be better off building from the ground up. That starting to look like a more attractive option.

[pbaldy]

You might check out a free tool called V-Tools, which has a deep find and replace function. The built-in autocorrect feature isn't well thought of; most of us turn it off (it's derisively nicknamed autocorrupt).

[Paul H]

Thanks Paul. I've always thought Access needed better SQL editing tools.

[Paul H]

I found the tools. Looks like something I could have used a long time ago. I take it I could change the name of a query or table and zoom through the entire database and find and replace any reference to the old table name. Wow. Now comes the hard part. Convincing the local IT department to let me install it.

[pbaldy]

No problem Paul (sounds like we're both talking to ourselves ). If you're unfamiliar with them, you might also like MZ-Tools and Smart Indenter.

[June7]

Paul, I commented in your other thread recommending Rick Fisher's Find and Replace.

[Paul H]

Thanks June. It will be up to client and their IT Dept to greenlight this. I hope they do, especially for the mental health reasons you noted. If they don't there will be a lot of swearing and cursing coming from my cube.

[June7]

I am not familiar with the other applications but I might look at V-Tools. If the IT dept is suspicious of 'free' software, Fisher's is only about $50.

[Paul H]

I've been piddling around in the hope I could get quick action (Ha!) from the local I.T. dept on these add ons! I'm on a shortish contract though and need to make (or show) some progress. With that in mind, I am starting with the forms and their controls. Renaming a form should be pretty harmless considering there are almost no text or combo boxes, only command buttons and lables with hyperlinks. Doing this also gives me an idea of how the processes work. I am tentatively renaming a few queries, documenting my changes as I go along. Also backing up frequently. I'm leaving macros in place for the most part since they have no real VBA support. In the odd chance that I.T. comes through, I'll start renaming tables and field name which are full of special characters #, @, - and spaces. If I have to go forward manually, it will make the job tedious and slow. Anyway I am moving forward. I'll keep you informed if anything interesting pops up.

[Paul H]

Just downloaded and installed my V-Tools. Had to run Access as an Administrator. Clicked on Database Tools => Add Ins and there they are, including the "Deep Search" option. Can't wait to use it, but I'm shutting down for the night, backing up my database and starting fresh in the morning. I'm hoping for great things.

[pbaldy]

Post back with results. I haven't needed that particular tool, but several MVP's swear by it, so I felt comfortable recommending it. Regarding:

I am not familiar with the other applications

If you mean MZ-Tools and Smart Indenter, I've used both for many years. I won't say I couldn't live without them, but I wouldn't want to. Both are very handy, and big time savers.

[Paul H]

June/Paul,

Been using V-Tools for a few days. It works great, sometimes. Other times, not so much. I'm consider taking a stab at Rick Fisher's Find and Replace.