I've been approached to do some debuggin' on an older Access ADP application (built by others). I've been an Access developer professionally for 12 yrs, but have stayed happily in the mdb/accdb world. Also I have a fair amt of experience with SQLServer back end (although a bit rusty).
The application appears to be straight forward. Beyond fixing a few offending Error messages, the client may soon be looking to upgrade, enhance or move up to a new solution (so possibly more work for me). Because of the ADP element, in the past I'd probably pass on this project. However, times are tough (think?) so I'm fishing for some advice:
1. Is ADP still viable or should I recommend the app be converted right off?
2. Can I open this file and debug it properly in 2007 or should I work in 2003?
3. What tech./coding pitfalls should I be on the lookout for?

Since you are not getting any replies, I would throw in my humble opinion:

I have been doing Access/SQL for at least 8 years, and I have never seen the need to go to ADP. What that buys you is a SQL Server Enterprise Manager-type interface in Access, but I have always found it easier to just use the Enterprise Manager and SQL Query Analyzer. I also use DNS-less connection objects in Access VBA, and manage all my errors in Access so the user doesn't get any SQL Server errors.

This also gets into database design issues. Some developers will make their SQL tables without constraints and relationships, and manage all of that in the application layer. This is fine and works well for data access by other applications, but those others might corrupt your data if the constraints and relationships are not documented and adhered to somehow. Depends on the environment. If you use SQL constraints and relationships, you risk throwing SQL Server errors at your users, and ADP will help with this somewhat.

You will also want to decide if you want your users connecting directly to SQL Server tables and views. This can be a big convenience since you can use CurrentProject.Connection instead of coding the long way around, but it can also lead to trouble if users try to work in datasheet view. Pesky users.

I have coded the long way around with local security, local tables, moving recordsets as needed, processing locally and moving modified data back as needed. That can work pretty well, depending on how many actions the users need to take with data. This will also raise the price of the app since design and build takes longer.

So to answer your questions:

1. No, convert the app.
2. I would work with it in 2003 since 2007 is kludgey with older code.
3. If you are using mdb/mde, or 2007 file formats, try to use DNS-less connections. Also, build your queries on SQL Server with the Enreprise Manager. Look into stored procedures since they can be a big help. There are some coding issues with passing arguments to sp_, but they are pretty well documented.

Did any of that help?

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com