Access 2016 Runtime Format Function Issue

In the 2nd screenshot in noticed :
Q45221TeanIdName

Hi John,

I'm not sure what you are asking.

If it is why this is different between the two queries (Q45221TeamIdName versus Q45221TeanIdName) it is because I made a typo which doesn't affect the actual operation.

Thanks,
Bob C.

Ok then...
I must say i am not sure what is the issue
Using Format should be enough
Format(SomeValue,"000") should cover all numbers till 999...if your number is greater then you need more zeroes...

Hi John,

The issue is that Access 2016 Runtime does not work correctly with the Format Function.

Note that if you try it with Access Full Version with the /Runtime switch it works correctly.

Therefore I believe that I have identified a bug in the Microsoft Access 2016 Runtime software version as follows which appears to be the latest version.  I also note that this is the same file that is specified for Access 2019 Runtime..
- accessruntime_4288-1001_x86_en-us.exe

I have also determined since opening this question that it also doesn't work correctly with the following two additional Funcrtions.
- CStr(InputFieldLong).
- CLng(InputFieldString).

So the question is whether or not there is something I can use in their place?

Thanks,
Bob C.

Interesting that format doesn't return correctly formatted string in Runtime...i don't know if its reported by anyone
What you can do is to count the Len of the CStr(InputFieldLong) and work with this
For example you want 6 digits
Len(CStr(InputFieldLong)) = 3
FinalOuput ="000" & CStr(InputFieldLong)
so if CStr(InputFieldLong) = "123" this would result
FinalOuput ="000" & "123"
000123

You could create a small function to generate the needed padding zeroes.

Your problem is more likely a missing reference. The full version may correct this, while the runtime won't.
So, go to menu Tools, References and double-check. Remove any reference not needed and correct any marked as MISSING.

Hi Gustav,

I am familiar with 'References' and this was my first thought.  I'm attaching my documentation in this regard, much of which was developed in conjunction with EE Experts.

The problem with this is that I can't see any way to check the References in the Access 2016 Runtime since there is no Ribbon selections other than File and it isn't accessable through the File option.

Is there a way to get at it?

Thanks,
Bob C.
V10109-VB-Code---Tools---References.docx

You can create a test database having the same references set, and then - for example called from an AutoExec macro - code like this:

Option Compare Database
Option Explicit

' Verify Access' external references.
' Returns True if all references are valid.
' Will run quietly, if called with parameter Quiet as True.
'
' Is intended to be called as the first command from the AutoExec macro
' followed by a call to a function - for example CompileAndSave - that will
' "compile and save all modules".
'
' 2018-07-10. Cactus Data ApS, CPH.
'
Public Function VerifyReferences( _
    Optional ByVal Quiet As Boolean) _
    As Boolean
    
    ' Settings for message box.
    Const Title     As String = "Missing Support Files"
    Const Header    As String = "One or more supporting files are missing:"
    Const Footer    As String = "Report this to IT support." & vbCrLf & "Program execution cannot continue."
    Const Buttons   As Long = VbMsgBoxStyle.vbCritical + VbMsgBoxStyle.vbOKOnly
    
    Dim Reference   As Access.Reference
    
    Dim Item        As Integer
    Dim Guid        As String
    Dim Major       As Long
    Dim Minor       As Long
    Dim Prompt      As String
    Dim Broken      As Boolean
    Dim SecondRun   As Boolean
    Dim Result      As Boolean
    
    Do
        ' Loop (a second time) the references and build a list of those broken.
        Broken = False
        Prompt = ""
        For Each Reference In Access.References
            If Reference.BuiltIn Then
                ' Nothing to check.
            ElseIf IsBrokenExt(Reference) Then
                Broken = True
                Prompt = Prompt & Reference.Guid & " - " & Reference.Name & vbCrLf
            End If
        Next
        
        If SecondRun Then
            ' Only shuffle the references once.
            Exit Do
        ElseIf Not Broken Then
            ' All references have been verified.
        Else
            ' Try to remove the last non-broken reference and add it back.
            ' This will shuffle the Reference collection and may or may not
            ' cause a broken reference to be added back.
            Item = Access.References.Count
            Do
                Set Reference = Access.References.Item(Item)
                If Not Reference.BuiltIn Then
                    If Not IsBrokenExt(Reference) Then
                        ' Record the reference's identification before removal.
                        Guid = Reference.Guid
                        Major = Reference.Major
                        Minor = Reference.Minor
                        ' Remove this reference.
                        Access.References.Remove Reference
                        ' Add back the removed reference.
                        Access.References.AddFromGuid Guid, Major, Minor
                        Exit Do
                    End If
                End If
                Item = Item - 1
                ' Exit loop when a built-in reference is met.
                ' These are always the top ones.
            Loop Until Reference.BuiltIn
            SecondRun = True
        End If
    Loop Until Not Broken
    
    Result = Not Broken
    
    If Result = False And Quiet = False Then
        Prompt = Header & vbCrLf & vbCrLf & Prompt & vbCrLf & Footer
        VBA.MsgBox Prompt, Buttons, Title
    End If
    
    Set Reference = Nothing
    
    VerifyReferences = Result
    
End Function

' Performs an extended check if a reference is broken, as the
' IsBroken property doesn't check for unregistered files. Thus,
' an unregistered reference may fail even if not marked MISSING.
'
' 2018-07-09. Gustav Brock. Cactus Data ApS.
'
Public Function IsBrokenExt( _
    ByRef Reference As Access.Reference) _
    As Boolean

    Dim NotBroken   As Boolean
    
    On Error GoTo Err_IsBrokenExt
    
    ' If the reference is not registered, calling property FullPath will fail.
    ' Even if the file exists in the Virtual File System, GetAttr will find it.
    If (VBA.GetAttr(Reference.FullPath) And vbDirectory) <> vbDirectory Then
        ' FullPath is valid.
        NotBroken = Not Reference.IsBroken
    End If

Exit_IsBrokenExt:
    IsBrokenExt = Not NotBroken
    Exit Function
    
Err_IsBrokenExt:
    ' Ignore non-existing servers, drives, and paths.
    Resume Exit_IsBrokenExt
  
End Function

Select allOpen in new window

And you may get away with a reccompile if any references has been changed:

' Compile and save all (other) modules.
' Is intended to be called from the AutoExec macro after having verified the references.
'
' After tampering with the references, the application may appear to be compiled, which it is not.
' This function will not fail - even if the application is compiled or appears to be - if it is
' kept on it own in a separate module.
'
' It is not a subfunction as only functions can be called from the AutoExec macro.
'
' 2018-07-06. Cactus Data ApS, CPH.
'
Public Function CompileAndSave()

  ' The command:
  '
  '   Application.RunCommand acCmdCompileAndSaveAllModules
  '
  ' can not be used, as no module is open when the AutoExec macro runs.
  ' Thus, use this undocumented SysCmd() call.
  Call SysCmd(504, 16483)

End Function

Select allOpen in new window

The files are attached as well.
SysCompileAndSave.bas
SysReferencesVerify.bas

Are you sure you need the VBA Extensibility reference? That generally is used for working with VBA objects in code (i.e. changing a Module or something like that)? I don't think I've ever deployed an Access database with that reference.

Are you sure you need the ADO Recordset library? What are you doing with ADO? If you're just opening connections and recordsets, you almost certainly don't need that library.

The Scripting runtime can sometimes be blocked by virus engines.

Office references can cause troubles if your machine is running a version higher than the target machines.

Assuming you're using early binding, you can determine if your database needs a reference by doing this:

1. Compile the code. If any errors occur, fix them and continue compiling until the menu item is disabled.
2. Remove the reference. BE SURE to make note of the location of the reference in case you need to restore it.
3. Delete the reference.
4. Compile again. If you have no errors, you almost certainly do not need that reference.

Besides all the nice referencing code posted by Gustav how about going simple
Just create a small test application that formats a number...test it on all cases..if it fails on Runtime then use another computer or VM to recheck it.
Post back the results.

Hi Experts,

Thanks for all the great things to try.  It will take me some time!

John:
I'm going keep your suggestion to 'Create A Small Test Application' for possible future analysis although I can probably use the same one as for Gustav's suggestion.

Scott:
I'm a little leary about doing this however it is worth a try.

Since I don't really have any knowledge with regard to References and what they are doing it is hard for me to answer your questions, however I will try:
- VBA Extensibility.  I don't have any idea what this does and therefore the potential impact of removing it.
- ADO Recordset Library.  Mostly I am opening / closing Databases / Recordsets.  However I do have code that does relinking.
- Early Binding.  I use Late Binding to import Excel Files as I have worked with EE Experts and determine that this is my best option.
 
Gustav
Thanks for the suggestions / code.  I want to try it but I'm not sure about how to actually do it.  What are .bas files?  I'm familiar with DOS. bat files.

If I understand correctly all I need is a Test Database containing the References.  That I understand.

What I don't understnad is:
- Within it I would have two 'Menu Items' to 'run' the two .bas files.  Is this correct?
- If so what would the code look like to run them?  I haven't done this before.

Thanks All,
Bob C.

- VBA Extensibility.  I don't have any idea what this does and therefore the potential impact of removing it.
- ADO Recordset Library.  Mostly I am opening / closing Databases / Recordsets.  However I do have code that does relinking.
- Early Binding.  I use Late Binding to import Excel Files as I have worked with EE Experts and determine that this is my best option.

Select allOpen in new window

Removing the references as a test has no impact. If there are problems, you simply reinstate the reference. As mentioned earlier, all you do is compile your code (to ensure it works BEFORE removing the reference), and the remove the reference and recompile. After the recompile, if you have no issues then you can be sure you don't need that reference, and you can remove it. All in all, you are waaaay ahead of the game if you remove any references you don't actually need.

Of course, you should make a backup of your database first.

The basics for Access references are these:

Visual Basic for Applications
Microsoft Access 16.0 Object Library
OLE Automation
Microsoft Office 16.0 Access database engine Objects

When you create a new Access database, Access adds those references (the numbers may be different depending on the version of Office you're running). The others are ones you've added for one reason or another.

If you're using Late Binding with Excel, then you most likely don't need the Excel reference. One of the major concepts of Late Binding is that your code is NOT version-dependent. It's somewhat pointless to use Late Binding but keep the reference in place.

As mentioned earlier, it is very rare that the VBA Extensibility library is needed in a production deployment. That library is typically only used with developer addins and such, things that actually interact with code modules and such.

Hi Scott,

Thanks for the additional info.

Bob C.

What are .bas files?  

Code modules. If you don't want to copy-paste my posted code, go to the VBE window and right-click Modules and select Import files.

Hi Gustav,

Thanks for the update.

A couple of questions:
- Once they are 'imported', am I correct that then all I do is create a Button somehwere to run each of them?
- Is there any disadvantage to keeping this functionality in the application (hidden from Users) ?
- Do I need anything else in the Test Database other than the References and code to run the two Modules?
- Can I simply do this in a copy of my latest .accdb?
- Can I run these Modules on each PC that I use to develop / test?

Thanks,
Bob C.

- As noted, the simplest is to call it from an AutoExec macro.
- You can keep it - to warn the user if anything is not as expected.
- No.
- Yes, but I would create a test application for a start.
- Yes.

And, as mentioned by Scott, in your application, remove any you don't know what does, compile the code, and - when success - run a full test of the application and correct any error that may be caused by a missing reference by adding the relevant reference back.

Hi Gustav,
1. What is an AutoExec Macro?  I don't use Macros at all in the application.
2. Am I correct that you are suggesting that it always run whenever the application is started?  If so are any overhead issues?
Thanks,
Bob C.

Hi Gustav,

I have inserted the code but I don't know how to do the 'Call'.
Here is the current code for the button.
Private Sub F00020CheckApplicationReferences_Click()
On Error GoTo STEP_999
'MsgBox "Step 000-Check Application References - M-00-002."
'Call VerifyReferences
STEP_900:
'Step 900-Shutdown.
STEP_990:
'Step 990-Exit Event.
Exit Sub
STEP_999:
'Step 999-Form Error Handler Code.
Dim DebugErrMsgOpt999 As String
'DebugErrMsgOpt999 = "Yes" 'Un-Comment for use.
If DebugErrMsgOpt999 = "Yes" Then
   MsgBox Err.DESCRIPTION & " (" & Err.Number & ")"
   Resume STEP_990   'Step 990-Exit Event.
Else
   Resume STEP_990   'Step 990-Exit Event.
End If
End Sub

Thanks,
Bob C.

I've attached a demo with some invalid references and the AutoExec macro for you to play with.

The overhead to run this at each launch is absolutely minimal.
CheckReferences.accdb

Hi Gustav,

Thanks for the update / code.

Late yesterday I proceeded with removing / re-adding the References.  At this point I have 5 setup and testing of the original problems has indicated that they have all been solved.  I need to continue with testing the whole application to ensure there arn't any more required.

I'll provide an update when the testing is complete.

Thanks,
Bob C.

Hi Experts,

Based on the majority of your suggestions I concentrated on the VB References.

I have been able to obtain a solution via the following basic steps.
On my Development PC (64 Bit) with Access 2016 Plus
01. Opened the FE Database.
02. Removed the BE Linkage to my 5 BE Datases by using the Convert To Local Table function.
03. Opened the Visual Basic > Tools > References and deleted all of the References not in use.
04. Closed the FE Database.
05. Opened the FE Database and added a couple of Referecces back in resulting in the 4 listed below.
- Visual Basic For ApplicationsC:\Program Files (x86)\CommonX86\Microsoft Shared\VBA\VBA7.1\VBE7.DLL  
- Microsoft Access 16.0 Object Library   C:\Program Files (x86)\Microsoft Office\root\Office 16\MSAC.OLB
- Microsoft Office 2016.0 Access database engine Object Library  C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE16\ACEDAO.DLL
- Microsoft Office 16.0 Object Library  C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE16\MSO.DLL
06. Tested the FE to ensure that all of the issues were corrected.
07. Split the FE Database Into one BE Database.
08. Create my 5 BE Databases from the single BE Database and linked the FE Database to them.
09. Tested the FE Database.
10. Created an Install EXE using Advanced Installer.

On my Test PC (32 Bit) with Access 2016 Runtime.
01. Ensured that any previous installation had been removed.
02. Ensured that Access 2016 Runtime was installed.
03. Completed the installation from the Install EXE.
04. Tested the installation without incident.

Thanks again to all of you for your asistance with solving this issue.
Bob C.