Converting ADP to ACCDB with Juan Soto

Legacy software migration often presents unique challenges for Access developers, especially when dealing with discontinued technologies.

In this Access User Group presentation, Juan Soto, Microsoft Access MVP and president of the Access with SQL Server Group, shares essential insights about converting ADP (Access Data Project) files to modern ACCDB format. The talk covers the historical significance of ADP files, their unique capabilities with SQL Server integration, and practical considerations for successful migration projects.

Whether you're facing an ADP conversion project or simply interested in understanding this legacy technology, this presentation offers valuable context and practical guidance for handling these specialized migrations.

Understanding ADP Files

Historical Context

• Created prior to Access 2003
• Last supported creation in Access 2003/2005
• Could still be used (but not created) in Access 2010
• Many legacy systems still actively using ADP files

Key Features

• Direct SQL Server connection without ODBC drivers
• Native table and view integration
• Ability to bind forms to stored procedures
• Queries saved as SQL Server views
• Tightly integrated with SQL Server

Migration Considerations

When to Convert

• Office 365 compatibility issues
• Can't mix with modern Office versions
• Only convert when absolutely necessary
• Still viable if current environment supports it

Technical Process

• Create new ACCDB file
• Import forms, reports, and modules
• Skip query/table imports (server-side objects)
• Link tables and views using current ODBC driver
• Review all form record sources

Common Challenges

Form Conversions

• Stored procedure bindings need rework
• Pass-through queries are read-only
• View binding requires special handling
• Need to maintain naming conventions

View Management

• Keep original query names when creating views
• Consider performance implications
• Execution plan advantages with views
• DDL commands for index creation

Performance Optimization

SQL Server Considerations

• View execution plans vs. temporary queries
• Cached execution plans for views
• Performance improves with usage
• Memory management benefits

Best Practices

• Use views for complex queries
• Consider temporary tables for specific scenarios
• Watch for query performance issues
• Monitor execution plans when needed

Conclusion

Converting ADP files to ACCDB format requires careful planning and deep understanding of both Access and SQL Server technologies.

While the process can be complex and time-consuming, Juan's presentation offers a clear roadmap for handling these migrations successfully, emphasizing the importance of maintaining functionality while leveraging modern Access capabilities.

Recording

The full recording is available on YouTube:

Join Live!

Want to get even more out of these presentations? Join the live Access User Group events! The next upcoming events are listed on the AUG Event Calendar.

Attending live gives you the opportunity to:

• Interact directly with presenters during Q&A sessions
• Network with other Access developers
• Share your own experiences and challenges
• Get immediate answers to your specific questions
• Participate in group discussions

With multiple user groups across different time zones (and languages!), you're sure to find a meeting time that works for your schedule.

Acknowledgements

• Base cover image generated by FLUX-schnell
• Initial draft generated by Claude-3.5-Sonnet