Friday, December 28, 2007

Access 2007 problems

I have an Access database that connects to SQL Server to provide custom reporting and batch updates of data. The file is 2000 format and was created using Access 2003 SP2.

I'm having quite a few issues using this file in Access 2007. My first attempts at 2007 were calamitous due to code artifacts that would not compile (oops). I found and fixed things that 2003 ignores, and once it compiled I thought things would be stable.

The first issue was when opening the compiled mdb, 2007 reported "an error loading module...continue loading project?" but the Form being complained about had compiled & worked perfectly fine using 2003. I then tried to open the form in design view but could not access the code-behind ("invalid whatever" error message). To fix this, I would simply open the mdb in 2003, copy the code-behind to the clipboard, set the "has module" = No, save the form, close, re-open in design view, and then paste the code back into the form and save it. OK, now the file works with Access 2007.

The second issue, when I added a new report using 2003. I took an existing report, modified it and did a save-as with a new name. When I subsequently opened the revised mdb with Access 2007, it complained about an "error loading..." and this time it named the original (existing) report. I fixed the report using the same method - by deleting and then reinserting the code module.

At this point, I have no explanation for this behavior. There aren't any users with Access older than 2003, so I can try converting the mdb to 2002 format to see if that stabilizes. Another option might be to convert the file to the new 2007 format as an accdb but that would require maintaing two versions of the same file, not a good idea. A third idea would be to create a new blank 2000 or 2002 database and then import all the objects into that; used to work in the past when an Access 97 file went bad.

No comments: