Leveraging SQL Server SPROCs with George Hepworth

Ever wondered if moving your business logic from the interface to the database might be the key to building more maintainable, cross-platform applications?

In this Access Pacific presentation, George Hepworth shared his ambitious journey of recreating the Northwind Developers Edition template using Power Apps, with all business logic consolidated into SQL Server stored procedures. What started as an experiment to test Power Apps capabilities evolved into a comprehensive exploration of database-centric development patterns that could benefit Access developers working across multiple platforms. George's approach aims to create a single set of stored procedures that can serve multiple interfaces—Access, Power Apps, .NET applications, and beyond.

Whether you're curious about Power Apps development, interested in stored procedure design patterns, or considering how to future-proof your Access applications, this presentation offers valuable insights into modern database development approaches and the challenges of building truly platform-agnostic solutions.

Project Background and Motivation

Core Questions Driving the Project

• Should business logic reside in the interface or the database?
• Can solo developers achieve competency across multiple platforms (.NET, VBA, Power Apps)?
• Is team collaboration realistic between SQL Server, VBA, and Power Apps developers?
• Does encoding business logic in the database violate best practices?

Personal Drivers

• Experience working on the Northwind 2 development team
• Long-standing interest in web technologies and Power Apps
• Concerns about Access's future and alternative tools for power users
• Desire to move away from copy-paste development patterns

Technical Approach and Architecture

Power Apps Implementation Goals

• Create all core CRUD operations (Create, Read, Update, Delete) for seven entities:
  • Products, Companies, Employees, Orders, Purchase Orders, Order Details, Purchase Order Details
• Implement data validation workflows (order progression through four stages)
• Migrate from Access ACCDB to SQL Server (Power Apps requirement)
• Build responsive interface for browser and tablet use

Stored Procedure Design Pattern

• Entity-based hierarchy: Seven master stored procedures (one per entity)
• CRUD operations: Four child procedures per entity (28 total nested procedures)
• Single entry point: All operations channeled through master procedures
• Consistent messaging: Standardized output parameters for success/error reporting

Key Design Decisions

• Entity-based vs. operation-based organization
• Premium Power Apps license required for SQL Server connectivity ($20/user/month)
• Unbound screens throughout (Power Apps limitation)
• Hidden screen architecture for code consolidation

Code Organization and Patterns

Master Stored Procedure Structure

-- CRUD types: 1=Create, 2=Read, 3=Update, 4=Delete
-- Default to Read (2) for safety
-- Zero primary key indicates new record creation
-- Standardized output parameters: Unified ID, Error Code, Error Message

Power Apps Integration

• Hidden control mechanism: Toggle buttons trigger stored procedure calls
• Variable management: Global variables control CRUD operations
• Error handling: SQL Server error messages parsed for user-friendly display
• Performance considerations: 18 screens vs. typical 1-10 in Power Apps

Documentation and Maintenance

• Claude AI assistance: Used for stored procedure logic development
• Naming conventions: 'z' prefix for internal-only child procedures
• Optional parameters: SQL Server 2022 syntax for flexible parameter passing
• Code generation potential: VBA-based automation for procedure creation

Development Challenges and Solutions

Power Apps Limitations

• No native code reusability (copy-paste required)
• Limited bound form concepts
• Performance issues with complex applications
• Browser-based debugging constraints

SQL Server Integration Benefits

• Centralized business logic: Single source of truth for data operations
• Cross-platform compatibility: Same procedures serve multiple interfaces
• Maintenance efficiency: Logic changes isolated to database layer
• Error handling: Consistent messaging across all client applications

Alternative Approaches Discussed

• SharePoint workarounds: Power Automate for license-cost avoidance
• Dynamic SQL: Template-based procedure generation
• Code generation: VBA-based automation for stored procedure creation

Advanced Concepts and Future Directions

Potential Improvements

• Class-based implementation: Converting procedures to reusable patterns
• Template systems: Boilerplate code generation for consistent structure
• Field-level operations: Dynamic parameter handling based on table schema
• Multi-interface support: Single procedure set serving Access, .NET, Power Apps

Team Development Model

• Skill separation: Database developers vs. interface developers
• Collaboration patterns: Version control considerations for multi-platform projects
• Documentation requirements: Comprehensive procedure documentation essential

Distribution and Scaling

• Script generation: Visual Studio database project capabilities
• GitHub hosting: Open source distribution potential
• Microsoft partnership: Possible Northwind 3.0 collaboration

Learning Resources and Next Steps

Key Takeaways

• Premium licensing costs are significant consideration
• Debugging stored procedures requires specialized skills
• Code generation can eliminate manual procedure creation
• Business logic centralization enables true platform independence

Recommended Tools

• Claude AI: More developer-oriented than ChatGPT for this work
• Visual Studio: Database project generation capabilities
• Access VBA: Table schema analysis for automated code generation
• SQL Server Management Studio: Essential for procedure development and debugging

Conclusion

George's experiment demonstrates both the potential and complexity of database-centric development patterns. While the approach requires significant SQL Server expertise and premium licensing costs, it offers compelling advantages for teams working across multiple platforms. The ability to maintain business logic in a single location while serving multiple interfaces addresses real-world development challenges, particularly for organizations considering migration strategies or multi-platform deployments.

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-Sonnet-4