Tips for MS Access developers on developing a Commercial MS Access application

Here are some recommendations for MS Access developers when getting into a commercial App:

We provide custom software development and many times we get phone calls from new Clients regarding their MS Access application. In many cases the Clients still have feelings towards their old application, and simply want us improve it or fix some other ms access developer’s bugs. The application could have been developed more than 20 years ago and the developer nowhere to be found.

Convert MS Access to a web application could be another solution. In this case you can use the old application for some of business rules.

See steps below of how to create a robust MS Access application:

Over the years I’ve seen a lot of great MS Access applications that looks like MS Access applications. If that sounds confusing – let me explain:

If your goal as MS Access Developer is to develop and take your custom software development to market, the last thing you want is for it to look like a MS Access application. You want it to look like as an commercial application as it could be. So here are some suggestions:

1. Splitting the database:

Most MS Access Consultants or any developers will know that it is not only good practice but crucial for the database to be split between front-end and back-end. One of the main reasons it to allow for releasing a front-end update to all Clients without impacting their individual datasets.

I want to go one step further and suggest that also splitting the back-end in to different databases could sometimes help to keep things simple and separate in making provision for the personal settings of each Workstation User. Especially where the general data set it stored on a LAN Server. The second back-end database could be stored locally on each Workstation.

Another option is to use MS SQL database as the back end instead of the MS Access back end part. This will result in a better stable solution. Our MS SQL Remote DBA will optimize it for a best performance.

2. Desktop Icon:

Spend time to create a professional looking Icon for your application which will be used as a Desktop shortcut icon, Taskbar icon as well as on each Form and Report. Make sure the icon is transparent.

There are lots of free tools on the Internet to do the above yourself else any Graphic Designer would be able to assist.

3. Taskbar Icon:

In most cases the Taskbar icon will “follow” the look of the icon set in your Application / Desktop Icon but it is not guaranteed as it depends on the User settings of the Taskbar preferences and Operation System (eg: Windows 7 vs Windows 10).

Although this can be forced down with a Windows Registry update it is something that is frowned upon and a big “no-no”. The User should be allowed to set their own preferences and the MS Access Consultant must cater for all options.

4. Always link Images – Never Embed:

This will help keeping the database debloated and fast and ensure Client satisfaction.

5. ACCDE / MDE:

Needless to say - refrain from releasing an ‘open’ version (accdb/mdb) of your application unless the Client specifically bought the rights to your VBA code and hard work.

6. Hiding the MS Access Outer Window:

Another method to add a professional look to the application is to hide the MS Access Window itself and only display the Form as in the example below:

The code can be found here: (The original MS Access Developer unknown):
Check this code of how to hide MS Access window.

The only downside to this method is that you have to Show and Hide when displaying Reports or Query Results etc

7. Splash screen:

Using the above method and setting the Form’s Border Style to ‘None’ makes for an excellent ‘Splash Screen’

8. Property Setter by MS Access Developer Bill Mosca:

With this little MS Access Add-on it is possible to set some of the System settings. For example enabling and disabling the bypass shift-key. Bill is not supporting the Add-on anymore but the copyright allows for the Add-on to be shared freely so you can get it here: Property Setter by MS Access Developer.   

9. Window Registry changes:

Although it is possible to check and edit the Windows Register with VBA, care must be taken not to corrupt the End-User’s system. In addition, never to overwrite the User’s Preferences without permissions (as explained on in step 3 above). But this can be helpful to alert the User of necessary changes. It might be that your Application works better with a certain Date Format and you could then get User permissions to change the settings in the Registry itself using VBA.

10. Setup2Go:

A great way to roll out Front-end backups (or even initial installation files) via a single Executable file (exe) is to use something like Setup2Go or Install Shield.

11. Auto updates of Front end and updating back end via Front end:

Automatically checking for updates will ensure all your Clients that they are using the most up to date version of your application. This will reduce support calls. The back end database can also be update instead of physically having to update each Client’s back end database structure one-by-one.

12. Form design Properties:

Some Forms design properties could be real dead giveaways that you dealing with an Ms Access app.

Here are some suggestions (if relevant):

• Caption: Set the Caption else Access will decide the Form’s title for you.
• Auto Center: Set to ‘Yes’ to ensure the Window is not hidden somewhere left base.
• Auto Resize: Set to ‘No’ if you want to control the Form size (recommended).
• Fit to Screen: Set to ‘No’ if you want to control the Form size (recommended).
• Border Style: Set to ‘Thin’ if you want to control the Form size (recommended).
• Records Selectors: Set to ‘No’.
• Navigation Buttons: Set to ‘Yes’ unless you created you own.
• Dividing Lines: Set to ‘No’.
• Scroll Bars: Set to ‘Neither’ and if possible size the Form correctly to cater for expansion.
• Min Max Buttons: Set to ‘None’ especially for Sub Forms since you want to control their sizes.
• Pop Up and Modal: Set Sub Form’s Modal to ‘Yes’ to ensure the main Form cannot steal the focus and as a result hide the Sub Form away without the User saving some data changes.

13. Msgbox Title:

Always set the Title parameter of a Message box else the default will be “Microsoft Office Access”.

14. Custom Menus:

Refrain from using the build-in Switchboard. Rather build your own menu items. With Pre-Ribbon versions like MS Access 2003 it was much easier but there are lots of tools available to help you create a custom Ribbon.

15. MS Access Options:

• Application Title: Set it else MS Access will decide on one.
• Application Icon: Use a Transparent icon – there are lots of online tools available to create one
• Display Form: Set it here or use an Auto Exec Macro.
• Set Overlapping Windows – Compared to Tabbed Documents, it gives a more professional look and a few of the above-mentioned options will not work on Tabbed Documents.
• Display Navigation Panel: Set to ‘No’.
• When creating your own Menu/Ribbon remember to Un-tick the System menus.

16. MS Access Backups:

If you have a server, auto upload Client back end datasets (compact first) on when they exit the application. It will make you look like a hero when they lose all their data due to a PC crash.

17. Converting MS Access to Web:

If your current application works well make sure any change that occurs, will be documented by your MS Access developer. This will help you want you want to eventually convert it to Web.

If you want your business to grow and have your application available from anywhere. I recommend you convert MS Access to Web-based solution. This way you will increase your productivity and your company will be ready for the 21th century.

It will be helpful if your old application is well documented by the previous developer. The more we know the better we can save you a lot of time and money.

The database could be either MS SQL or MySQL for best performance and high security. Our custom software developers work closely with either MySQL or MS SQL Remote DBA that help to design and optimize the database for the maximum performance.

18. Additional Services that we Provide:

• Remote DBA services for MS SQL or MySQL.
• SQL Consulting.
• Database design for MySQL or MS SQL.
• Database optimization.
• Convert your Desktop Application into a web-based solution.
• Web scraping services while using dot net.
• Optimize your Stored Procedures.
• Dot Net development.
• Alpha AnyWhere Developers, also known as: Alpha Five Development.
• Mobile application development Solutions.
• Convert Visual Pro Apps to Web based solutions.
• Maintain Visual FoxPro Apps.
• Convert MS Access To Web.

To learn more about our services please contact us or call via this phone: 732-536-4765.