Report runs fine with Access but not the runtime

[Mike Krailo]

The report I have runs perfectly fine on any system that has MS Access installed on it, but comes up with a Enter Parameter Value pointing to the Date identifier when running on a machine that only has the runtime installed. I trouble shot this down to a text box in the footer that contains the following.

="Generated: " & Date()

So it is using the VBA.Date function and maybe this has to do with early or late binding but what is the best way to achieve the desired result without getting the parameter error. I thought about making a simple function to do the same thing but I'm interested in avoiding this problem in the future so I need to understand what is going on in general here.

 

[arnelgp]

just a wild thought.
you can add Calculated column to a query and use the query as your recordsource:

select field1, ("Generated " & Date()) As GenDate From yourTable.

then bind the textbox in the footer the GenDate field.


you should also check for Missing Reference on your db.

 

[Mike Krailo]

I'll try that Arnel, but If this is due to no references or early binding, what is the way to use late binding so the references don't matter? I have to wait till I get to work later before I can test it out.

 

[arnelgp]

it is not about late/early binding.
when you go to VBE and on Tools->Reference, you see "Missing Reference", that is the problem
and need to be resolved first.

 

[CJ_London]

What happens if you run as .accdr on your machine with a full version of access?

and is the runtime on the other machine the same version

 

[Mike Krailo]

CJ, I'm not sure about the runtime version offhand as I installed this quite a while ago, but I remember being confused about which version to use. So I need to reinvestigate that. I just tried changing it to .accdr on my machine and everything works fine.

I seem to remember trying to install the version of runtime that I thought was the correct one, and the installer complained that it was the incorrect one. So I kept trying different versions until I found one that would actually install. It was very frustrating.

 

[CJ_London]

what version are you developing in?, ideally needs to be the same version for runtime. And is the app installed a .accdb or .accde? If not .accde, suggest try it.

You can't mix 32 and 64 bit office so if you are developing in 32bit and the other machine is using 64bit office,, you won't be able to successfully install 32bit runtime.

@isladogs has good experience of different versions of access, not sure if that extends to runtime, but I've flagged this up to him

 

[Mike Krailo]

Thanks CJ. I'll gather more info when I get to work. It is just an accdb file as it changes a lot. I could certainly try the accde file to see what happens.

 

[Pat Hartman]

The problem is most likely a missing reference. The first command that references a library throws an error even when that isn't the missing library so Date() is so ubiquitous, it is often the error we see.

Unless you can install a full version of Access on the user computer, it will be hard to track this down. You might try adding a procedure to list all the references so you can see the problem. Then hide the button or remove the code once the reference issue is fixed.

 

[Mike Krailo]

I fail to see how to manipulate phantom references on a machine using the runtime. If anyone knows code to list current references, please inform how to do so. I haven't got a clue. Here is the references on my development machine but not sure what this has to do with the target machine.

CJ, I develop on 64bit MS365 and the office version installed on the target system with the issue has 32bit Office 2013 Home and Business which does not include Access. I installed the Runtime 2016 for Access on that machine (16.0.14326.20238). Not sure how to tell if it is 32bit or 64bit.

 

[Pat Hartman]

Here's a procedure I use to capture the references and put them in a table. To make this work in your situation, make a form with a big textbox and change the code so that instead of printing to the debug window or to a table, just concatenate into the form's text field.

Sub ReferenceProperties(frm As Form)
    Dim ref As Reference
    Dim db As DAO.Database
    Dim TD1 As DAO.TableDef
    Dim QD1 As DAO.QueryDef
    Dim TS1 As DAO.Recordset
    Dim strDatabase As String
    Dim ThisDB As DAO.Database
    
    On Error GoTo Err_ReferenceProperties
    'strDatabase = "C:\hartman\LinkDB2.mdb"
    strDatabase = frm.txtDBName
    
    Set ThisDB = CurrentDb()
    If strDatabase = "" Then
        Set db = CurrentDb()
    Else
        Set db = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
    End If
    
    db.Containers.Refresh
    
    frm.txtAccessVersion = db.Properties("AccessVersion")
    frm.txtJetVersion = db.Version
    Set QD1 = ThisDB.QueryDefs!qDeltblReferences
        QD1.Execute
    Set TD1 = ThisDB.TableDefs!tblReferences
    Set TS1 = TD1.OpenRecordset

    ' Enumerate through References collection.
    For Each ref In References
        ' Check IsBroken property.
        TS1.AddNew
        TS1!RefName = ref.Name
        TS1!RefFullPath = ref.FullPath
        TS1!RefVersion = ref.Major & "." & ref.Minor
        TS1!RefGUID = ref.Guid
        TS1!RefKind = ref.Kind
        
        If ref.IsBroken = False Then
            TS1!RefBroken = 0
            'Debug.Print "Name: ", ref.Name
            'Debug.Print "FullPath: ", ref.FullPath
            'Debug.Print "Version: ", ref.Major & "." & ref.Minor
        Else
            Debug.Print "GUIDs of broken references:"
            Debug.Print ref.Guid
            TS1!RefBroken = -1
        End If
        TS1.Update
Next ref
Exit_ReferenceProperties:
    db.Close
    Exit Sub

Err_ReferenceProperties:
    Select Case Err.Number
        Case 3043
            MsgBox "Please select a valid database", vbOKOnly
        Case 91   ' db was not opened so it cannot be closed.
            Exit Sub
        Case Else
            MsgBox Err.Number & "-" & Err.Description
    End Select
    Resume Exit_ReferenceProperties
    Resume
End Sub

 

[CJ_London]

I develop on 64bit MS365 and the office version installed on the target system with the issue has 32bit Office 2013 Home and Business which does not include Access. I installed the Runtime 2016 for Access on that machine (16.0.14326.20238). Not sure how to tell if it is 32bit or 64bit.

in that case .accde is not going to work anyway. If you have 32bit office, then access runtime must also be 32bit. You can't mix them.

The other problem you have is that something developed in a later version won't run in an earlier version. You should be developing in a version which is as early as the earliest your users are using - i.e. would appear to be 2013.

Reason is access (as with all apps) is not backwards compatible. So when the app is opened, it will see a reference for example 'microsoft office 16.0' - but it doesn't have that version. However it is forwards compatible so if it sees 'microsoft office 15.0' it knows it can apply the 16 version it does have. Office 2013 is version 15 so will not be able to work with a version 16 app. As at today, Office 2016 and above are all version 16. See this link

Microsoft Access Version Releases, Service Packs, Hotfixes and Updates History

Microsoft Access has many versions, Service Packs, Hotfixes and other Updates with Office

www.fmsinc.com

So seems to me your options are to upgrade your users machine to at least office 2016 or for you to develop in office 2010 or 2013 - and preferably be consistent with 32 or 64 bit. You could try uninstalling the current runtime and installing a later version - but no guarantees that will work. I've not tried it so cannot speak from experience.

Since your user is using runtime - I presume you are aware that among other things, the right click menu options won't work, if you need them, you will need to write your own. That's the reason for the .accdr - it enables you the developer to test that the app will work as intended in a runtime environment.

 

[Mike Krailo]

CJ, although the one user has Office 2013, that does not include Access. Shouldn't the Runtime version of access work as long as it is for 64bit and is 2016? Or that simply not possible? Like I said, I develop using 64bit MS365 (essentially 2016 Access). I can get some things to work using the existing runtime but there is a lot of stuff that doesn't work. The essential things work OK but there are some things that crash the application. I'm slowly working through all the issues to fix as many as I can but it is a pain.

 

[CJ_London]

Shouldn't the Runtime version of access work as long as it is for 64bit and is 2016?

You said the user had 32bit 2013 office - I've neve tried it but understand you cannot mix bitness with office products. You might be able to install 32bit 2016 access - see this link

Install and use different versions of Office on the same PC - Microsoft Support

Provides tips on how to install multiple versions of Office on the same PC.

support.microsoft.com

and 32bit access should be able to run a .accdb developed in 64bit access providing all the usual things have been done around declaring API's and datatypes (and they are the same version i.e. 16)

 

[Pat Hartman]

Bitness is an issue when you are distributing an .accde. The Runtime MUST be 64bit if you compiled the FE with a 64 bit version of Access. If you are not using any thing else that is bitness specific like using references to Word, Excel, or Outlook, you can use an .accdb that you just rename to .accdr. It isn't the same as the .accde but it will allow you to run the app with the 32 bit version of the A2016 runtime. That way the Access references will match those on your computer and the bitness of the Runtime will match the user's computer.

This is just a work around. The best option is for you to develop in the lowest version that your users have and use the same bitness.

 

[Mike Krailo]

Well I don't have any other version to develop in. So that is a limitation. It would be nice if Microsoft allowed developers to have access to any version they needed to accommodate various situations like this but it doesn't look like that is going to happen. I'll have to work with what I have.

 

[strive4peace]

hi @Mike Krailo

perhaps uninstall the old Runtime version, reboot, and try this one?

Download and install Microsoft 365 Access Runtime​

https://support.microsoft.com/en-us...-runtime-185c5a32-8ba9-491e-ac76-91cbe3ea09c9

 

[Pat Hartman]

Well I don't have any other version to develop in.

That's why I gave you a solution that let you use your current version and bitness and still run on the other machine.