Cleaning up Access databases

[boerbende]

Dear readers,

I have inherited multiple databases several years ago, have updated them with own developments and am now sitting with a lot of different name conventions for columns (not exactly my fault!)

For example: in one table, there is used "maskin" for "position", in another "pos", in a third "winder" etc.
this is not only used in VBA, but also in field parameters, form recordsources querydefs, reports, modules, etc etc.
Dateproduced is called "dato", "proddate" and so on.
Database has front-end in Access 2010 and backend in SQL server Express

And not of any help, the databases are pretty continuously in use (24 x 7)
AND... linked with each other by use of linked tables...

I know, I know, this should have been done much more earlier. But I feel I need to do it now ...

Does somebody have best practices / tips to share, how to approach this problem, what tools to use?
Any help is appreciated

Thanks

Ben

 

[Galaxiom]

Sounds like a dangerous task. IS it really that important?

You would need to build a complete parallel system and a set of queries to import the data to the new system. Design those queries so they can bring in new data without duplication.

Start by installing V-tools. It is a free Access plugin. Its Total Search will find every reference to a name everywhere in the project and database. It has a replace facility but should only be used with extreme care.

The linked tables are going to be problematic. Access can change the local name of a table but it can't change the source table name or the field/column names of a linked table.

 

[The_Doc_Man]

First step: Establish a concordance document so that you can identify the names used in each DB and what they REALLY mean. Your concordance should include the tables, queries, forms, reports, and modules that explicitly name those fields.

The Database Tools Dependencies option will help you identify tables, queries, forms, and reports so that you know that if you change X in table T, that you will have to also touch queries Q, form F, and report R. A text search through "Whole Project" should help you find the code references for modules M. Don't forget when establishing this concordance document to include relationships.

You might need management approval for the time expenditure depending on just how "tight" your shop is set up with regard to time charges. However, for maintenance purposes, this concordance will be worth its weight in gold.

Second Step: Get management buy-in on the proposed change (see third step for details). Management can help you to impose this change on users, provide you offer an orderly approach.

Third step: I'm sure there are times ... no, let me make it stronger. There MUST be times set aside for maintenance of each DB, even if only for making a backup copy and running a Compact & Repair. During these times, approach the issue of making name changes. Pick a couple of innocuous ones at first to get practice.

As part of this, get your people up on the idea that you will be doing some updates and that you will have some new FE files for them that, if they don't use these new files, will leave them unable to work in those databases.

Fourth: If you weren't up on this concept before, look up articles on the use of a DESIGN MASTER to be the place where you make changes. Have a completely parallel development copy of everything.

Fifth, I cannot recall the references, but there are some "global change" tools that have been mentioned from time to time in this forum. It might be possible to find a tool to do this job with less pain than doing it by hand. (No bets on that one....) If the other members of the forum remember or know of such tools, they might chime in for you.

 

[Lightwave]

It may be totally impractical but if I can this would be a nudge to me to think about a full re-design.

Fixing things by repairing an existing system can be next to impossible and you are only likely to make small gains - and that's why I would consider a complete re-design. If I am doing a full re-design I look to include really good features and not just fix old problems.

Key features that I like to include are;
Upgrade back end to SQL Server
Introduce Spatial Key values
Improve Normalization ( sometimes a couple of changes can make things really much better )
Major data cleansing operation.

Yes its time consuming but you learn a lot and you can do it in parallel to the existing application with an eye to transferring the information across at the very end.

At the end of the day it comes down to just how much value you could add by redesigning it.

Good luck