Category Archives: MS Access General Information

Microsoft Access 2016 Runtime has been Released!

It’s here. It’s finally here!

Microsoft has released Microsoft Access 2016 Runtime. It can be downloaded from Microsoft’s website at:

https://www.microsoft.com/en-us/download/details.aspx?id=50040

Before going wild deploying it in a production environment, be sure to test it out first in a Test environment just in case there are any problems as it is just been release to the public (we’ve all seen the problems that plagues Office 2016 initially!).

Finally a Fix for the Images not showing in Forms and Report for Access 2016

It took several months, but Microsoft finally has released an official fix for the issue of images not showing on forms and reports in MS Access 2016.

Microsoft Installer (.msi)-based edition of Office 2016

It can be downloaded from Microsoft’s site at:

https://support.microsoft.com/en-us/kb/3114379

Office 2016 Click-to-Run editions

It took a few extra days, but the fix has finally been ported to the Click-to-Run editions as well.

File -> Account -> Office Updates -> Update Now

I haven’t taken the time to test out either yet, but should it ever fail, you can always try one of the original workarounds proposed in: http://www.devhut.net/ms-access-missing-images-images-not-showing/, but hopefully this will be the end of this dreaded issue.

 

MS Access – Missing Images, Images not showing

UPDATE

There’s finally an official update from Microsoft to fix the problem, see: http://www.devhut.net/2015/12/08/finally-a-fix-for-the-images-not-showing-in-forms-and-report-for-access-2016/ for all the details and links.


There has been a MAJOR screw up and sadly we are seeing countless posts/questions regarding images not showing up in forms/reports.  Sadly, even several months after being flagged, we are still waiting upon an official fix from Microsoft!  🙁

Proposed Solution 1

One proposed solution is to set your database options (File -> Options -> Current Database)

12-5-2015 6-57-31 AM

and then reinsert all your images, but this will still not help if you are working with bmp images, so you’d need to ensure they are of another image format first.

Proposed Solution 2

Another fix has been to simply change the image’s Size Mode to Stretch or Clip and changing the Picture Type to Linked.  In many cases, it was as simple as that.

12-5-2015 7-03-34 AM

Below is the beginning of code to loop through all the forms and reports within a database and switch the properties in question.  The issue being that one way or another, you will still need to manually intervene since by switching them from Embedded to Linked, you will need to supply a path/filename.

'---------------------------------------------------------------------------------------
' Procedure : FixImages
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Try to start fixing MS' mess with images
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2015-11-30              Initial Release
'---------------------------------------------------------------------------------------
Sub FixImages()
    On Error GoTo Error_Handler
    Dim DbO                   As AccessObject
    Dim DbP                   As Object
    Dim frm                   As Access.Form
    Dim rpt                   As Access.Report
    Dim ctl                   As Access.Control

    Set DbP = Application.CurrentProject

    'Review/Fix Forms
    Debug.Print "Processing Forms"
    Debug.Print "~~~~~~~~~~~~~~~~"
    For Each DbO In DbP.AllForms
        Debug.Print vbTab & DbO.Name
        DoCmd.OpenForm DbO.Name, acDesign    'Open the form in design view so we can work with it
        Set frm = Forms(DbO.Name)
        With frm
            For Each ctl In .Controls    'Loop through all of the controls
                If ctl.ControlType = acImage Then    'If an image control is found then apply our settings
                    Debug.Print vbTab & vbTab & ctl.Name
                    ctl.SizeMode = acOLESizeClip    'could also be acOLESizeStretch - https://msdn.microsoft.com/en-us/library/office/ff837281(v=office.15).aspx
                    ctl.PictureType = 1    '0 would be embedded - https://msdn.microsoft.com/en-us/library/office/ff197027(v=office.15).aspx
                End If
            Next
        End With
        DoCmd.Close acForm, DbO.Name, acSaveYes    'Close and save our changes
    Next DbO

    'Review/Fix Reports
    Debug.Print "Processing Reports"
    Debug.Print "~~~~~~~~~~~~~~~~"
    For Each DbO In DbP.AllReports
        Debug.Print vbTab & DbO.Name
        DoCmd.OpenReport DbO.Name, acDesign    'Open the report in design view so we can work with it
        Set rpt = Reports(DbO.Name)
        With rpt
            For Each ctl In .Controls    'Loop through all of the controls
                If ctl.ControlType = acImage Then    'If an image control is found then apply our settings
                    Debug.Print vbTab & vbTab & ctl.Name
                    ctl.SizeMode = acOLESizeClip    'could also be acOLESizeStretch - https://msdn.microsoft.com/en-us/library/office/ff837281(v=office.15).aspx
                    ctl.PictureType = 1    '0 would be embedded - https://msdn.microsoft.com/en-us/library/office/ff197027(v=office.15).aspx
                End If
            Next
        End With
        DoCmd.Close acReport, DbO.Name, acSaveYes    'Close and save our changes
    Next DbO
    
    Debug.Print "Operation Complete!"

Error_Handler_Exit:
    On Error Resume Next
    Set ctl = Nothing
    Set frm = Nothing
    Set rpt = Nothing
    Set DbO = Nothing
    Set DbP = Nothing
    Exit Sub

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

What might also be useful is a simple procedure to quickly identify the problematic forms and reports. Below is such a procedure, it will loop through all the forms and reports looking for the specified control type; in our case acImage.

'---------------------------------------------------------------------------------------
' Procedure : IdentifyImageObjects
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Identify Forms/Reports with the specified control type
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Usage     : Call IdentifyImageObjects(acImage)
'           : Call IdentifyImageObjects(acLabel)
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2015-11-30              Initial Release
'---------------------------------------------------------------------------------------
Sub IdentifyImageObjects(sCtlType As AcControlType, Optional bLeaveOpen As Boolean = True)
    On Error GoTo Error_Handler
    Dim DbO                   As AccessObject
    Dim DbP                   As Object
    Dim frm                   As Access.Form
    Dim rpt                   As Access.Report
    Dim ctl                   As Access.Control
    Dim bCtlTypeFound         As Boolean

    Set DbP = Application.CurrentProject

    'Review/Fix Forms
    Debug.Print "Processing Forms"
    Debug.Print "~~~~~~~~~~~~~~~~"
    For Each DbO In DbP.AllForms
        bCtlTypeFound = False
        DoCmd.OpenForm DbO.Name, acDesign    'Open the form in design view so we can work with it
        Set frm = Forms(DbO.Name)
        With frm
            For Each ctl In .Controls    'Loop through all of the controls
                If ctl.ControlType = sCtlType Then    'If an image control is found then apply our settings
                    bCtlTypeFound = True
                    Exit For    'Stop the loop once we find 1 instance of the Control Type we are searching for
                End If
            Next
        End With
        If bCtlTypeFound = True Then
            Debug.Print vbTab & DbO.Name
            If bLeaveOpen = False Then DoCmd.Close acForm, DbO.Name, acSaveNo
        Else
            DoCmd.Close acForm, DbO.Name, acSaveNo
        End If
    Next DbO

    'Review/Fix Reports
    Debug.Print "Processing Reports"
    Debug.Print "~~~~~~~~~~~~~~~~"
    For Each DbO In DbP.AllReports
        bCtlTypeFound = False
        DoCmd.OpenReport DbO.Name, acDesign    'Open the report in design view so we can work with it
        Set rpt = Reports(DbO.Name)
        With rpt
            For Each ctl In .Controls    'Loop through all of the controls
                If ctl.ControlType = sCtlType Then    'If an image control is found then apply our settings
                    bCtlTypeFound = True
                    Exit For    'Stop the loop once we find 1 instance of the Control Type we are searching for
                End If
            Next
        End With
        If bCtlTypeFound = True Then
            Debug.Print vbTab & DbO.Name
            If bLeaveOpen = False Then DoCmd.Close acReport, DbO.Name, acSaveNo
        Else
            DoCmd.Close acReport, DbO.Name, acSaveNo
        End If
    Next DbO

    Debug.Print "Operation Complete!"

Error_Handler_Exit:
    On Error Resume Next
    Set ctl = Nothing
    Set frm = Nothing
    Set rpt = Nothing
    Set DbO = Nothing
    Set DbP = Nothing
    Exit Sub

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

Proposed Solution 3

I also found an interesting statement (taken from: http://answers.microsoft.com/en-us/office/forum/office_365hp-access/embedded-images-disappeared-after-upgrade-to/2c84727f-89e8-48f8-9096-eb330379d4c6?page=1) in which user sLaeYa says:

If you export your Form:

  • Right Click on the Form in Navigation Pane
  • Select Export to XML File
  • Choose Destination
  • Select Data and Presentation

In the destination folder there is an images folder, you will find that the names of your main embedded image has changed to “Form_Name.BMP”

If you change the file name to the new one it starts working again.

I cannot confirm or deny this claim as I haven’t had the time to test it out yet.  Feel free to leave a comment should you have any more relevant information on the subject.

MS Access – Deploying Your Database Front-End to Your Users

We all know that the proper way to setup any multiple-user MS Access database is to:

  • Split the database (Back-End – Tables; Front-End – Queries, Forms, Reports, Macros, VBA, …)
  • Place the Back-End on the a server to which all the users have access to
  • Deploy a copy of the Front-End to each user so they have their own local copy to run the database from.

With this in mind, a very common question in a variety of forums then becomes, how do we deploy a copy of the Front-End to our users and ensure that they always get any updates?

There are a number of possible approaches. such as:

  • bat/vbs script to blindly copy the master Front-End file to the local computer and then launches the database
  • vbs script which checks the current version installed and perform an update if/when required and then launches the database
  • html script which performs any updates and then launches the database
  • Integrating a version check within your database, or creating an intermediary database to perform updates and launch your database
  • use specialized software to launch the database which takes care of this

Below are a few good resources that illustrates each of these:

BAT/VBS/HTML Approach

Devhut.net http://www.devhut.net/2010/09/14/launch-open-an-ms-access-database-using-a-vbscript/
Devhut.net http://www.devhut.net/2010/09/15/launch-open-an-ms-access-database-using-a-vbscript-part-2/
UtterAccess.com Code Archive Easy Front-end Autoupdater For Large (or Small) Database Environ
UtterAccess.com Code Archive Updated Auto Update (and now Publish) MDE Front Ends
UtterAccess.com Code Archive Auto Update Users Front Ends
UtterAccess.com Code Archive Front End Automatic Update to New Version
Bob Larson’s Front-End Auto-Update Enabling Tool
Bill Mosca’s Deploying and Updating Front End Database Applications (at the time of writing this post it was the first item on the page)

Database Approach

Steve Schapel’s Distributing an Updated Access Application to a Network
Danny Lesandrini’s Automatically Deploy a New Access Client
Peter’s Software Application Starter

Specialized Software (Paid)

Tony Toews’ Auto Fe Updater
FMS Inc’s Total Access Startup

Count of the objects in your MS Access Database

Have you ever needed to get a simple count of the number of :

  • Tables
  • Queries
  • Forms
  • Macros
  • Reports
  • Modules

I recently needed to get some summary data regarding a database I was working on.  The following single line VBA commands will get you the count(s) you are after.

'Number of Tables
CurrentDb.TableDefs.Count 'includes system tables
'Number of Queries
CurrentDb.QueryDefs.Count
'Number of Forms
Currentproject.AllForms.Count
'Number of Macros
Currentproject.AllMacros.Count
'Number of Reports
Currentproject.AllReports.Count
'Number of Modules
Currentproject.AllModules.Count 'does not include object modules

Easy as can be!

MS Access – Why Convert Your Database Into an MDE or ACCDE?

Yet again, another questions that always comes up over and over.

 

What is an MDE/ACCDE database?

So why do we normally recommend that you convert your database (mdb/accdb) into a compile version (mde/accde) before deploying to your end-users?

The short answer is that it minimizes the amount of access the users have to mess around with your database.

 

So how does it work exactly?

An MDE/ACCDE is simply a compiled version of the original MDB/ACCDB.

This means that since it is already compiled, there will be a slight performance benefit.  Also, since it is a compiled version, this ensures that your code is sound.

The main benefits however however are that a compile version is locked down.  Users cannot edit Forms, Reports or VBA code.  Actually, the VBA isn’t even accessible.  That said, Tables, Queries and Macros remain completely unsecured.

 

What They Are Not

As stated above, they do not secure Tables, Queries or Macros in any way so you need to take any necessary steps to restrict your users access to these elements.

AN MDE/ACCDE does not secure your data in anyway!  For this you must secure your back-end by using the Encrypt with Password command  or any other technique of your choosing (be very careful creating homemade encryption solutions!).

 

Warning

MDE/ACCDE files cannot be edited/developed.  You need to keep the originating MDB/ACCDB to be able to continue to develop your database.

 

Final Thoughts on MDE/ACCDE Files

So by distributing your database as an MDE/ACCDE your ensure that your users cannot change your forms, report and VBA.

Now, if you disable the shift-bypass, create a proper AutoKeys macros to disable certain keystrokes, create your own command bar or ribbon and hide the object browser/navigation pane and then convert it into an MDE/ACCDE your can truly minimize any impact your users can have on your application.  If your database is an 2007+ format, you can take one extra step; once you have converted your database into an ACCDE, change the the extension to ACCDR to force it to run in runtime mode locking down even further what your users can do.

 

Making an ACCDE

Per Paulla’s inquiry in the comments below, here are the instructions for actually creating an ACCDE from your ACCDB.

  • Open your accdb
  • Click on the File tab
  • Click on Save As
  • Select the Make ACCDE
  • Click on the Save As button
  • You will the be prompted for the path/filename to use, so navigate and enter the value of your choosing
  • Click Save

Note the VBA must first compile without any issues before being able to create an ACCDE, otherwise the process will fail with an error notification.

Determine Installed Version of MS Access – VBScript

Below is a simple VBScript to determine the current version of MS Access installed on the computer

Dim oRegistry
Dim sKey
Dim sValue
Dim sAppVersion
Const HKEY_CLASSES_ROOT 	= &H80000000

Set oRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}//./root/default:StdRegProv")
sKey = "Access.Application\CurVer"
oRegistry.GetStringValue HKEY_CLASSES_ROOT, sKey, "", sValue
sAppVersion = Right(sValue, Len(sValue) - InStrRev(sValue, "."))
MsgBox sAppVersion & ""
Set oRegistry = Nothing

Simply save the above in a text file and name it as you please with the extension vbs, close and save. Then double click on it and it should return a message box with the version number of your Access currently configured MS Access installation.

WebBrowser ActiveX Control – Google Maps Invalid Character Scripting Error

I had an MS Access database which had been in place for over a year and that was working just fine.  One day, at the beginning of October 2013, my client informed me that they had recently started receiving a Scripting error (see the image below) every time the form displayed a map (Google Maps) of an address, any address.

WebBrowser_GoogleMaps_Script_ErrorAt first, I assumed it was a Google glitch and figured we’d give the Google team a chance to rectify whatever the issue was.  That said, days, weeks, went by and the problem remained.  So it was time to do some more digging.

During the testing phase. I was able to confirm that using the same URL directly in a web browser (IE, FireFox, Chrome) all displayed just fine without any errors.  Furthermore, the error itself did not occur on every machine.  This leads me to seriously believe it has to do with some windows update that is applied to certain computers and not others.  If it were truly a Google issue, it would be generalized, which it evidentially is not!

I also, after some reading, was lead to believe it was due to Internet Explorer’s ‘Compatibility Mode’, but after performing a few simple tests, this was quickly ruled out as the source of the problem.

At this point I was at a loss as to what to do, so since I am an MS Access MVP, I sent an e-mail out to my fellow Access MVPs to see what others might suggest.  Alex Dybenko, provided me with the key piece of information to figure out the root cause of the problem.  Using a nifty piece of software that he had, he was able to identify that the WebBrowser control (with my MS Access database) was returning that it was running as a User-Agent MSIE 7.0 on his system, even though his actual IE browser was MSIE10.0?!  After performing the same test, I identified that my WebBrowser controls was reporting to be MSIE6.0 rather than MSIE10.0.

Hence, the WebBrowser control is actually running in a dumbed down mode, when compared to the actual version of Internet Explorer that you installed.  So apparently, Google Maps does not play nice with older versions of Internet Explorer.

Knowing what the actual issue what, I when digging online to see what could be done.  I eventually came across an obscure registry key that can be used to tell a program to use a specific IE emulation version.

The solution is a simple Reg Hack to for the WebBrowser control to switch it’s emulation to a more recent version of MSIE (MicroSoft Internet Explorer).

Open RegEdit and navigate to the following Registry Key (and yes you need to perform this registry hack on every computer running your database!) depending on the application using the WebBrowser control (not your OS!).

32-bit
HKLM\Software\Microsoft\Internet Explorer\Main\FeatureControl\FEATURE_BROWSER_EMULATION

32bit-FEATURE_BROWSER_EMULATION

64-bit
HKLM\SOFTWARE\Wow6432Node\Microsoft\Internet Explorer\MAIN\FeatureControl\FEATURE_BROWSER_EMULATION

64bit-FEATURE_BROWSER_EMULATION

Then create a new DWord entry in which you specify the program executable that has the WebBrowser control in it  as the ‘value name’ and use one of the following values for the Decimal ‘value data’

7000: IE7 Emulation
8000: IE8 Emulation
8888: Force IE8 Emulation
9000: IE9 Emulation
9999: Force IE9 Emulation
10000: IE10 Emulation
10001: Force IE10 Emulation
11000: IE11 Emulation
11001: Force IE11 Emulation

So since we are talking about an issue with the WebBrowser within our MS Access database, we create a DWord with msaccess.exe for the Value Name and I used 9999 as the value data. Error message gone.

As usual, you could easily create a bat, vbscript, VBA routine to push this into the registry as part of your program or installation routine!  I was recently asked if I could supply an example of such code.  As such, feel free to download  one of my samples:

WebBrowserEmulationRegistryHack_MSAccess
WebBrowserEmulationRegistryHack_MSExcel

That said, be forewarned that improper editing of the Registry can permanently damage your computer to the point of it not working anymore.  I assume no responsibility whatsoever for the sample/code provided.  It is provided for educational purposes only, and it is your responsibility to validate and test it to ensure it works properly.  It was put together rapidly in the hopes it might help, but has not been fully tested.

Also note, since it impacts the executable, in our case msaccess.exe, this hack will impact all databases containing WebBrowser controls. This could theoretically cause problems with older databases, so you could easily push the registry hack at the startup of your db and delete it upon closing, to try and minimize impacting other databases. Obviously, if they are opened at the same time, there is little you can do.

This hack works for MS Access databases, MS Excel, WPF, Visual Studio projects, anything that uses the WebBrowser ActiveX control.

Hopefully this will help someone else!

References

If you are looking for further information, fellow MVP Tom van Stiphout recently brought the following article to my attention: Web Browser Control – Specifying the IE Version
MSDN – Internet Feature Controls (see the section entitled Browser Emulation)

 

Extra

If you aren’t looking to fix the issue, but rather simply make the error not display, you can always place the following in the Form’s load event

Me.WebBrowserControlName.Object.Silent = True

MS Access – Securing Access to the Back-End Database

Limit the settings within your database

Go into the database Options (Office Button->Access Options->Current Database) and uncheck elements such as:

Display Navigation Pane

Optionally:

Allow Full Menus
Allow Default Shortcut Menus

While you are there ensure that Track name AutoCorrect Info is not checked. As well, you should not ever use Compact on Close.

Control the type of file your distribute

Be sure to convert (Database Tools->Make ACCDE) your front-end into an *.accde file for distribution to your users.

Remember that error handling within your procedures becomes very important in mde/accde since users cannot debug the code.

Remember though that you need to always keep the accdb, since that is the format that allows you to continue development.

Make your own Ribbon

If you were to create your own Ribbon(s) and disabled all the built-in ones and hide the nav pane, you could basically completely limit what the user sees, and has access to. Thus they would only be able to open whatever you’ve created buttons, and other controls, to open.

Security

Either create a login form, or a table that you cross-reference the active network usernames against to stop nosy employees from getting into the database in the first place.

I also urge you to take a look at Allen Browne excellent post: http://allenbrowne.com/ser-69.html

Folder Permissions

Set the Back-End folder permission to ‘Traverse permissions’: “it allows users access to a folder on a network share without them being able to see the folder without knowing its full address. Essentially they have access to a dummy folder on the share, but they can’t access the storage folder unless they know the exact address.” – Scott Gem

Also setup the permissions on the folder so that only your specific users can access it.  This will  stop users who have no business in your database from ever being able to see it, let alone mess around with it.

Disable the SHIFT Bypass

Password Protect the Back-End

Disable AutoKeys

Set your Table To Hidden

MS Access – Database Created with the 32-bit version …

Have you ever developed a database using a 32-bit Access version and then tried to deploy it on a 64-bit version and receive the error: “This database created with the 32-bit version of Microsoft Access.  Please open it with the 32-bit version of Microsoft Access”.

32-bit error when opened on 64-bit version of MS Access

First let me state that it is not recommended to use the 64-bit of MS Access.  So right from the start, this is a bad idea!

Regardless, what is the solution.  Actually, it isn’t a very hard fix.

Make a  copy of the 32-bit database and recompile on a 64-bit version of MS Access.  So basically at the end of the day, you need to start maintaining 2 versions of your db: a 32-bit version and a 64-bit version.  Once you copy over the 32-bit version to the 64-bit version, Decompile the database, Compact and Repair, Compile (address any issue that may arise such as missing references, etc…) and finally Compact and Repair.  Then you can distribute your database or convert it to an mde/accde format…

So yes, as a developer, if you are going to be distributing a database to users who have a 64-bit version of MS Access, then you need to also have a 64-bit version of MS Access.