Comprehensive reference list

[Alc]

My department has just been told that we're upgrading to Office 2016 from 2010, next week. I know from a test run they did with one user that I'm going to need to change at least some of the references that our (many) Access databases use.

Each of the databases uses a different combination of references, depending on its purpose, so I figure the fastest way to gauge which are affected is to look at the current list and see if any items appear on a list of those that are different between the two versions.

I've been googling this for a while but it dawned on me that one of our illustrious posters might have had a similar problem, so it could be faster to ask here.

Does anyone know of a comprehensive online list of which Access references are used with which version of Office?

 

[Ranman256]

My condolences. Good luck.

 

[Alc]

My condolences. Good luck.

Thanks, it's going to be an interesting week

 

[MarkK]

I know from a test run they did with one user that I'm going to need to change at least some of the references...

So you don't know what they tested or what failed? I would not take any action on info that vague. You should wait until you have hard facts.

 

[Lightwave]

I don't know maybe this would be useful - might be out of date.

Everything I am googling seems to be somewhat old

http://access.mvps.org/access/modules/mdl0022.htm


Some more reading on reference issues

 

[Alc]

So you don't know what they tested or what failed? I would not take any action on info that vague. You should wait until you have hard facts.

In the case I tested, I know it was the version of Excel that caused the problem. Not all of the databases contain an Excel reference, but some contain one for Word, Powerpoint, etc.

Unfortunately, the upgrade is going to happen a week from today, whether I like it or not. This is a worldwide company and we're the last country to be upgraded. Nobody here has any say in the matter, as the decision was made in the Japanese head office.

Our department uses Access a lot, most don't use it at all. I think the decision was made that since so few people will be affected (taken as a global percentage) the risks are minimal. That may be true, but it's not going to be much consolation for those of us who ARE affected.

 

[Alc]

I don't know maybe this would be useful - might be out of date.

Everything I am googling seems to be somewhat old

http://access.mvps.org/access/modules/mdl0022.htm


Some more reading on reference issues

Thanks, I'll work through those two.

 

[MarkK]

In my experience Office products automatically move references to newer versions very successfully, for instance, if I have an Access 12.0 Db that references Excel 12.0, and I move that project to a machine that has Access 14.0 and Excel 14.0 installed, everything is fine. However, if I write a project in Access 14.0 with a reference to Excel 14.0 and then move that project to a machine with Access 12.0 and Excel 12.0 installed, then the Excel reference breaks.

In your case, if all your office products are upgraded to newer versions on all your machines, there is a reasonable chance that it will be a trouble free upgrade. It would be like moving your project to a machine with a newer version of Office, which typically, in my experience, succeeds.

I guess only time will tell.

 

[Alc]

In my experience Office products automatically move references to newer versions very successfully, for instance, if I have an Access 12.0 Db that references Excel 12.0, and I move that project to a machine that has Access 14.0 and Excel 14.0 installed, everything is fine. However, if I write a project in Access 14.0 with a reference to Excel 14.0 and then move that project to a machine with Access 12.0 and Excel 12.0 installed, then the Excel reference breaks.

In your case, if all your office products are upgraded to newer versions on all your machines, there is a reasonable chance that it will be a trouble free upgrade. It would be like moving your project to a machine with a newer version of Office, which typically, in my experience, succeeds.

I guess only time will tell.

Looks like you're right.

At the risk of counting my chickens before they hatch, I just did a few tests on someone else's laptop which has:

• Excel 2016
• PowerPoint 2016
• Word 2016
• Access 2010

and opened a few of the exiting databases ‘Microsoft Excel 14.0 Object Library’ was automatically changed to ‘Microsoft Excel 16.0 Object Library’ and there wasn't even a noticeable overhead on the time taken for it to open. On the downside, I got a horrified call from one user who was now no longer able to get into the database. I manually reverted the reference and all is well.

I think the plan will be:
No development work to be done on the databases after tomorrow
Monday - Wednesday of next week,

1. Make copies of all relevant front ends and test that each copy opens

On Thursday, once everyone is done for the day

1. Make a copy of the front ends and rename them (to be safe)
2. Replace the front ends with the new version, or just reopen them and have them update themselves (whichever is faster)
3. Let IT run the upgrade once I leave

On Friday

1. Check that each front end still opens
2. Wait for the inevitable unforeseen problem and deal with them as they come.

 

[The_Doc_Man]

Sadly, because VBA code that exercises those references uses just-in-time compilation, you will run into broken references because you CAN'T write code to check references. The whole project has to compile BEFORE you can get to the point where you could check (and fix) the references. By the time your VBA code would get to run, the references are already broken.

There is such a thing as late binding of various external functions, but so many functions are intrinsic to VBA that it becomes difficult if not impossible to find them all.

 

[Alc]

Just thought I'd write a quick note to let people know how the Office upgrade went, just in case anyone else is going to be experiencing it soon.

This was, as a reminder, upgrading Word, Excel and Powerpoint to Office 2016 but leaving Access at Office 2010. We don't currently use Outlook, so that was irrelevant.

Key points were:

1. As I was told, the Access database DID automatically update the references to the other applications. For example, the first time I opened one database, Excel 14.0 was changed to Excel 16.0 without my needing to do anything manually.

2. One database which contains a module called by a batch file to run overnight did produce an error as follows:

Compile error in hidden module: {name of module}
This error commonly occurs when code is incompatible with the version, platform or architecture of this application.

This problem was resolved by adding the Office 16 object library to the list of references.

3. I've noticed a drop in the speed for running some processes. For example, one query was timed as follows:
Access 2010 with Office 2010 - 53 seconds
Access 2010 with Office 2016 - 1 minute 40 seconds
Access 2016 with Office 2016 - 52 seconds
(this last time was tested on a separate laptop)

I'll add any other findings as I come across them, but the update was done on Thursday night and pretty much all I've done since is try to find problems. Next step will be to test each on the laptop where Access is at 2016 as well, before the whole department upgrades.

 

[Lightwave]

Thank you for the update Alc

Although I didn't think about this when first designing databases using Access - the ease with which you upgrade to later versions is second to none in MS Access

I've just spent several months doing an upgrade of a web application. Just to get to the same basic functionality as before.

Upgrading some of my applications from 2003 to 2016 could potentially take less than half an hour.

 

[gemma-the-husky]

not sure.

you may find references to office 2016 from Access 2010 no longer work.

I tend to develop in A2003, but use office 2013/2016 as well

A2003 errors with outlook 2013 and with excel 2013. Not tried versions A2007/A2010 although I have them, as I don't need to use in between versions, but possible that will give problems as well.