Access 2007 vs. Access 2010

[WriterCat]

My computers were recently upgraded from Windows XP and Office 2007 to Windows 7 and Office 2010. I am responsible for three Access databases that are used by some of our affiliates. I recently made changes to those files in Access 2010, and now our affiliates are receiving error messages when they use the files. The files will not compact - they receive an error message that they do not have the correct version of Access to do that. I had Access 2007 installed on one of my computers and receive the same error when I try to compact the databases. The changes I made included modifying queries, relinking a table from a SQL Server database, and changing a couple of macros.

They also receive a warning that the files are incompatible.

I made new files using Office 2007, imported the objects from the files I'd worked on in 2010, and asked one of our affiliates to test the file. Same issues except he did not receive the compatibility warning.

Have any of you experienced issues with these two versions of the file? One person asked if the problem was the operating system, not Access. Is that a possibility?

Any guidance will be appreciated.

Nancy

[WendellB]

Hi Nancy,

You've stumbled on one of the issues with doing development in 2010 and then running the databases in 2007. Are you using the .mdb format, or the .accdb format? That can have a significant bearing on the problems you are having.

Take a look at this Access 2010 to Access 2007 Unrecognized Database Format thread in the Microsoft Access for Developers forum. It lays out things that will cause that issue. Also note in particular the reference that Albert Kallal mentioned in his post - it's the MSDN article Backward Compatibility Between Access 2010 and Access 2007. The bottom line is you have to tread very carefully, and not use any of the 2010 new features.

[WriterCat]

Thanks! I will check out those resources.

I didn't consciously use anything new, but who knows... The files are in accdb format.

Nancy

[AccessDeveloper]

Hello Nancy,

I experienced the same problem when upgrading from Access 2007 to Access 2010. What I learned is that none of my Users with Access 2007 (.accdb or .accde) could open any applications that had any development done by me using Access 2010 (even if saved as Access 2007 version). Microsoft really made this difficult. A Developer must use the same version (or a previous version) as the Users ... Developers cannot use a more recent version since Access is not backward compatible.

We had to upgrade every User to Access 2010 and it solved the problem. Very costly!

Paula

[johnhutchison]

I use Access 2010 all the time now, but the users of my databases have a mixture of 2010, 2007, 2003 and occasionally earlier versions.
But I only ever use the old mdb format, so I don't have any problems (other than needing to modify the References)

[WendellB]

Same here - I have yet to find a compelling need for any of the special data types that you get with the .accdb format. And I don't use any of the new control types that might require the format either.

[iansavell]

I would suggest the default situation for many businesses is that there will be a mixed population of MSO installs. 2010, 2007, 2003, even some XP. Over the years I've become used to the issue of having to update the references on my projects to allow the older PCs to work. For years I kept an Office 2000 system just for "compiling" Access projects. Now I tend to talk a secretary through the process - it is usually her boss that has the oldest PC.

Having to export forms to textfiles and back is a whole order of magnitude more difficult. So although I've developed some .accdb databases for a company that did a mass upgrade to MSO2007 I'm going back to good old MDB.

Ian

[WendellB]

...I'm going back to good old MDB.

I'm with you and John on that - I don't see any compelling reason to switch to the .accdb format for the things we do, and while it's architecture may be a bit long in the tooth, I pretty well know it will work on most anyone's system.

[mikelane2]

Try to create a license free, installable runtime copy of the database for everybody else to use.

When I updated our company from Office 97 to 2007, I update most of the employees to Office 2007 Standard, No Access. I updated myself to Office 2007 Professional With Access. After converting the database and fixing all the code, I created a run time version of the database.

Access 2007 made it real easy to create an installable runtime version of the database that you can even burn onto a CD. All the required files and folders are created by Access, including the autostart.ini & setup.ini files. When you insert the CD into a user's computer, even if there are NO Office applications on it, the runtime version of Access will install everything required. There are no licenses or fees to worry about, and you can install the runtime version of the Access database on as many computers as you want.

When you open the runtime database, everything is identical to the full Access 2007 database program, exept there is no direct access to tables, queries, reports, etc. You need to make any tables, queries, reports, etc, available through code (like a switchboard form that loads automatically when the database is opened, for example). The switchboard can then have buttoms that open other forms that can run queries, enter data, open reports, etc, all by code or macros. The runtime version of Access doesn't allow the user to create queries or reports or anything else. It only allows you to open what has been created.

I have several users who don't have Access on their computers. Then installed the runtime version of the Access database and can do most of what I can (except creating new queries, etc.). All the tables, queries, reports are hidden, but they can be programmed to open on user designed forms. When ever I need to create new features in the database, I do it in Access 2007, then I rename the file extension to create the runtime. The only difference between the runtime and the full Access database file is the file extension (the last letter of the file extension is changed to 'r'). Then I replace the runtime on each of the computers. The runtime database is linked to another Access database file on the server.

I don't know if Access 2010 has this feature, somebody let me know. This would solve the upgrade problem by allowing the designer to upgrade to Access 2010, and create runtime versions of Access to install for free on all the rest of the users for a company specific Access database.

[johnhutchison]

I have not used it, but there is a free runtime for 2010 available from Microsoft here.

[iansavell]

Mikelane2 makes a very useful point - runtime Access and MDE files provides a way round most of these problems.

However, as he points out, with runtime you only get what you write, and it is surprising how much most Access developers rely on Access to provide some core functionality. That includes most of the default menus (including right-click) and toolbars. I'm not sure how far that goes but I suspect you lose filtering, searching, export to Office etc. You also lose default error handling - unless you trap absolutely everything your app just crashes on error and you'll have to try and reproduce the error on a full system to debug it. All that can mean a lot of extra coding and testing which is hard to justify on a small in-house development. For example, a few years back I produced a small app to search UK census data files in a lunchtime and it is still in regular use, creating a fully functioned runtime version would have taken days.

But another key thing is that in the past the runtime was provided with retail Access but not a license to distribute it. For that you had to separately purchase a developer license. I note from John's link that the 2010 runtime is free to download and distribute, which makes it a more viable option.