MS Access – Hide the Ribbon

One more of these common questions in the forums is how to hide the Access application ribbon completely.  As per the usual with Access, this can be accomplished a few different ways:

  • Using VBA
  • Using Ribbon XML

Continue reading →

MS Access – Bug – Record Source and Row Source Changes Not Saving

Software Bug

A new bug with MS Access has surfaced with the latest build (9029.2167) in which SQL Statements used to define a Record Source or Row Source (in forms/reports, combo boxes, listboxes, …) do not get saved after being modified and the user purposely saving the changes.  They just don’t get committed.

There are a couple threads that have popped up relating to this issue, such as:

The Workaround

Continue reading →

MS Access – VBA – HasModule

I was trying to help out in an UA discussion Form/report Document Hasmodule? in which Stuart was trying to find a way to determine if a form or report object had a module.

Now, the obvious answer is to simply use the HasModule property.  But no, in this instance that wasn’t a viable option.  Stuart needed a way to determine whether a form/report had a module without needing to open the object first as is required by the HasModule property.

A little head scratching later I came up with 3 functions that should accomplish the requested task and do not require opening the object beforehand.  Perhaps these can serve others.

Continue reading →

MS Access – Templates on Office.com

In a hush-hush move, Microsoft has temporarily made a handful of the Access Templates available for download directly on the Office.com website like Excel, Word, … instead of needing to do so through the application itself, but they are doing so in some sort of A/B testing methodology which means some people will see the link to the download while others will not.  Apparently, they are trying to gauge the interest in such a feature to determine if this should remain a permanent site feature or not.

Continue reading →

MS Access – Bug – Automatic configuration of the current version of Microsoft Access has failed

By all accounts, it appears that Office365/2016 update 9029.2167 is flawed and causing some user to not be able to launch Access at all and instead receive the following error message

Automatic configuration of the current version of Microsoft Access has failed. Your database might not function correctly. This can occur if you do not have the necessary permissions to install Microsoft Access on this computer

In the last week (more now) there have been a whole slew of posts that have appeared on the subject, for instance:

Worse is the fact that this was reported through the Insiders Program back in January!

Continue reading →

VBA – Convert File Formats

This is somewhat a continuation on my previous post VBA – Convert XLS to XLSX in which I provided a simple little procedure to upgrade an older xls file to the newer xlsx file format.

I thought to myself, would it be nice to have a more versatile function that could migrate between various other common file formats.

So I set out to take my original function and transform it to enable to user to specify the desired output format and came up with a nice function that enabled anyone to converts Excel compatible files to another Excel compatible format.

Then I said to myself, it must be possible to do something similar for Word and set out to create a function that would enable people to convert file between the various Word compatible formats.

Below are the 2 functions I came up with.
Continue reading →

VBA – User-defined type not defined

This might seem obvious to some, but I thought I’d add a small post regarding Enums and the User-defined type not defined error.


If you are developing a new procedure and creating an Enum to simplify input variable entry and then compile your code and get an error of

User-defined type not defined error

as shown above, be sure to review where you created/placed your Enum as it needs to be in the module’s header section above the first procedure in the module.  Otherwise, you get the above error.  If that is the case, simply move your Enum block to the module header and try again, everything should fall into place and work.

VBA – Convert XLS to XLSX

I was trying to help someone who asked a question in another one of my posts and ended up creating the following procedure that can convert an older xls Excel file into the newer xlsx format and thought it might be helpful to others.

'---------------------------------------------------------------------------------------
' Procedure : XLS_ConvertXLS2XLSX
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Converts an xls (2003-) into an xlsx (2007+)
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sXLSFile  : String - XLS file path, name and extension to be converted
' bDelXLS   : True/False - Should the original XLS file be deleted after the conversion
'
' Usage:
' ~~~~~~
' Call XLS_ConvertXLS2XLSX("C:TempTest.xls")
' Call XLS_ConvertXLS2XLSX("C:TempTest.xls", False)
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2018-02-27              Initial Release
'---------------------------------------------------------------------------------------
Function XLS_ConvertXLS2XLSX(ByVal sXLSFile As String, Optional bDelXLS As Boolean = True)
          '#Const EarlyBind = True 'Use Early Binding, Req. Reference Library
    #Const EarlyBind = False    'Use Late Binding
    #If EarlyBind = True Then
              'Early Binding Declarations
              Dim oExcel            As Excel.Application
              Dim oExcelWrkBk       As Excel.WorkBook
    #Else
              'Late Binding Declaration/Constants
              Dim oExcel            As Object
              Dim oExcelWrkBk       As Object
              Const xlOpenXMLWorkbook = 51
    #End If
          Dim bExcelOpened          As Boolean
 
          'Start Excel
10        On Error Resume Next
20        Set oExcel = GetObject(, "Excel.Application")      'Bind to existing instance of Excel
30        If Err.Number <> 0 Then      'Could not get instance of Excel, so create a new one
40            Err.Clear
50            On Error GoTo Error_Handler
60            Set oExcel = CreateObject("Excel.Application")
70            bExcelOpened = False
80        Else      'Excel was already running
90            bExcelOpened = True
100       End If
110       On Error GoTo Error_Handler
 
120       oExcel.ScreenUpdating = False
130       oExcel.Visible = False     'Keep Excel hidden until we are done with our manipulation
140       Set oExcelWrkBk = oExcel.Workbooks.Open(sXLSFile)
150       oExcelWrkBk.SaveAS Replace(sXLSFile, ".xls", ".xlsx"), xlOpenXMLWorkbook, , , , False
160       oExcelWrkBk.Close False
170       If bExcelOpened = True Then oExcel.Quit
 
180       If bDelXLS = True Then Kill (sXLSFile)
 
Error_Handler_Exit:
190       On Error Resume Next
200       Set oExcelWrkBk = Nothing
210       Set oExcel = Nothing
220       Exit Function
 
Error_Handler:
230       MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
                 "Error Number: " & Err.Number & vbCrLf & _
                 "Error Source: XLS_ConvertXLS2XLSX" & vbCrLf & _
                 "Error Description: " & Err.Description & _
                 Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                 , vbOKOnly + vbCritical, "An Error has Occurred!"
240       oExcel.ScreenUpdating = True
250       oExcel.Visible = True     'Make excel visible to the user
260       Resume Error_Handler_Exit
End Function

MS Access – Backup a Database Using a VBScript

The following goes to support my previous article MS Access – Automatic Backup of an Access Database, specifically the section regarding using a VBScript in conjunction with Windows Scheduled Task.  I had a very basic VBScript, VBScript – Backup a File and add a Date Time Stamp, which illustrated how to add a Date/Time stamp to a file when you copy it and this made a great starting point for a backup script.

Several people have mentioned that they need more of a helping hand to transform that basic script into a more complete backup solution.

So I decided to try and help everyone out some more and below is a pretty comprehensive backup script that you can use.

Continue reading →