Version 3.42 Last Updated 18 July 2024 Approx 1 MB (zipped)
Section Links:
Background Info
Download
Installation Instructions
Using the Add-In
Version History
Video
Acknowledgements
Feedback
UPDATED 17 JULY 2024 - New ADD-IN version with additional features
This is an updated version of my SQL to VBA and Back Again utility app.
This new version is available as an Access add-in so it can be run directly from any Access app
Background Info Return To Top
Some years ago, Allen Browne created a small utility called Sql2vba.
It is available from his website: http://allenbrowne.com/ser-71.html
Allen wrote that 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."
When I started developing in Access, I found this so useful that I extended the idea to work in the other direction as well, VBA to SQL, so that I could easily create and load a query based on converted VBA code. This is useful for testing when the VBA string is long & complex as shown below.
I had always intended to create an add-in version to make it even easier for developers to use in conjunction with their own apps.
I was finally prompted to do so following an exchange of emails with fellow Access developer, Michael Villegas.
I also took the opportunity to add some additional functionality not available in the earlier standalone version.
Download Return To Top
This free add-in is supplied as a 32-bit or 64-bit ACCDE file and will run in Access 2010 or later.
Click to download:
a) 64-bit ACCDE version for Access 2010 or later:
SQL to/from VBA Converter 2010+ 64-bit 0.8 MB (ACCDE file - zipped)
b) 32-bit ACCDE version for Access 2010 or later:
SQL to/from VBA Converter 2010+ 32-bit 0.8 MB (ACCDE file - zipped)
The zip files also include a PDF user guide with full installation instructions and detaied information on using the add-in.
Download and unblock the zip file. For more details, see my article: Unblock downloaded files by removing the Mark of the Web
Unzip and save the ACCDE file to your add-ins folder and make this a trusted location (see below for more details).
As it is an ACCDE file, the code is not accessible. However, an ACCDA file with all source code may be purchased at a reasonable price.
Click here to purchase the source code version.
Installation Instructions Return To Top
1. Download the zip file to your computer
The zip file contains the 32-bit or 64-bit add-in (ACCDE file) and a PDF file with instructions on installation and usage.
Unzip and copy the file, SQL2VBA_v342_AI_x64.accde or SQL2VBA_v342_AI_x32.accde to your Access add-ins folder.
The folder path is usually: C:\Users\YourWindowsUserNameGoesHere\AppData\Roaming\Microsoft\AddIns
NOTE:
You will need to tick Show Hidden Items in File Explorer to see the AppData folder
2. Browse to and run the .accde application from your AddIns folder to 'register its location'. This message appears:
The path settings have been updated in a hidden system table USysRegInfo. Click OK to close the add-in
3. Open any Access app and go to Database Tools . . . Add-Ins on the ribbon
4. Click Add-in Manager. This dialog appears showing all the available add-ins on your workstation. Installed add-ins are marked with a X
Select the SQL to VBA Converter add-in from the list and click Install then click Close.
The SQL to VBA Converter add-in is now available for use with all your Access apps
5. You can also install Add-Ins from the File . . . Options . . . Add-Ins menu
Select Access Add-ins on the dropdown then click Go
The Access Add-in Manager will open - same as step 4
6. To run the add-in, again go to Database Tools . . . Add-Ins on the ribbon and select SQL to VBA Converter from the list
The SQL to/from VBA Converter form will open in your app. At first, most or all of the buttons will be disabled.
7. The form will appear in the centre of the screen but can be moved to any more convenient location. It can also be minimized at any time.
8. If you close the SQL to/from VBA Converter form at any time, just repeat step 6 to re-open it
Using the Add-In Return To Top
1. The form is loaded automatically when it is loaded as an Access add-in. It can also be used as a standalone app if preferred.
2. To use the form, copy a query SQL or VBA statement to the clipboard
Select the required tab (SQL or VBA), then click the Paste button (or use Ctrl+V).
Once you have pasted in a query SQL or VBA statement, click the appropriate Convert button
Converting SQL to VBA will:
a) Add strSQL = " at the start together with line continuations and other related items.
b) Change quotes (") to double quotes ("") leaving apostrophes (') unchanged e.g. around string values
c) Capitalise all keywords e.g. Update => UPDATE, Group By => GROUP BY etc
d) The VBA string created can be used in code or to make a temp query for testing
Converting VBA to SQL will:
a) Strip out all superfluous items e.g. strSQL = , leading and trailing quotes && line continuations
b) Convert double quotes ("") to quotes (") leaving apostrophes (') unaltered
c) The resulting SQL can be used to create a query in SQL or design view
The output from each conversion should ALWAYS be tested using the Test Query SQL / Code From VBA buttons
When running your SQL statements in VBA code:
i) Use CurrentDb.Execute or DoCmd.RunSQL with action queries (Append, Update, Delete, Make Table). For example:
CurrentDb.Execute "UPDATE tblSettings SET tblSettings.Version = 3.2, tblSettings.VersionDate = #6/27/2021#;", dbFailOnError
ii) For Select queries, you need to create and open a query definition. For example:
Dim strSQL As String, qdf As DAO.QueryDef
strSQL = "SELECT ID, Version, VersionDate FROM tblSettings;"
Set qdf = CurrentDb.CreateQueryDef("MyTempQuery", strSQL) ' create temp QueryDef object
DoCmd.OpenQuery "MyTempQuery" ' view the query
CurrentDb.QueryDefs.Delete "MyTempQuery" ' delete the temp query
3. You can use Ctrl+Enter to add line breaks at selected positions in your query SQL.
The line breaks will be retained on conversion in both directions
4. Alternatively, when the SQL tab is open, tick the Expand SQL checkbox to add a line break after each field.
You can also opt to Include vbCrLf in VBA statements before each line break
5. If there are SQL or VBA errors, the text string will be marked in RED.
The errors MUST be corrected before you can test the output or run the conversion
For example, there are 2 initial quotes at the start of the VBA statement below:
In the SQL statement below, the TRANSFORM keyword is spelt incorrectly and there is an extra character after the end semicolon.
6. Click the Test Query SQL button to create a temp query in your app for testing.
The query will open in design view (except for SQL-only queries such as UNION, DDL etc)
For easy identification, the temp queries from SQL are named #qrySQL1, #qrySQL2 etc.
7. If the host app is an ACCDB file, click the Code From VBA button to create an example procedure in module modExampleVBACode in the host app.
If the module does not exist, it will be created automatically.
The procedure will be called e.g. ExampleCode_qryVBA1.
Sub ExampleCode_qryVBA1()
'=========================================================
' Example code created by the SQL2VBA converter add-in
' Author: Colin Riddington (Mendip Data Systems)
' Date: 12/07/2024
' Copy & paste this code into your app. Modify as appropriate
' OR reference it in your own procedure
'=========================================================
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
' define the VBA string
strSQL = "SELECT MSysObjects.Name AS QueryName, tblSysObjectTypes.SubType AS QueryType, " & _
" GetQueryLastSavedView([Name]) AS LastSavedView, GetDateLastUpdated([Name]) AS LastSavedDate" & _
" FROM MSysObjects INNER JOIN tblSysObjectTypes ON (MSysObjects.Flags = tblSysObjectTypes.Flags)" & _
" AND (MSysObjects.Type = tblSysObjectTypes.Type)" & _
" WHERE (((MSysObjects.Flags)<>3) AND ((MSysObjects.Type)=5))" & _
" ORDER BY MSysObjects.Name;"
' Set the database to the current database
Set db = CurrentDb
' Debug.Print Left(strSQL, 6)
' Open/execute temp query
If Left(strSQL, 6) = "SELECT" Then
' Close & delete existing temp query if it exists
On Error Resume Next
DoCmd.Close acQuery, "~qryVBA1"
db.QueryDefs.Delete "~qryVBA1"
On Error GoTo 0
' Create a new temp query
Set qdf = db.CreateQueryDef("~qryVBA1", strSQL)
' Open the query in design view
DoCmd.OpenQuery "~qryVBA1", acViewDesign
Else
db.Execute strSQL, dbFailOnError
End If
' Clean up
Set qdf = Nothing
Set db = Nothing
End Sub
A temp query ~qryVBA1 will also be created.
If the code is run, the temp query ~qryVBA1 will be over written.
Temp queries from VBA are called ~qryVBA1, ~qryVBA2 etc
NOTE:
If the host application is an ACCDE file, you cannot create new code, but you can still create temp queries for testing.
The button caption is now labelled Test Query VBA.
8. Click the Delete Temp Queries button to delete all temp queries when no longer needed
Before doing so, rename any temp queries that you want to keep.
9. You can also copy the active window e.g. for pasting the VBA into your own code in the Visual Basic Editor or into Notepad etc.
10. Click Clear Windows to clear both the SQL and VBA tabs and empty the clipboard
11. If you close the SQL2VBA form at any time, just select it from the list of Access add-ins to re-open it.
Version History Return To Top
3.03 - 28 Sep 2018: fixed issue with Copy to Clipboard code in 64-bit Access
3.23 - 14 Apr 2023: minor bug fixes mainly to remove legacy code no longer used
3.27 - 22 Jun 2024: modified code to fix issues with functions such as IIf & DLookup
3.28 - 24 Jun 2024: added expand SQL & include vbCrLf options
3.30 - 26 Jun 2024: added create query from VBA string. Other minor code changes
3.32 - 29 Jun 2024: added tab inset to expanded SQL / VBA. Generic removal of any leading string when converting VBA to SQL or to temp query
3.42 - 12 Jul 2024: converted to an 32/64-bit ACCDE add-in with additional functionality
Video Return To Top
A video demonstrating how this example app works will be made available on YouTube in the near future.
Acknowledgements Return To Top
With thanks to:
Allen Browne for the original idea used in this application
Dale Fye for code used in converting the original utility to an add-in
Philipp Stiefel for suggesting alternative code for creating a module from an add-in
Michael Villegas for suggesting additional features and testing various versions in the development of this add-in
Feedback Return To Top
Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems Last Updated 18 July 2024
|
Return to Access Add-Ins Page
|
Return to Top
|