Problem with accdb association on user's desktops

[burma]

I'm running into the problem of opening accdb's in the wrong version of Access on user's machines. This seems to happen most with machines that have both 2010 and 2016/365 installed. Anyone have a decent workaround to that, like a script or batch file? Even a hardcoded shortcut such as

"C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" "C:\Users\someone\Desktop\New Microsoft Access Database.accdb"

doesn't seem to do it in some cases: even though the user has set "always open in 2016" in "Open With", the file still opens in 2010.

In addition to a workaround, anyone know how to fix this permanently, like in the registry or gpo? Thanks

 

[theDBguy]

Try right clicking on the file and use Open With.

 

[burma]

Try right clicking on the file and use Open With.

I have and it doesn't always work. On machines that experience this, the only sure way I've found is to open the correct version of Access first, then open the file from there.

 

[theDBguy]

I have and it doesn't always work. On machines that experience this, the only sure way I've found is to open the correct version of Access first, then open the file from there.

Well, in that case, another potential approach is to use a shortcut to open the file with the correct Access version.

 

[burma]

Well, in that case, another potential approach is to use a shortcut to open the file with the correct Access version.

Been there done that - see above. It doesn't always work.

 

[theDBguy]

Been there done that - see above. It doesn't always work.

That depends on how you constructed the shortcut. Did you add the file as an argument to the Access.exe command?

Edit: Oops, I just reread your original post. Sorry, I'm on my phone and didn't see it right away. On my machine though, right clicking seems to be enough. Good luck!

 

[Pat Hartman]

Access versions fight over control of the registry. So, the last version of Access you open takes control of the extensions it recognizes. There are two ways to solve the problem.
1. Always open the version of Access you want and then open the database - awkward and not functional for users.
2. Always use a shortcut that specifies the version of Access to open followed by the name of the Access database - much more user friendly for users.

As the programmer, I work with too many applications to keep shortcuts for each of them so I use option 1 for myself and only keep shortcuts for the apps I use rather than develop. So, the timekeeping app has a shortcut because mostly I will be using it rather than developing it.

 

[Isaac]

Years ago I concluded that it is not safe to have multiple versions of Access installed on users machine, not if you value your sanity and theirs.
Just don't do it.

People will tell you this solution or that solution, but full circle....it's going to cause problems, deliberately having two systems conflicting with each other. File associations will be the least of your struggles. References will come into play, especially with trying to bind references to a specific library.

I have simply told the users one of the requirements is, your machine has one version installed and one only.
Just my opinion.

 

[burma]

Years ago I concluded that it is not safe to have multiple versions of Access installed on users machine, not if you value your sanity and theirs.
Just don't do it.

People will tell you this solution or that solution, but full circle....it's going to cause problems, deliberately having two systems conflicting with each other. File associations will be the least of your struggles. References will come into play, especially with trying to bind references to a specific library.

I have simply told the users one of the requirements is, your machine has one version installed and one only.
Just my opinion.

I agree. Now if I can get the client to agree ..

In the mean time, a solution other than opening Access first and then pointing to the file, would be welcome.

 

[Isaac]

Well, almost all my FE deployment schemes begin with a VBScript that controls what opens, what copies to their FE, etc. etc.

Incorporated into that would be opening a SPECIFIC application and then using OpenCurrentDatabase to open the accdb file.
I'm not thinking Set AccessApp = Access.Application, I'm thinking more along the lines of Shelling out an exe path

 

[burma]

Well, almost all my FE deployment schemes begin with a VBScript that controls what opens, what copies to their FE, etc. etc.

Incorporated into that would be opening a SPECIFIC application and then using OpenCurrentDatabase to open the accdb file.
I'm not thinking Set AccessApp = Access.Application, I'm thinking more along the lines of Shelling out an exe path

Sounds like the best approach for now. If you have a sample script, please forward. Thanks

 

[gemma-the-husky]

I thought databases try to open with the last version of access you used. Try opening A2016, and then closing it, or open A2016 and then navigate to the database within A2016.

Is there any reason you need A2010?

 

[Isaac]

Sounds like the best approach for now. If you have a sample script, please forward. Thanks

I am thinking something like this, this is untested air code but demonstrates the idea.
Hopefully if someone spots an error here they can correct me. This is for VBSCRIPT, not vba:

dim appAccess
Dim objShell

Set objShell = WScript.CreateObject( "WScript.Shell" )
objShell.Run("""C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE""")

set appAccess = getobject(,"Access.Application")
appAccess.opencurrentdatabase "path to accdb file"

 

[burma]

I am thinking something like this, this is untested air code but demonstrates the idea.
Hopefully if someone spots an error here they can correct me. This is for VBSCRIPT, not vba:

dim appAccess
Dim objShell

Set objShell = WScript.CreateObject( "WScript.Shell" )
objShell.Run("""C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE""")

set appAccess = getobject(,"Access.Application")
appAccess.opencurrentdatabase "path to accdb file"

I'll need to account for if the user has 2010 and 2016 open at the same time. Thanks, that gives me a starting point.

 

[Isaac]

You mean they need to not only have multiple versions installed, but multiple db's open under different installation versions at the same time as you then (3rd step) open your database under a certain version too??
Yikes - that's crazy.

Yes, if so, that definitely calls into question my GetObject. (are you allowed to close their other ones first? if so, easier)

You can evaluate a bunch of window captions and get their window handle, but that's getting way over into knock-on-wood type of VBA..

 

[isladogs]

Using version specific shortcuts will only work without reconfiguring Office in the registry if the oldest version of Access is currently set as the default. Similarly using Open With.

See if there is any additional info of use to you in my article on dual installations

Installing Multiple Office Versions

This article explains how you can successfully install more than one version of Office on the same workstation including Access runtime and mixed bitness installations

www.isladogs.co.uk

 

[burma]

You mean they need to not only have multiple versions installed, but multiple db's open under different installation versions at the same time as you then (3rd step) open your database under a certain version too??
Yikes - that's crazy.

Yes, if so, that definitely calls into question my GetObject. (are you allowed to close their other ones first? if so, easier)

You can evaluate a bunch of window captions and get their window handle, but that's getting way over into knock-on-wood type of VBA..

I know, it's a tough crowd. Some of them may be still running 2003. I uninstalled 2010 on my dev box just because of the headaches, but now I'm reinstalling it to test different scenarios.

 

[burma]

See if there is any additional info of use to you in my article on dual installations

Installing Multiple Office Versions

This article explains how you can successfully install more than one version of Office on the same workstation including Access runtime and mixed bitness installations

www.isladogs.co.uk

Thanks, I'll check it out.

 

[isladogs]

Thanks, I'll check it out.

Note the additional comments I just added to the start of my reply

 

[burma]

Using version specific shortcuts will only work without reconfiguring Office in the registry if the oldest version of Access is currently set as the default. Similarly using Open With.

That's just what I've found. You would think that Open With and version specific shortcuts would work, but in some cases, no