Running SQL Stored Procedures from Access VBA

Running SQL Stored Procedures from Access VBA

One of our applications had been in use for several years and had started to slow down due to the size of the back-end database. We tried a number of methods to increase the processing speed but ultimately needed to change the back-end database to SQL.

Once the backend data was migrated to SQL from an Access ACCDB file, we implemented several stored procedures to significantly enhance processing speed. These stored procedures not only streamlined operations but also eliminated many inefficiencies inherent in VBA-based processing. For example, a routine tasked with pricing 12,000 entries and verifying available discounts previously took approximately 26 minutes to complete. By transitioning this process to an SQL Stored Procedure, we reduced the processing time to an astonishing 20 seconds, demonstrating the transformative power of optimized database operations.

The stored procedure highlighted below does not return any records because it validates entries directly on the transaction table. This approach avoids unnecessary data retrieval, ensuring faster and more efficient execution. To simplify the connection setup, we use the table “tblVatRates” table for the connection string, avoiding reliance on system variables and making the code more portable and easier to manage.

This shift to stored procedures not only improved performance but also enhanced maintainability, scalability, and reliability across the system.

Function to Execute Stored Procedures
Function ExecStoredProcedure(tmpStoredProcedureName)
Dim qrySQL As DAO.QueryDef
Set qrySQL = CurrentDb().CreateQueryDef(vbNullString)
qrySQL.Connect = CurrentDb.TableDefs("tblVatRates").Connect
qrySQL.SQL = "exec " & tmpStoredProcedureName
qrySQL.ReturnsRecords = False
qrySQL.Execute
Set qrySQL = Nothing
End Function
Access to SQL

When moving a back-end from an Access ACCDB file to an SQL-based system, several considerations must be made to ensure a smooth transition and a robust end result. Here are the key factors to consider:


1. Scalability Requirements

  • Access Limitations: Access databases are typically suitable for small to medium-sized systems, but they struggle with scalability when user numbers or data volumes increase.
  • SQL Advantage: SQL databases (e.g., SQL Server, MySQL, PostgreSQL) are designed to handle much larger datasets and concurrent users. Assess whether your application needs to support future growth.

2. Performance Improvements

  • Consider how SQL’s advanced indexing, query optimization, and stored procedures can drastically improve performance.
  • Evaluate the performance bottlenecks in the ACCDB file that you aim to resolve.

3. Data Integrity and Reliability

  • Access uses a file-based architecture that can lead to corruption in multi-user environments.
  • SQL databases provide transaction management, data consistency, and backup/restore capabilities for greater reliability.

4. Security Enhancements

  • SQL databases offer more robust security features, such as encryption, role-based access control, and integration with identity providers.
  • Ensure sensitive data is handled securely, and assess the need for compliance with regulations like GDPR or HIPAA.

5. Concurrency and Multi-User Support

  • If the system will be used by multiple users simultaneously, SQL databases are significantly better at handling concurrent connections.
  • Test how your application will behave under the new architecture in terms of locking, isolation levels, and contention.

6. Application Changes

  • Frontend Modifications: Ensure the frontend application (if Access or another tool) can connect to the SQL database. This may involve rewriting queries or modifying connection strings.
  • Code Refactoring: Move business logic from VBA or macros in Access to SQL stored procedures or application-level logic.

7. Data Migration

  • Plan the data migration carefully. Analyze data integrity in the ACCDB file and clean up anomalies before transferring data to the SQL backend.
  • Use tools or scripts to automate data migration and validate data after the transfer.

8. Infrastructure Requirements

  • SQL databases require dedicated infrastructure, which might include cloud hosting, on-premise servers, or hybrid setups.
  • Evaluate hardware, licensing, and operational costs.

9. Cost Implications

  • Access: Usually included with Microsoft Office, so there’s little additional cost.
  • SQL: Depending on the chosen database, costs may include licensing, hosting, and maintenance. Open-source options like MySQL and PostgreSQL may lower costs.

10. Training and Expertise

  • Ensure your team has the necessary skills to manage and maintain an SQL database.
  • If the team lacks SQL expertise, plan for training or hire experienced professionals.

11. Backup and Disaster Recovery

  • SQL databases offer more robust backup and recovery solutions. Determine the frequency and methods of backup, and plan for disaster recovery.

12. Integration with Existing Systems

  • Assess how the SQL database will integrate with other systems or tools, including reporting platforms, APIs, and third-party software.

13. Long-Term Maintenance

  • Consider ongoing maintenance requirements, such as patching, updates, and performance tuning.
  • Assign clear responsibilities for database administration (DBA) tasks.

14. Testing and Validation

Develop a rollback plan in case issues arise during or after the migration..

Perform extensive testing after the migration to ensure data integrity, functionality, and performance.


Other Links running a stored procedure from Excel

Linked in Article

Check out our access db samples for other examples


Leave a Reply

Your email address will not be published. Required fields are marked *