Access reference library goes missing after opened from newer version

Question

I have built an Access database that is shared on our company network. Most users on the network have Access 2010 installed but some have Access 2016. When the database has been opened by someone with Access 2016 and then subsequently opened by someone with Access 2010 they are getting an error message as the Microsoft Excel 16.0 Object Library is marked "Missing".

I can fix this temporarily by selecting the '14.0 library' but the error recurs as soon as it is opened in the later version again. Is there anything I can do to stop this from happening?

Thanks

Answer 1

You can use late binding, which helps you avoid the issue. Code with late binding works with any appropriate object library, whichever is available.

You need to change the object initializations:

Dim excelApp As Excel.Application
Set excelApp As New Excel.Application

To

Dim excelApp As Object
Set excelApp = CreateObject("Excel.Application")

Note that if you use late binding, intellisense will be unavailable. My usual practice is to develop using early binding, and change it to late binding as soon as it's finished.