Refactoring an Access Database (1 Viewer)

[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.

 

[Pat Hartman]

I have only ever encountered ONE database I felt justified in refactoring and I've been working in this field for 50 years. That database was written by a class-crazy pseudo-expert who thought that one instruction classes were fine and nested them 7 levels deep. The code was absolutely impossible to follow. Even printed out. I didn't have anywhere near enough fingers to trace the logic. There were many others that I hated but since I could follow the logic, I left them alone unless I had to modify something. In that situation, I decided case by case, if I would modify the code or refactor to clean it up and make future enhancements easier. When table changes were involved, the decision usually came down on the refactoring side. But my employers/clients don't pay me to perfect other people's working code. For my own projects, I do it as I go along if I realize I did something dumb. Although there was also one time when the programmer was still working for me where I made him do it. He had a spelling issue. I do too but I also know how to use a dictionary. This was before spellcheck and intellisense. I had to make two emergency changes in one week to a program he wrote because he was on vacation and his spelling errors kept me making compile errors.

 

[Shimon]

Thanks Pat,

The reason I would like to refactor is so that all the objects are in English. I find it very hard to change ( or even follow the logic) when I have to figure out where the pieces go . If I go that route, even with a find and replace utility, it means that most updates and bug-fixes will not get to my client, as the original programmer will (probably) not agree to change all the objects. He says that he got used to working like this and manages fine.

One example is a Lunar Month date selector, which had five rows, but after two years there was a Lunar month which needed six rows. Copying his fix was very easy. It probably would work even if the business tables were in English, but I suspect that there might be other bug-fixes which will be hard to incorporate if my table names are different than his.

He is willing to learn and make small changes to the design, but even after having a two hour session with him, explaining the basic normalizing theory and practice, he still didn't create any relations in the Database. He did add Indexes.

There is some pretty sloppy code, using Auto-Numbering IDs for switch cases in VBA (for example if fromPreferences (30) = 3 then ......,) but I guess that this would be included in the small changes that we all do. In these cases I would probably duplicate the tables, add a column called ID_NAME that is unique and inambiguous and re-write the code, but keep his code (with a call to the new function).
Sincerely,
Shimon

P.S. I watched your video ( a second time) on using Before-Update to prevent bad data and enjoyed it very much.

 

[arnelgp]

As it is written in Right To Left and the code is Left To Right

maybe the db was created for Arabic audience, the reason for the orientation.
and the client approved of such.
before you do any modification, create a copy of the db and keep in a safe place, in case it got worse than before.
i may hint you are also not an ms access programmer yourself. if you have, you wouldn't have come for some comforts.

 

[Shimon]

Hi,
The application was written for (and in) Hebrew.
The main benefit of using this version of Access is that the orientation of all f
Forms (and therefore the controls and other frames or whatever they are called) are placed nicely in a RTL manner.
I also like to use this environment, but change the names of the objects to English. This way they get placed nicely, and read easy in the VBA.
This brings me to another point. There was a programmer that wanted to develop a multi language application.
He posted his problem, that the orientation cannot be manipulated by VBA.
Shimon

 

[arnelgp]

somewhere in this forum, there is a case where you can change the Labels of Controls to simulate multi language.
orientation of Text Input can be change also by changing the Keyboard language via VBA.

 

[GaP42]

This might be a starting point: Richard Rost: Multi-Language

 

[Pat Hartman]

I'm confused. If you are not the primary/only developer, how do you intend to refactor anything without the permission of the primary developer? It is one thing if you have only yourself to worry about and I do understand the issue with continually working with something you don't understand. I'm assuming you want the GUI to remain in Hebrew. I think Richard Rost has videos on how to swap the langue displayed. I partially had this particular problem back in the 70's when I did a job in Kuwait. We had one team member who's job it was to verify all the Arabic. But, we could switch the CRT or the printer for reports from Arabic to Latin characters and that would at least allow us to verify the numbers although we quickly learned the Arabic numerals and so didn't have to always switch.

However the innards of our COBOL code were in English and we used an English language compiler. I'm pretty sure the permanent staff also used an English compiler. Otherwise, they would have had a lot of trouble maintaining the code base after we left.

 

[Shimon]

Hi,
There are two distinct organizations, providing similar services in two cities.
We (my daughter) started off about seven years ago, in a small storage room, with five sets with between 5-20 dresses per set.
We had an Excell sheet for each set, and i created a cross query to print out each days' dresses.
I knew that we would need a database sometime, but kept on pushing it off.
The second organisation, which is much better established came to us to "learn the business" and opened the service in another city. They hired a programmer to write the application and gave it to us. There are slight variations in the services we give, and the programmer took that into consideration when he wrote the program.
Since then, he became very busy and is not very attentive to our requests, so my daughter asked me to do the changes. I tried to do them in a way that if we would get an update from him, it wouldn't be too hard to incorporate my changes.
I never checked this yet.
The last version he sent us seems much more robust and professional looking, so I think we will go over to his application and just copy / change the parts that are important to us.
This is a decision my daughter must make.
Sincerely,
Shimon