MS Access – Library Databases

Here’s a hidden gem for any serious developer that doesn’t seem to get enough of a spotlight.

… and no this is not an article about creating a library database to manage you collection of books, CDs, DVDs, …

What is a Library Database?

A Library database, also sometimes referred to as a Code Library, Code Db or Reference Library, is a standard Access database (which can be compiled to secure it from prying eyes) that enables one to create an easily deployable, reusable, maintainable library of objects and code to implement within any other database.

Huh? What does that mean in plain English?

Simply put, when you start a new project, typically, we go around scavenging code modules, forms (dialogues, date picker, progress bar, …) and the likes from different sources to import them into you new project to give us our basis to start building our application?

This is where a Library database can come in.

With a Library database, you place your common code components (Date/Time, Validations, APIs, …) and other objects (Date picker, Progress bar, …) and then simply reference it within your main database so you can use them seamlessly.

Continue reading →

Happy Birthday Microsoft Access

It’s hard to believe, but it’s already been 25 years that Microsoft Access was first released and what a game changer it has been! It was back on this day (although the exact date can be argued to be a few days earlier or later depending on who you speak to), ~~November 13, 1992~~ October 28th, 1992, that Microsoft Access 1.0 was first released. You can learn more by reading the Wikipedia article about Microsoft Access (but do note their date is wrong).

After a discussion with fellow MVPs, it would seem that even Wikipedia has the date wrong and that, as indicated above, the date is as per the images shown above (from Microsoft), so October 28th, 1992 as there are MVPs who received their first copy of Access the following day (October 29th, 1992).

Regardless of the specific date (launch date, CTP date, public announcement date, …) it was around this time of year some 25 years ago that Access made its entrance into the computing world.

Continue reading →

MS Access – Continuous Form Data Entry Approaches

Anyone that has created a continuous form is well aware of the new entry blank row appearing at the bottom of the form.

Although this is functional, let’s face it, it is less than ideal! Continuously having to scroll down to the bottom, or even having the new row hidden completely out of sight does not enhance the user experience. Furthermore, if you apply default values to your controls it can end up confusing your users as they will think they are actual rows of data rather than a row for performing data entry.

So I thought I’d create a very simple demo database to illustrate a couple alternative approaches to implement within a database to simplify your end-users’ lives and up your game as a developer.

Continue reading →

Access – Bug – 1710 Breaks Compatibility with ACCDEs and Access Runtime

It’s been a rough year for the Access Development Team and Updates! Sadly, Update 1710 is no exception to the rule.

First reported in the Microsoft Answers forum by JDKilmer and now confirmed by the Dev Team, it would appear that update 1710 breaks compatibility between ACCDEs and Access runtime. Access generates the “unrecognized database” error:

This database is in an unrecognized format. The database may have been created with a later version of Microsoft Access than the one you are using. Upgrade your version of Microsoft Access to the current one, then open the database.

Continue reading →

Access – The wizard you’ve requested is not installed or is in a bad state.

Here’s a new one for me. I was at a client’s simply trying to use the Linked Table Manager and received the following error message?!

The Wizard you’ve requested is not installed or is in a bad state. Please install or reinstall the wizard. If you do not have permissions to do this on your computer see your system Administrator.

Of course, the standard troubleshooting was performed

Reboot
Repair
Uninstall/Reinstall

Sadly, nothing worked. And then I Googled and came across a thread on dbforums entitled Wizards not working in Access 2013 in which I learnt that Microsoft, in certain cases, appears to not configure the Trusted Locations to include the ACCWIZ folder and thus we get the error whenever we try and launch any of the wizards. That’s right, Microsoft improperly sets up the Trusted Locations for the ACCWIZ folder.

Continue reading →

MS Word – Bug – Cannot Turn Off Design Mode

The Problem

I was revisiting an old word document which included some Content Controls. I would

Open the document
Enable Design Mode
Disable Design Mode (or at least try to)

and would receive the following error message

Word cannot turn off design mode because placeholder text in a content control contains invalid items. Placeholder text cannot contain items such as floating objects, revision marks, or content controls. Remove these items from the placeholder and try again.

How is it there was no issue creating the document many moons ago?
How is it this error occurs even though I haven’t made a single change?

Continue reading →

Access – Slow Design View

Recently, I’ve been doing a lot of work using Access 2010 and have been finding it slow when doing any design work.

Let be clear about my setup, I have already ensured an optimal development environment:

All the files are local
Subdatasheet are set to None
AutoCorrect is turned off
Running off of an SSD
Access is fully patched
Plenty of CPU/RAM/HD …
and so on …

Regardless of the above, Access is plain slow. It is slow to

Open an object in design view
Select subforms
Switch between design and SQL view when trying to edit a record source
Open the Existing Fields dialog
…

Continue reading →

Office 2007 / Access 2007 End of Extended Support

Yes, indeed, it’s that time when Microsoft turns its back on Office 2007! Not only do they turn their backs on it, but they try and erase any evidence of its existence (as if it were a crime! LOL!) by going to such lengths as to remove all associated downloads, help files & article, support information, … from the Microsoft ecosystem (see below for more details).

As an FYI, I’m passing along the info that was just provided to me by the Access Dev Team
Continue reading →

Microsoft Office 365 – Uninstall an Update

With Microsoft’s major push of Office 365, more specifically Click-to-run (C2R or CTR), I think it important to briefly touch base on the entire update process.

Do note that the following also applies to the C2R versions of Access 2013, 2016, 2019, 2021+ the only difference being the build numbers.

While Office 365 will, unless reconfigured, automatically updates itself based on the Update Channel (you can learn more about Update Channel by reviewing Overview of update channels for Office 365 ProPlus) you have chosen to use (and yes, I too, never made any such choice and was automatically setup on the Deferred Channel). This, on the surface, sounds like a brilliant approach, but as we have recently seen (updates 1705, 1706, 1707) sometimes updates can introduce new bugs that are more problematic then the ones they are supposedly resolving.

With Office 365, much to most people’s dismay, one can no longer simply open the Control Panel to uninstall an update. Microsoft no longer offers a graphic interface to manage the updates and one must use the “DOS” command prompt to uninstall updates (or as Microsoft now refers to it as “Revert to an earlier version”).

You can read Microsoft’s instructions on uninstalling an update by using the following link:

How to revert to an earlier version of Office

How to revert to an earlier version of Office 2013 or Office 2016 Click-to-Run.

Corrections

Now let’s make a few corrections and additions to their article!
Continue reading →

Excel – Hide/UnHide WorkSheet(s)

A couple more simple Excel VBA procedures that might be useful to some.

It can be very useful to hide certain worksheets from the users, for instance, sheet used for lookups, lists,… and procedure such as the ones presented below can help you automate the task of hiding, as well as restoring them for you administer them.

Hide/UnHide all the Worksheets

Hide All The WorkSheets

'---------------------------------------------------------------------------------------
' Procedure : WrkSht_HideAll
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Hide all the worksheets except for the active sheet
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/

' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Oct-25             Initial Release
' 2         2017-09-25              Updated Error Handler
'---------------------------------------------------------------------------------------
Public Sub WrkSht_HideAll()
    On Error GoTo Error_Handler
    Dim WrkSht                As Excel.Worksheet

    Application.ScreenUpdating = False
    For Each WrkSht In Worksheets
        If WrkSht.Name <> ActiveSheet.Name Then WrkSht.Visible = xlSheetVeryHidden
    Next WrkSht

Error_Handler_Exit:
    On Error Resume Next
    Application.ScreenUpdating = True
    If Not WrkSht Is Nothing Then Set WrkSht = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: WrkSht_HideAll" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Continue reading →