Code library database

[bodders24]

I have been considering how to re-use my generic VBA code and Access objects across multiple applications. These are for such standard things as : closing all forms, error reporting, connecting to SQL Server, startup routines, etc.

I am thinking of creating a code and object library database, and I would appreciate any feedback on the idea. If people have created one do you do anything specifically to identify the objects, e.g. prefix of AA (Access architecture) at the module or procedure/function level ?

Also how do you ensure that the database stays up to date with any changes or additions ?

Grateful for your thoughts

Bodders

 

[theDBguy]

One approach is to add your generic database as a reference to your project. Have you tried that?

 

[tvanstiphout]

> how do you ensure that the database stays up to date with any changes or additions

That can be very tricky. Let's consider how others like MSFT do this. The Windows API is one of MSFT's code libraries, and it has been VERY constant with regards to the public interface. They have to. If they change the arguments of say CreateWindow API call, all apps in the world will fail to create a window. In cases where they just HAD to have a more powerful CreateWindow, they came up with a new API: CreateWindowEx for extra special features. Within reason, CreateWindow's internals can change. For example the window might be created with slightly rounded corners, or just a hint of transparency in the window title.

If you follow in these footsteps, you will never distribute a new version of your library that is not compatible with existing apps.

 

[GPGeorge]

I have been considering how to re-use my generic VBA code and Access objects across multiple applications. These are for such standard things as : closing all forms, error reporting, connecting to SQL Server, startup routines, etc.

I am thinking of creating a code and object library database, and I would appreciate any feedback on the idea. If people have created one do you do anything specifically to identify the objects, e.g. prefix of AA (Access architecture) at the module or procedure/function level ?

Also how do you ensure that the database stays up to date with any changes or additions ?

Grateful for your thoughts

Bodders

I learned the hard way what Tom warns about. I have used an accdb as a library reference for many years. It includes my error handler. The last time I decided to modify it, I ended up chasing down dozens, if not hundreds, of instances in nearly a dozen databases I use. Never again.

Instead, if you want to use new parameters for a sub or function, do so using the keyword Optional so you don't break everything, everywhere.

 

[Josef P.]

At first, I also used a collection of code libraries (At that time, I created them with Access Developer Extensions as DLL).
Due to the effort involved in maintaining different versions, I switched to a collection of codemodules that I could import into and update in the respective application. This made it easier to maintain the various applications. Improving the “library” codemodules is also easier, as I improve them in the respective application in which I am currently working with the codemodule. The codemodule is then exported to the code repository and is available for update for other applications.
To ensure that I don't break existing interfaces when making changes, I validate them using unit tests.

 

[bodders24]

> how do you ensure that the database stays up to date with any changes or additions

That can be very tricky. Let's consider how others like MSFT do this. The Windows API is one of MSFT's code libraries, and it has been VERY constant with regards to the public interface. They have to. If they change the arguments of say CreateWindow API call, all apps in the world will fail to create a window. In cases where they just HAD to have a more powerful CreateWindow, they came up with a new API: CreateWindowEx for extra special features. Within reason, CreateWindow's internals can change. For example the window might be created with slightly rounded corners, or just a hint of transparency in the window title.

If you follow in these footsteps, you will never distribute a new version of your library that is not compatible with existing apps.

Tom

If I understand you correctly, you would never update an existing proc or function in the code library database, you would create a new version (eg OpenFormV2) of it which would have the enhanced/updated functionality, but the existing version (eg OpenForm) would still be compatible. The only downside to that would be an increase in size in the code database over time.

Bodders

 

[Imb]

I have been considering how to re-use my generic VBA code and Access objects across multiple applications. These are for such standard things as : closing all forms, error reporting, connecting to SQL Server, startup routines, etc.

I am thinking of creating a code and object library database, and I would appreciate any feedback on the idea. If people have created one do you do anything specifically to identify the objects, e.g. prefix of AA (Access architecture) at the module or procedure/function level ?

Also how do you ensure that the database stays up to date with any changes or additions ?

Grateful for your thoughts

Bodders

Probably every developper has build some code- or object-library for fast and robust developping of applications.
Most are general error handling, form classes, control classes, ...

Some 40 years I am playing with generalization of Access applications. The problem (or my problem) with Access is that the binding of of controls in the forms is based on the fields in the correspoding tables. But a "control" (I will call it a User-Control) is much more than the way how it is stored. To handle that difference you have the BeforeUpdate and AfterUpdate events of the controls in the form. But that makes the form User-Control dependant, and thus hard to generalize.

I have removed all User-Control dependencies form the forms. Then all forms become "the same", with only the display function of the controls. The result is that I only use two forms: one as a continuous form, the other as unbound.
During opening of the form the controls are positioned, depending on what you want to display.
The binding of the control, to make them a User-Control, is done when a control is entered: User-Control-binding. Then the control in "bound" to the corresponding record in a metadate table in FE. With this record it is completely specified how the control behaves as a User-Control.

Each control is "any" form becomes getrs a controltype. This controltype can be to behave as Label, or Commandbutton, but can also represent a field. In this case the controltype can be far more than the bare fieldtype, for nuanciating how User-Controls function.

Because there is no predominance of forms over controls, forms can be automatically created based on the configuration of controls in the metadata-table. Compare to the "automatic" generation of Datasheet-tables, but now including all the User-Control specifics.
Almost all process to run applications can be automated, and made it specific with User-Control binding.

All these generalized processes are collected in a linked library, and so available to any application.
No need for form classes or control classes, no need of general error handling: each process has its own dedicated error-repairing/reporting functionality.

The central part is the active User-control. Only a couple of the control events are used to react specific to the User-Controltype. A "important" type is the User-Controltype "FK". With this type, representing a link to another table, you can traverse the whole application completely dynamical.

Data integrity is controlled form the metadata-table. So while typing, you can already where problems will occur when changes are written to the tables.
All changes in the metadata-table are logged. With the logged data the backend is automatically synchronized when installing a new version in the production environment. Except for some real problems, I never have to go to the backend.


It is a complete other way of thinking, but now form the User-Control side.