Access 2010 & Runtime problems with Windows XP

We use Microsoft Access to produce various front end programs for use with SQL Server databases.  We then distribute these frontend programs using the Access Packaging Wizard and Access Runtime system.
We run two servers, one local server running SBS 2011 and SQL Server 2008R2 and one dedicated remote server (cloud server) running Windows Server 2003 and SQL Server 2005.
Most of our client computers operate on Windows 7 Professional (both 32 bit and 64 bit processors) but we have some remote workers working on Windows 7 home edition, one working on Windows Vista and one working on Windows XP.
We have recently upgraded to Access 2010 for programming the frontend system to be compatible with SQL Server 2008R2.  Previously we were using Access 2007.
We are having compatibility problems with the Windows 7 home, Windows Vista and Windows XP remote computers when we try to run Access 2010 runtime system and a frontend program created and packaged in Access 2010 that tries to connect to the Cloud server running SQL Server 2005.
Debugging faults that only show up when running via a runtime version of Access is quite problematic but I have tried to pin down the problems as best I can.
It seems that the problems are likely to do with different version of Microsoft ActiveX Data Objects in that I have pinned down one of the problems in the Access Basic code to the first “set variable = currentproject.connection” command.  I have tried “set variable = currentproject.AccessConnection” command but the failure is the same.  (I pinned down this command by using a series of Msgbox prompts to find the offending command).
If we install a full version of Access 2010 on a spare Windows XP machine and open the .adp version of the frontend then the program works correctly.  If we open the .accdr version from the packaged frontend using Access 2010 runtime the frontend fails with an illegal command.  If we open the .accdr version with Access 2010 runtime version on a Windows 7 Professional machine then the frontend works correctly.
The problems seem to be variable depending on the operating system in use,   from illegal commands to the frontend seeming to work correctly except some queries do not return any data.
Assuming that my idea is correct, is there a version of Microsoft ActiveX Data Objects that is compatible with both Access 2007 and 2010 (including the runtime systems) and Windows XP, Windows Vista, Windows 7 Professional and Windows 7 Home?
We currently have the offending systems functioning correctly by packaging the .adp version of the front end using Access 2007 and running Access 2010 runtime on the computer.  Both Access 2007 runtime and Access 2010 runtime are installed.  I believe we now have quite a muddle of Access installation packages which probably means there is some cross contamination of versions of support systems.  I mention this as the Access 2007 packaged frontend does not work with Access 2007 runtime although it use to before Access 2010 was introduced to the picture.
AlistairSteedAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BardobraveCommented:
If I've been able to follow it up correctly... do your offending machines work properly if you use 2010 application with Access 2010 runtime?

It seems to be a merely runtime version problem?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
First: Access 2010 is not compatible with some of your operting systems, it would seem. This is from the Requirements for 2010 (http://technet.microsoft.com/en-us/library/ee624351.aspx#section6), under the "Operating System" requirements section:

"Windows XP with Service Pack (SP) 3 (32-bit), Windows Vista with SP1, Windows Server 2003 SP2 and MSXML 6.0 (32 bit Office only), Windows Server 2008 or later (32-bit or 64-bit), Windows 7 operating system."

I would strongly suspect that your troubles are not in ADO, but instead will lie in incorrect or missing references on some of those machines. While you might be failing on the CurrentProject.Connection, I'd be willing to bet that the issue is NOT that line of code, but rather a reference that is causing troubles. Missing or incorrect references often are masked by other issues, so check those first.

Can you listout the references that you're using in the database?

I would also strongly encourage you to finalize your deployment environment, and take steps to insure that your clients adhere to those specs. If you're going to work with Access 2010, then upgrade all of your clients to the 2010 Runtime - and make SURE that those clients are (a) running a compatible operating system and (b) fully updated on Windows and Office.

0
AlistairSteedAuthor Commented:
I will try to answer your points as best as I can.

Barbobrave - Unfortunately 2010 applications do not function using 2010 runtime on the older operating systems.

LMSConsulting
Your point is well made about switching all users to a more up to date system and maybe this will be the easiest solution.
To answer your other points.  I do not know how to check broken references when there is only a runtime system on a machine so I cannot be sure if there are any.  All machines have Office 2010 (without Access) on them.  The references that are used are:
Visual Basic Applications
Microsoft Access 14.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft Calander Control 2007
Microsoft Excel 14.0 Object Library
Microsoft Outlook 14.0 Object Library
Microsoft ActiveX Data Object 2.8 Library

I believe all the machines comply with the Service Packs you mention but I have not been able to check them all at present.

My guess is the probelm relates to Microsoft ActiveX Data Object 2.8 Library.  I do not know if there a valid version suitable for all the operating systems I'm having problems with.  I find it easy to accept that Windows XP will not be compatible but I would have thought Vista and Windows 7 Home should not be giving problems.

Thanks for your input.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you certain that the machines all have Excel and Outlook installed, and that they're all using 2010 versions of those programs?

Unless you're using some highly advanced features of ADO, you can set the reference to a much lower version (like 2.1, for example). Access can easily "upsize" references, but it cannot downgrade them - so the Runtime will take your ADO 2.1 reference and move it up to 2.5, or 2.8, or whatever is on the machine. But if you have ADO 2.8 referenced, and the target machine has ADO 2.5, then the reference will be broken, and Access will not be able to fix it.

I'm not familiar with Microsoft Calendar Control 2007, but if it's not the builtin datepicker for 2007/2010 then you will have to insure that the endusers have that control. I'd concentrate my efforts on the ADO and other references first, however.

Also, on review, it looks like you are probably okay with the Operating Systems. The requirements make no distinction in regard to Windows 7 Home Edition (I thought at first there was a distinction), so you're probably okay with that.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AlistairSteedAuthor Commented:
I think you are right about the version of ADO.  I suspect I will need to try each earlier vesion until everything works or there is a compatability issue.  I'm not aware of using any advanced feature so it should be ok.  

It might be easier to upgrade the older kit and move on.

Thanks for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.