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.