Here I sit with what I thought would be a fairly straightforward upgrade from an Access2003 MDB to an Access2007 ADP using SSMA. After MONTHS of work, I've actually got the ADP working almost completely. The client has now migrated to it.
Now the performance issues jump out - using this ADP is disgustingly slow. Things that took a couple of seconds in an MDB in 2003 take a LONG time in the ADP. Just switching records on a form is painful.
The SQL database is working fine, and is fast when accessed by anything but the ADP.
I've been searching the internet and keep seeing mention of using an MDB with ODBC to connect to SQL instead of an ADP.
Would this help? Would this be reliable?
Is there a way to do this w/o significant rewrite of the ADP?
The performance currently is DISMAL and inexcusable (thanks Microsoft - apparently ADPs worked better in 2003). I need to improve it drastically.
> upgrade from an Access2003 MDB to an Access2007 ADP using SSMA. After MONTHS of work, I've actually got the ADP working almost completely.
You are late to the party. It's exactly in Office 2007 Microsoft has finally decided that ADP will be depricated in the near future. It's still supported in 2007, but as you might have noticed, "ADP" has disappeared from "create" menu. And for a reason.
> keep seeing mention of using an MDB with ODBC to connect to SQL instead of an ADP. > Would this help? Would this be reliable?
Yes, with returning to MDB, this will be indeed the only option. However, I wouldn't hold my breath regarding the performance. In these days, performance is not even in the first dozen of priorities. Open any technical magazine, and 9 out of 10 topics will be about security. That's what everybody should be thinking about. Another important consideration for Office 2007 developers was to turn it from a tool into Experience. Hence Ribbon etc. So, the answer to the 2nd question - whether anything related to Office 2007 would be reliable - is negative. Reliability is not a priority either. Security and Experience are. Have fun and be safe. Whore's values.
> Is there a way to do this w/o significant rewrite of the ADP?
No. MDB is totally different from ADP.
That said, try to compile the project into ADE, and use bound forms. When I was beta-testing it, I did not notice any significant performance problems. Are you connecting to sql server in local network?
- you are not alone, and performance is not the only problem ahead. Are you sure you want 2007? while there are clearly problems, what advantages are you expecting compared to 2003? the Ribbon?
