Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

Example Apps for Businesses, Schools & Personal Use

Version 3.03    Updated 28/09/2018              Approx 0.6 MB

 

Some years ago, the wonderful Allen Browne created a small utility called Sql2vba.

See his website: http://allenbrowne.com/ser-71.html

 

The purpose was to save developer time:

"Rather than typing complex query statements into VBA code, developers often mock up a query graphically, switch it to SQL View, copy, and paste into VBA.

If you've done it, you know how messy it is sorting out the quotes, and the line endings.

Solution: create a form where you paste the SQL statement, and get Access to create the SQL string for you."

 

I found this so useful that I extended the idea to work in the other direction as well:

VBA to SQL so I could easily create & load a query based on VBA code.

This is useful for testing when the VBA string is long & complex as shown below.

 

With Allen's permission, I am publishing this utility here

To use this utility, just copy the form frmSql2vba & the 2 modules modFunctions & modResizeForm to your own applications

 

The latter module contains code originally by Jamie Czernak used to automatically resize forms depending on users' screen resolution.

If you don't want to use that part:

a) omit the module modResizeForm 

b) remove the line ResizeForm Me from the frmSql2vba Form_Open procedure

 

Click to download:  SQL to VBA and back again (zipped)

 

NOTE: Updated 28/09/2018 (v3.03) to fix issue with Copy to Clipboard code in 64-bit Access

SQL to VBA and back again  

Click any image to view a larger version ...

Screenshots

vba QuerySQL Return to Example Databases Page