SQL to VBA and back again (1 Viewer)

Status
Not open for further replies.

isladogs

MVP / VIP
Local time
Today, 16:14
Joined
Jan 14, 2017
Messages
18,186
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.
Useful for testing when the VBA string is long & complex as shown below







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

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

Any comments welcomed.

See post #6 for the latest version
 

Attachments

  • SQL2VBA.accdb
    488 KB · Views: 1,396
  • #Query1.PNG
    #Query1.PNG
    39 KB · Views: 3,919
  • SQL.PNG
    SQL.PNG
    39.1 KB · Views: 4,080
  • vba.PNG
    vba.PNG
    43.7 KB · Views: 4,976
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 16:14
Joined
Sep 21, 2011
Messages
14,044
Hi Colin,
Thanks very much for this. I spend a fair bit of time creating a query in the designer and then try and amend for VBA.

Just for your info the display for the resize is a little off on my screen running at 1920*1080. The form is still usable, but thought I'd let you know.

Please see attached.
 

Attachments

  • sql2vba.png
    sql2vba.png
    76 KB · Views: 1,140

isladogs

MVP / VIP
Local time
Today, 16:14
Joined
Jan 14, 2017
Messages
18,186
Thanks for the info Gasman
By chance I have a secondary monitor with exactly that resolution.

Have modified the form size slightly & it now seems to be fine.

NOTE for other users:
No other changes made
If the earlier version worked for you, there is no need to download this

See post #6 for the latest version
 

Attachments

  • SQL2VBA.accdb
    492 KB · Views: 820
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 16:14
Joined
Sep 21, 2011
Messages
14,044
Thank you Colin.
I obviously have something wrong with my system. the new version hides the buttons even more. :D

I 've now amended the form, to fit on my system by changing the tab size. Thank you for leaving it to be able to be amended.
 

Attachments

  • design mode.PNG
    design mode.PNG
    15.4 KB · Views: 664
  • form mode.PNG
    form mode.PNG
    12.2 KB · Views: 678

isladogs

MVP / VIP
Local time
Today, 16:14
Joined
Jan 14, 2017
Messages
18,186
I've just done a few minor tweaks & bug fixes to this utility (its now on v3.02 ....though the screenshot says 3.01!)

Main changes:
a) Fixed error 2046 that recently appeared when clicking 'Copy Active Window' button. It used to be fine . . .
b) Added the option to start the VBA statement generated with CurrentDB.Execute or DoCmd.RunSQL according to user preference



The eagle eyed will notice I've swopped the tab order so SQL is now on the left as its the usual starting point for most people using this.

See post #6 for the latest version
 

Attachments

  • Capture.PNG
    Capture.PNG
    23 KB · Views: 2,951
  • SQL2VBA.zip
    76.6 KB · Views: 810
Last edited:

isladogs

MVP / VIP
Local time
Today, 16:14
Joined
Jan 14, 2017
Messages
18,186
I've just updated this to v3.03.
This fixes an issue with the Copy to Clipboard API in 64-bit Access.
 

Attachments

  • Sql2vba.zip
    70.4 KB · Views: 894

Scribtor

Registered User.
Local time
Today, 17:14
Joined
Sep 27, 2018
Messages
16
Simply awesome!


If I only had this earlier, it would've made my life so much easier!

Thanks a bunch for this!!!
 

Cliff67

Registered User.
Local time
Today, 09:14
Joined
Oct 16, 2018
Messages
175
Brilliant job, the amount of times I've switched between QBE and SQL view I can't begin to guess

thanks again Colin
 
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom