Convert Access 2000 .adp to 2007.accdb

[joejack0330]

We have most of our Access 200 databases in .adp format connecting to our sql 2005 databases but see that most signs point toward Microsoft not supporting .adps in the future and recommending going to .accdb and because of all the problems with the runtime 2007 with .adps and all the changes needed to get the 2000 versions to work in 2007, we were thinking that if are going to have to go through every database anyway then maybe it's time to bite the bullet and convert to .accdb at same time. BUT...it doesn't look easy because not only will we need to link all tables and views and deal with how they record source and so on are affected in the forms and reports but will we then need to create a passthru query for all stored procedures because most of our forms and reports are based on them? Anyone have any suggestions or know of any conversion tools? Thanks, Joe

 

[BillKuhn]

Boy I'd like to hear any suggestions for this too!

I've 'upgraded' a customer from an Access2003 MDP to an Access2007 ADP with SQL 2005 backend. Major fight to convert and work through bugs. Now performance is absolutely dismal and unacceptable. Apparently Access2003 ADP technology was OK, but of course we used current Microsoft technology which absolutely stinks!

I've seen some references on the internet that access using ODBC is significantly faster than ADP.

True? How do we switch?

I'm really not trying to be an access developer so my skill level with Access is low, and this is my first and last Access project, by the way.

Bill Kuhn - MCSE
bkuhn@kuhngroup.com
The Kuhn Group, Inc.

http://www.kuhngroup.com

 

[BillKuhn]

Update on the Access 2003 ADP - 2007 ADP conversion I did..

Performance was unacceptable due to neverending queries (1000s per SECOND!) from the project asking for database schema information. this would start after a user did a 'filter by form' filter on a form and then closed the form - these queries would then start and continue until the project was closed. Imagine 15 users * 1000s of queries/second. I found this by running a trace on the SQL server using SQL profiler.

The solution?

I created a new blank ADP in 2007 (which still shows Access 2000-2003 format by the way). I copied/pasted all the objects from my existing ADP into it. Viola - no more endless queries. Performance is OK.

Nice Access bug.

BTW what I had been working on was a 2003 ADP that had been opened and modified in 2007. Now I have a 2003 ADP that was *CREATED* and modified in 2007. That works.

Bill Kuhn - MCSE
bkuhn@kuhngroup.com
The Kuhn Group, Inc.

http://www.kuhngroup.com

 

[lameid]

Sounds like you had a SQL server upgrade in there too.

Access 2003 does not support SQL 2005 for design changes. It has something to do with the extended properties, I think. The only way to manage SQL 2005 objects if your using Access 2003 is to not use Access. I bet Access 2007 was getting confused by extended properties and cleaned them up with what you did.

I have a hunch that making a copy of the front end and upgrading to 2007 ADP would have fixed the problem. I still use Access 2003 so don't look for a test from me in the near future.

I aggree though it is a stupid bug. So is depricating the functionality to use Access import specifications programmatically in 2007.

Honestly, having gone through an MDB to ADP migration, I don't see the point. I would much rather keep all the MDB functionallity and deal with pass thru queries and code to manage updates instead.

It seems to me that Access 97 was the version that did what it was supposed to. Although, ADO is good if you are not working with JET data. But who needs a UNIFIED help system and the ability to build to controls in code anyways?

Sorry about the Soapbox... you got me started with the comment about current Microsoft technology stinking.

I'm definitely deprecating my upgrade budget for Office.

Funny thing, I thought office sales generated more revenue than Visual Studio .NET and OpenOffice.

 

[BillKuhn]

You would think so. Office is AFAIK the second highest revenue generator for MS (besides OS). It would be nice to keep some QC in it.

This was an upgrade from an Access2003 MDB to ACCESS2007 ADP by the way. The purpose was just to give them the reliability of a SQL data store as a temporary step to a .NET/SQL application.

What a certified PITA.

Bill Kuhn - MCSE
bkuhn@kuhngroup.com
The Kuhn Group, Inc.

http://www.kuhngroup.com

 

[BillKuhn]

I just realized my original post on this thread said we started with a 2003 MDP. Wrong. It was an MDB.

Bill Kuhn - MCSE
bkuhn@kuhngroup.com
The Kuhn Group, Inc.

http://www.kuhngroup.com