Field Size Option Not Working in Access 2016

That’s right, another MAJOR bug has reared its ugly head for MS Access 2016 users in which the combo box to assign a Field Size no longer displays any values!

For the past couple of days now, we have been seeing an increasing number of reports of the above problem in various forums.

Firstly, the MVP group have already notified the Access Dev Team, so they are looking into the matter. Now, how long it will take to see a patch, your guess is as good as mine. It took months for them to patch the missing image issue.

Also, in their defense:

One needs to remember that the problem was caused by a change made outside the Access codebase. But its Access that needs to be recoded. And that means making sure there isn’t a domino effect from any code changes. – Scott Gem (MS Access MVP)

As for potential solution to the current problem, there are 3 workarounds (taken from MS Access 2016 No Longer Displays Field Size Options).

Workaround 1 – Change Default Property

Select: File->Options->Object DesignersIn the Table design view section use the Default number Field Size ComboBox to select one of the following:

Byte, Integer, Long Integer, Sing, Double, Decimal, Replication ID

Then click “OK” to save your options

When you return to the Table designer and create a new Number field it will now default to your selected Field Size.

Every time you wish to change Number Field Sizes you will need to return to Options and re-select the appropriate default Field Size.

This workaround will not allow you to change the Field Size for existing number fields.

Workaround 2 – Use SQL

It is also possible to use SQL DDL to create a new table using commands.

If you are interested in learning more regarding the DDL language, below are a few links to brush up on the subject.

Microsoft Access: DDL Code Examples (Allen Browne)
Access Data Definition Language (DDL): Creating and Altering Tables On The Fly (Danny Lesandrini – Database Journal)
How to use common Data Definition Language (DDL) SQL statements for the Jet database engine (Microsoft)

Update 2016-06-19

Ken Sheridan was kind enough to post some sample code for everyone.

Simply execute a DDL statement, e.g.
 ALTER TABLE MyTable
 ALTER COLUMN MyNumber Double;
Do this in the query designer in SQL view.

Workaround 3 – Copy/Paste

Another workaround that, fellow MVP, Joe Anderson mentioned in a recent forum discussion is that you can still copy/paste fields from an existing table to another one. So if you already have a table with the Field type, size, … you need to create, then you can simply copy/paste it. Or if you have an existing table close to what you need, you can copy/paste it and then rename the table/fields as required.

Update 2016-06-21

Tom van Stiphout, Access MVP, was kind enough to put together a table with each number data type to copy/paste from. This is probably the easiest solution to the current dilemma until MS comes out with an official fix. Taken from one of many Forum threads he has contributed to:

Here is a simple workaround:
Download this file I posted: https://1drv.ms/u/s!AnmKsZFxs8_KhoEukCFECeXiwA-fvA
It has a single table, with a number field of each type. This will allow you to copy/paste while designing your table.

Can’t get any easier than this! Thank you Tom.

Let’s keep our fingers crossed the Dev Team come out with a fix promptly for all our sakes!

Update 2016-06-19

From what we have been told a fix will be available soon. I’m going to go out on a limb here and guess within this week. Which would mean just 2 weeks after it was reported. – Scott Gem (MS Access MVP)

Let’s hope Scott is right!

Update 2016-07-04 A Fix has Finally been Released

Over the course of the weekend we have seen a update start getting rolled out through Office365 and Windows Update.

For office365 there are multiple update channels so some people may get it sooner than others. The Access team doesn’t control the release of Office updates … – Scott Gem (MS Access MVP)

George Hepworth confirmed the update for the MSI versions both 32 and 64 bit versions.

So make sure to perform an update of your systems and this problem should be a thing of the past.

So, for MSI installation you simply run Windows Update.
For, Office365 CTR installations, File -> Account -> Update Options -> Update

Anyone Can Make a Database, NOT!

Over the course of my career, I have been amazed how often I see employers getting employees with no database experience, and more importantly training, to try and develop in-house mission critical databases thinking to themselves that anyone can create a database. Sadly, nothing could be further from the truth!

MS Access is unlike Excel, PowerPoint and Word and requires substantial training and experience to develop a proper solution with. The simple fact of the matter is that, by default, MS Access creates a flawed structure to begin with when you start your database by creating a single database file which novice developer have no knowledge of. Then add to the fact that in most cases people seem to believe they can simply migrate an Excel worksheet into a single database table thus completely starting off with the wrong table normalization structure upon which every other aspect of the database is built upon.

The simple fact of the matter is that by the time I am asked to intervene, companies have already spent 10’s of thousands of dollars in employee time to develop databases which must be complete restarted because of basic flaws in table design requiring redesign of Tables, Queries, Forms, Report and VBA code!

Case Study 1

Let use a formal, real-life, anonymous examples to illustrate the problem.

I had a client come to me after spending 2 years and 22,000$ to develop a database for their business. They originally came to me to ask me to review it because of performance issues.

I reviewed the database that had problems in every corner, table structure was not properly normalized, queries were not optimized, forms were a disaster and VBA code was a mess to put it politely. In one evening, I manage to prove how bad the VBA coding was by redesigning a single report (well the VBA code used to generate it) and reduced the reports load time from 2minutes 37seconds down to 3 seconds, and still was purely through proper VBA coding I still had not addressed table structure!

So in the end, by not hiring a professional from the start, this client:

Lost 2 years
Lost 22,000$
Lost the time and fees associated in reviewing his database
Would have to restart everything (time and $$$ associated with that)
Would incur extra costs for extracting their data and importing it into the new solution

What was sad in this case was that the client did not have any funds left to rectify the problems with there database. They believed that I was going to tell them that everything was optimal and it was an Access limitation, but nothing could have been further from the truth. Incompetent development was entirely at fault, but the true root cause was poor management thinking someone who had never created a database could do so at an enterprise level.

In this instance, the cost would have probably been in the same order of magnitude, but I would have been able to deliver a solution in a fraction of the time, probably 3-4months instead of 2 years! Obviously, it would not have suffered from the performance issues as well.

Case Study 2

I have an acquaintance that works for a government agency and has spent 2 weeks developing a database, well the beginnings of one at least. They were having certain difficulties and contacted me for some pointers. In one evening, approx. 4 hours, I completely redid the entire databases from scratch that they had spent 2 weeks on and had made it substantially better. So what is cheaper I ask you, 2 weeks of your employee’s time to end up with a subpar database which doesn’t work properly or a couple hours of a consultant’s time?!

If we were to compare, in this case we’d be looking at:

Employee :: 55 $/hr x 36 hr/wk x 2 wks = 3960$
Consultants :: 100$ $/hr x 4 hrs = 400$

The above represent a 890% decrease in cost and a 1700% decrease in time, simply by hiring a professional developer.

The other aspect of not hiring a professional is that quite often managers think they are saving money by getting an employee to tackle the database. The simple fact of the matter is nothing could be further from the truth, as illustrated by the brief examples above. When you hire a professional developer, yes the hourly fee is greater than that of your employee, but it will take an employee countless hours (if not days, weeks, months) to accomplish what an experienced developer can do in a couple of hours. Not to mention the fact that a developer, such as myself, has a toolbox of existing tools, that we can integrate into a database in seconds.

By hiring a professional, typically, you:

Reduce costs (sound counter intuitive, but my experience seems to prove this point)
Reduce development time by orders of magnitude
Increase the quality of the final solution

One last word of caution is that Access is not like any other database or programming language. Please do not think that hiring a DBA with a great deal of experience with ORACLE (for instance) makes them a good Access developer. Just as much as an experienced C++ developer does not make them a good Access developer (I’ve had the pleasure of cleaning up after this mess!). Now, like when hiring any professional (developer, contractor, …) you need to do your due diligence when choosing who to deal with.

Ask for references
Ask to see samples, screenshots of previous work
Ask how long they’ve been in business
Ask how many Access databases they’ve created
…

Now this post isn’t meant to discourage anyone wanting to start learning MS Access, but don’t expect to come out of the gates and have your 1st database work properly, nor your 5th, … It takes some serious development time to tame MS Access!!! Now, if you understand the learning curve ahead of you and are still interested, then let the good times roll.

MS Access – Show All Records if Form Control is Left Empty

Have you ever used a form to allow the user the ability to make selections and then applied them as criteria in a query?

Have you ever wonder about how can I allow the user to make a selection to perform a filter, but if it is left blank then display all the records?

The solution is rather simple, and one that I picked up several years back thanks to a discussion or two involving Marshall Barton and John Vinson (2 Access MVPs).

So normally you’d create a query criteria similar to:

=Forms!YourFormName.YourComboBoxName

To enable it to handle a Null entry, and thus display all the records if the control is left unpopulated, you simply change it to:

=Forms!YourFormName.YourComboBoxName Or Forms!YourFormName.YourComboBoxName Is Null

Now I’ve seen people using SQL along the line of (I have to admit I did so myself when I first started using Access – we all started somewhere!):

Like "*" & Forms!YourFormName.YourComboBoxName & "*"

and while it will work, LIKE is a much less efficient search mechanism in any database (not just Access), so it is a best practice to use the criteria as demonstrated by John and Marshall for optimal performance. In small recordsets either technique will work just fine, but when you start dealing with larger recordsets, the difference becomes very noticeable, very quickly since LIKE cannot use indexes to apply the criteria and has to go row by row.

Determine the Full Path of a Mapped Drive

If ever you are trying to reverse engineer the full path of a mapped drive, nothing could be easier (once you know how!).

Open a command prompt
1. Windows + R (this will open the Run dialog as shown below)
  1. enter cmd in the Open: control
  2. press OK (this will launch the command prompt)

Windows Run Dialog

In the command prompt, simply enter net use and press Enter

Command Prompt - net use

It will then give you a breakdown of all the mapped drive with their full paths.

VBA – Trailing Character

It is common to have to ensure the formatting of certain string to be consumed by other procedures.

A common example of this can be when trying to building a path from a string entered by the user. In some cases, they include the trailing slash and in other it may be omitted. So how do you code for these types of scenarios where you never know exactly what you are going to be handed as data?

Now if you are only ever working with paths and trailing slashes, then Allen Browne had created the following function:

Function TrailingSlash(varIn As Variant) As String
    If Len(varIn) > 0 Then
        If Right(varIn, 1) = "\" Then
            TrailingSlash = varIn
        Else
            TrailingSlash = varIn & "\"
        End If
    End If
End Function

But I wanted a slightly more versatile function that could be used in other cases as well and didn’t want to create a new function everytime the trailing character(s) was different. As such, several years ago I created the following function for exactly these cases!

'---------------------------------------------------------------------------------------
' Procedure : CheckTrailingChars
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Ensure the ending character(s) match the string supplied.
'             Primary purpose is to ensure paths include the trailing \
' 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).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sString   : The string to check the last characters
' sChar     : The last characters to look for
'
' Usage:
' ~~~~~~
' CheckTrailingChars("C:\Users\Daniel\Desktop\XLS Testing", "\")
'             Returns C:\Users\Daniel\Desktop\XLS Testing\
' CheckTrailingChars("C:\Users\Daniel\Desktop\XLS Testing\ ", "\")
'             Returns C:\Users\Daniel\Desktop\XLS Testing\
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-05-13              Initial Release
'---------------------------------------------------------------------------------------
Public Function CheckTrailingChars(sString As String, sChar As String) As String
    On Error GoTo Error_Handler

    If Right(sString, Len(sChar)) <> sChar Then sString = sString & sChar
    CheckTrailingChars = sString

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

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

MS Access – VBA – Determine if a Form Exists or Not

I was writing a procedure for a client some time ago that interacted with a series of forms that came from a table, the problem being was that some of the specified forms no longer existed. When faced with this we have 2 options:

Use error trapping to trap this specific error and move on
Create some function to validate if the form exists before executing the code in the first place

I chose option 2 and set out to create a simple function to do exactly that. This is what I put together:

'---------------------------------------------------------------------------------------
' Procedure : FrmExist
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine whether or not a form exists in the current database
'             Returns True if it exists, False if it does not
' 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).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFrmName    Name of the form to search for
'
' Usage:
' ~~~~~~
' ? FrmExist("frm_Login")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-03-17              Initial Release
'---------------------------------------------------------------------------------------
Function FrmExist(sFrmName As String) As Boolean
    On Error GoTo Error_Handler
    Dim frm                   As Access.AccessObject

    For Each frm In Application.CurrentProject.AllForms
        If sFrmName = frm.Name Then
            FrmExist = True
            Exit For    'We know it exist so let leave, no point continuing
        End If
    Next frm

Error_Handler_Exit:
    On Error Resume Next
    Set frm = Nothing
    Exit Function

Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FrmExist" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Then I could easily perform checks an act accordingly with a single call to my function, doing something like:

If FrmExist("frm_Login") = True Then
    'Do something because the form was found
Else
    'Do something because the form was not found
End if

MS Update KB3085515 Breaks Access 2010?!

Yes, it has been reported, and confirmed by many now, that one of Microsoft’s updates (KB3085515) released March 8th, 2016 breaks MS Access. From what I’m seeing it appears to limit itself to affecting only Access 2010 ~~compile versions of databases (accde, ade, accdr and I’m assuming mde files). Accdb seem to continue to work normally.~~. Others even report it affecting accdb files, so it appears to potentially impact any 2010 database file.

So if you wake up this morning and you are having issues with your database that previously worked just fine, then check and see if KB3085515 was installed recently, and if so uninstall it. You can find Microsoft’s detailed instructions for uninstalling this update at: March 8, 2016, update for Office 2010 (KB3085515).

For more details on this problem, see: KB3085515 breaks MS Access 2010 reference.

I reported the issue, after seeing the thread pop-up yesterday evening (Gina Whipp brought it to the attention of the Access MVPs), to the proper authorities at Microsoft. So, they have been made aware of the issue and let’s just hope Microsoft pushes another update to fix this flawed update soon!

Update 2016-03-10

Also, the Access Dev. Team finally made a post on their blog about the problem and are supposed to eventually post more information, so you may want to keep an eye on:

You may encounter errors with your ACCDE/MDE files and/or wizards after installing the March update for Office 2010 (KB3085515)

Update 2016-03-11

Several people have reported that even uninstalling the update that cause all these problems did not undo the damages it caused and that they solution was to manually replace VBE7.DLL file put in place by the update with a prior version they found by searching their computer:

For me the unistalling of the KB did not work unfortunately. But reading the about the update of the VBE7.DLL made me experiment a bit. I searched my machine for all versions of this DLL and found one with version 7.0.16.28 (this was the newest before the update on my machine) Renaming c:\Program Files (x86)\Common Files\microsoft shared\VBA\VBA7\VBE7.DLL to VBE7.OLDDLL and then copying the older version of the DLL to this location solved the problem for me, fortunately. – Hans Peter

MS Access – VBA – Get Record Count

I’ve recently been working on doing some serious code cleanup in a client database, and also wanted to try and do some optimization when possible.

Premise

One thing I’ve been curious about was getting record count. The database that I’m working on, the previous developer would check the record count before opening any form or report, so it is checked constantly. Now moving beyond to horrific setup of checking things in this manner rather than, in the case of reports, simply using the No Data event (for instance), it got me thinking a bit about what is the best way to get a record count.

So I put out to test a couple different approaches to getting a Record Count for a recordset to see which was optimal.

Methods

Below are 2 major technique with a total of 5 different variations. What I did is I create a few procedures to perform a thousand iterations over each technique to log the execution time and then created a simple query to gathered an average value to compare with.

Method 1 – SELECT *

The existing developer would used code similar to

Dim rs                    As DAO.Recordset
Dim lRecCount             As Long
Set rs = db.OpenRecordset("SELECT * FROM TableName;")
rs.MoveLast
lRecCount = rs.RecordCount
rs.Close
Set rs = Nothing

Method 2 – DCount(*)

Now I was curious how this would compare to using DCount()

Dim lRecCount             As Long
lRecCount = DCount("*", "TableName")

Method 3 – SELECT pk

Then this got me wondering how each of these perform when running on a single fields.

Dim rs                    As DAO.Recordset
Dim lRecCount             As Long
Set rs = db.OpenRecordset("SELECT [pkFieldName] FROM TableName;")
rs.MoveLast
lRecCount = rs.RecordCount
rs.Close
Set rs = Nothing

Method 4 – DCount(pk)

Dim lRecCount             As Long
lRecCount = DCount("[pkFieldName]", "TableName")

Comparing .OpenRecordset(“SELECT * …. vs. DCount(“*” …., DCount was the clear winner. Switching DCount to use the PK field instead of * negatively impacted performance but it was still slightly faster than the OpenRecordSet technique. But only working with the PK field with the OpenRecordset technique (.OpenRecordset(“SELECT [pkFieldName] FROM …) drastically improved performance to the point that it was noticeably faster than any other technique.

Method 5 – SELECT Count(pk)

Out of personal curiosity, I decided to quickly test using the Count() in a SQL Statement to see how it fit into the grand scheme of thing using something along the lines of

Dim rs                    As DAO.Recordset
Dim lRecCount             As Long
Set rs = db.OpenRecordset("SELECT Count([pkFieldName]) AS RecCount FROM TableName;")
lRecCount = rs![RecCount]
rs.Close
Set rs = Nothing

This approach performed very similarily to that of DCount, only slightly edging out DCount.

Results

Here are the detailed results of my findings.

	Execution Time (ms)	Delta w/ Method 3 (ms)	% Decrease in Performance
Method 1 – SELECT *	14.37	8.08	-78%
Method 4 – DCount(pk)	13.1	6.81	-70%
Method 2 – DCount(*)	9.7	3.41	-43%
Method 5 – SELECT Count(pk)	9.09	2.8	-36%
Method 3 – SELECT pk	6.29	–	–

In conclusion, based on my testing, the following (Method 3 – SELECT pk) was the fastest when working on MS Access Linked tables.

Let’s Further the Discussion

Further considerations to extend this discussion. My tests were performed on a relatively small table (5k records) with 163 fields (a mess of a table that I’m working on cleaning up), so it would be very interesting to perform the same tests on a table with a million or more records to compare and a properly normalized table too.

Update 2016-02-25

Okay, so I wanted to further the discussion (simple personal curiosity) and see for myself how the above stood up to larger recordsets. So I downloaded George Hepworth’s Test Bed database (300k records) and set out to run the same set of tests as above. Here were my results

	Execution Time (ms)	Delta w/ Method 5 (ms)	% Decrease in Performance
Method 1 – SELECT *	21.77	15.61	-112%
Method 3 – SELECT pk	20.88	14.72	-109%
Method 4 – DCount(pk)	6.51	0.35	-6%
Method 2 – DCount(*)	6.46	0.3	-5%
Method 5 – SELECT Count(pk)	6.16	–	–

Conclusions

Recordset_Performance_Analysis

It is pretty obvious from the data above that Method 1 is to be avoided at all cost (this was what the previous developer used throughout the db I am working on, so I have some work ahead of me to clean it all up), but beyond that the interpretation become more tricky.

One could say:

for small recordsets use Method 3 – SELECT pk
for larger recordsets use Method 5 – SELECT Count(pk)

It does make perfect sense that Method 3’s performance would be based upon the size of the recordset as it has to travers the entire set (.MoveLast) to get an accurate count. Thus, the larger the recordset the bigger the performance hit.

Then again, someone might split the difference and simply say use Method 5 for all situations as the difference is negligible.

Let’s Further the Discussion Even More

What’s left to discuss you might ask? Well, we could push things further and:

explore how each technique reacts when applying filters to restrict the count.
explore how these technique behave through ODBC

but this will have to wait for another moment.

VBA – Find Term in VBA Modules/Code

Sometimes I need to perform searches in the VBA code to try and cleanup client databases, and sometimes Mz_tools, although an exceptional add-in, just doesn’t give me enough flexibility to perform the type of searches I need. It is rare, but it does happen.

Below is a generic procedure to search through all your VBA code for a term. As it stands below, it offers little benefit over Mz-Tools, but it allows you the flexibility to modify it as you see fit to do things Mz-Tools simply can’t.

For instance, I needed to search through the VBA code of a database looking for a given term, where the next line included a specific function. There is simply no way to do this with Mz-Tools, but I added one line to my code below and presto I had my listing to work off of. And let me tell you when you are dealing with complex databases with 100’s of forms modules, reports module, modules, class modules, a routine like the one below can make a drastic improvement!

I must also state that I was surprised by the speed of such a search, it is fast considering the quantity of code I was combing through when I last used it. Access & VBA never cease to amaze me sometimes.

Continue reading →

VBA – Count the Number of Occurrences of a String or Character within a String

I was trying to help someone in a forum that had a simple question “How can I count the number of time a string/character is found within a string?”

I originally answer by referring them to a procedure written by someone that looped through each character within the string. But this seemed wrong.

So after a few minutes of simple reflection, it hit me that nothing could be simpler! Access/VBA has built-in function to handle this very easily. Below is the procedure I came up with.

'---------------------------------------------------------------------------------------
' Procedure : CountOccurrences
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Count the number of times a string is found within a string
' 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: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sText         String to search through
' sSearchTerm   String to count the number of occurences of
'
' Usage:
' ~~~~~~
' CountOccurrences("aaa", "a")               -> 3
' CountOccurrences("514-55-55-5555-5", "-")  -> 4
' CountOccurrences("192.168.2.1", ".")       -> 3
' CountOccurrences("192.168.2.1", "/")       -> 0
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2016-02-19              Initial Release
' 2         2019-02-16              Updated Copyright
'                                   Updated Error Handler
'---------------------------------------------------------------------------------------
Function CountOccurrences(sText As String, sSearchTerm As String) As Long
    On Error GoTo Error_Handler
 
    CountOccurrences = UBound(Split(sText, sSearchTerm))
 
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: CountOccurrences" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

So, as you can see, by simply combining 2 built-in VBA functions we can quickly and efficiently get our answer. VBA can be so beautiful at times!

What’s even nicer about this solution is that it is not Access specific, so it will work in Access, Excel, Word, … any VBA project.