Refactoring an Access Database (3 Viewers)

[Shimon]

Hi,
I inherited a Database written in a Right To Left Language. It was written by a programmer who has no formal training, and therefore has several basic design flaws.
As it is written in Right To Left and the code is Left To Right, I find it very hard to understand and follow the code. Changes are very difficult for me as the cursor direction changes all the time.

I would like to refactor it, so that the table and column names are in English, and maybe make some further changes to the database.

Does anyone have a set of general guidelines how to go about planning, implementing and testing this process?

There is a book by Martin Fowler ( happens to be one of the Guru's that I followed in the past) on Refactoring a Database, but I think I will gain more from the common wisdom of this forum, than from reading his book.

The current size of the DB is 13-14 MB each for the front-end and back-end, after two years of usage. There are about 15 tables, 30-40 queries and over 100 forms and sub-forms ( probably only half of them is use) and 9 reports.

The back-end is on a Desktop which was built to serve as the Server and the main workstation. There are another 2-3 laptops in use, one of them via wireless connection.
The application serves a non profit dress rental organization, with up to 40 rentals and deliveries every day in the busy season. Each rental is up to 6 dresses.
The organization is open ( not necessarily open to the public) about 10-12 hours a day, almost every weekday ( and some days on the weekend,).
There are over 100 volunteers, but only about 20-30 of them use the Access program.
Sincerely,
Shimon

 

[Josef P.]

Perhaps even activated Name AutoCorrect options can help.
Or:
Perhaps it helps if you only switch to the new naming at the beginning using views and only change the tables directly later.

Maybe you'll get a few ideas from this video:

 

[Shimon]

The following paragraphs outlines the planned steps necessary for refactoring with minimal effort and risk. The first steps might seem to be excessive, but will pay off on the long run.

• Create a test(s) for each component that might be affected by the refactoring.
• Create a one button "Run All Tests", that gives results fast or that can run off-hours (without hindering the development).
• Verify a green light for all tests.
• It is not acceptable to say "well I have errors, and they are alright". If there are acceptable errors, verify that they are easily discernable and they still enable to give an honest green light.
• Every "Red Light" needs to point to a specific area of failure.
• Create and test small changes in order to verify an easy mechanism for rollback.
• Implementing and testing them in Test platforms may be done in medium ( smallish steps).
• Roll them out in to Production in parts that are small enough so that you can guarantee an easy Rollback.
• Very often this means a multiple step change instead of one. For instance, normalizing a Customer table that has two phone number columns to use a linked table would be done in the following manner
• Adding a table
• Using both the new and old data
• Migrating the old data to the new format
• Removing the code that refers to the old data
• Removing the old data columns.
• Some of the above steps can be done in together, but the Rollback would be almost impossible.

In my Access application, this means that I need to

• create a small DB with a small set of data
• Optional: create a thin FE, I.E. delete unused/ old Forms.
• In order to do this, I should probably add a logging procedure to all my forms and sub-forms, so that all Forms opened will register their names.
• This will probably make testing and debugging problems easier, if I join this table with the existing Error logging table.
• set up a macro (or procedure) that tests all (or most) of my Forms, Queries and reports.

 

[Shimon]

I will now relate to some steps specific to my current project.

The current project is protected, so that a standard user does not have access to Design view or to any DB objects. There is always a Form open, that fills the application window. If you exit the Program in any unplanned way, it re-starts access.
Upon startup, you must enter a name and password. The name is a dropdown list from a calculated field from a linked table.

In order to be able to to a project-wide rename, I must make some changes to the way I start-up the application ( so that I don't lock myself out).
I also realized that if I want to refactor the code in the Modules, I have to understand them.
As I learn the workings, I should add remarks to all the functions / subroutines or what ever.

If I find a utility that does the renaming satisfactorily, I will post some screenshots of the tables and forms.
Sincerely,
Shimon

 

[Gasman]

This was sent by my work computer, without seeing the content of the quote.

I have disabled javascript and can still reply, though in a very small window and also get a warning.

Unable to post pictures though.

 

[Shimon]

I have disabled javascript and can still reply, though in a very small window and also get a warning.

Unable to post pictures though.

Thanks Gasman.