July 18, 2006 at 6:56 am
Has anyone run through the upgrade process from SQL-Server 2000/v8.0 to SQL-Server 2005/v9.0 with a predominantly MS-Access front end?
I was just hoping to find out what issues you had to deal with so I can look for those specifically before launching into full system testing.
There was a test before I came where the data was restored to a v9 server and the apps tested against it. It went well and everyone was happy till I pointed out the database was running in compatibility mode 80...
We run a range of MS-Access modules as the front end to our system, ranging from pure MDB/DAO upto fully unbound and SP'd ADP/ADO modules. I've been working on new modules using stored procedures to do the dirty work from within unbound forms in ADPs. Our web servers use v9 and we want to upgrade so we can use the xml data from the webforms more easily.
July 19, 2006 at 7:13 am
I've been working on an Access Project with Access 2003 and SQL Server 2000. I tried creating a project using SQL Server 2005 and it gave me a message saying it could not create Projects in SQL Server greater than the 2000 version. It should work with linked tables though.
July 20, 2006 at 2:40 am
That should be ok, I'll be creating the new database using SQL server management tools, not ms-access, I already know I can get basic connectivity from MSaccess to a 2005 database.
July 24, 2006 at 12:45 pm
I have been testing an Access 2003 data project against an SQL 2005 database for a while now without issue. The database was converted from 2000 to 2005 - which, as you noted coverts to compatibility mode 80. First run through on testing was in mode 80. The database was then set to native mode 90 and re-tested with the same results. The application is unbound, SP'd ADP/pure ADO, with a fair amount of embedded VBA code in the forms doing database access and manipulation (all of it ADO access). The only thing I have changed in the 2005 version of the db are a handful of functions. Not because they wouldn't run, they did, but because the Ready To Launch utility considered them suspect. Just changed them to SP's. The whole thing was pretty painless actually.
July 25, 2006 at 2:33 am
Cool thanks for the feedback, looks like the upgrade should be fairly painless...
What is/where is the ready to launch utility?
"unbound, SP'd ADP/pure ADO" ahh such bliss! unfortunately this little beastie was bought back from the colonies (literally!) a few years back so still has plenty of DAO, hacks, poor error handling and other worse sins!
I'm still in the prep stages as there is no rush to upgrade, so any feedback is good.
July 25, 2006 at 8:27 am
Sorry for the confusion. SQL Server Upgrade Advisor is the utility that flagged the functions in the converted database. I'm sure you have already run SQL Server Upgrade Advisor during your conversion process. MS gave us all a copy of SQL Server 2005 Standard Edition plus a copy of Visual Studio 2005 in a Ready to Launch presentation last year.
I'm not sure that your conversion is going to be pretty painless. It sounds like you have a ton of old code, written by people that may or may not be still present. Good thing you aren't in an urgent rush to upgrade. Might be adding a few gray hairs with some of the the apps you mentioned. Good luck!
I've still not completed testing my app as yet. I have to test it as a .ade with a full version of Access 2003 running as well as with the free run time version of Access. Also need to test the app running in a Remote Desktop Connection environment. I'll send you an email if I run into anything.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply