Go Back   Access World Forums > Microsoft Access Reference > Sample Databases

 
Closed Thread
 
Thread Tools Rate Thread Display Modes
Old 04-24-2017, 06:50 AM   #1
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,052
Thanks: 115
Thanked 3,020 Times in 2,747 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
SQL to VBA and back again

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
Attached Images
File Type: png #Query1.PNG (39.0 KB, 2321 views)
File Type: png SQL.PNG (39.1 KB, 2276 views)
File Type: png vba.PNG (43.7 KB, 2817 views)
Attached Files
File Type: accdb SQL2VBA.accdb (488.0 KB, 677 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


I know that you know that we both know nothing

Last edited by isladogs; 12-23-2018 at 04:42 AM. Reason: Added inline images
isladogs is offline  
The Following 6 Users Say Thank You to isladogs For This Useful Post:
Cliff67 (12-20-2018), gleesonc (07-05-2017), NauticalGent (05-02-2017), Scribtor (10-10-2018), sonic8 (12-27-2018), usm01 (07-08-2017)
Old 05-08-2017, 02:09 AM   #2
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,396
Thanks: 436
Thanked 805 Times in 780 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: SQL to VBA and back again

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.
Attached Images
File Type: png sql2vba.png (76.0 KB, 233 views)
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline  
Old 10-25-2017, 05:09 AM   #3
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,052
Thanks: 115
Thanked 3,020 Times in 2,747 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: SQL to VBA and back again

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
Attached Files
File Type: accdb SQL2VBA.accdb (492.0 KB, 296 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


I know that you know that we both know nothing

Last edited by isladogs; 12-24-2018 at 09:36 AM.
isladogs is offline  
Old 10-25-2017, 07:02 AM   #4
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,396
Thanks: 436
Thanked 805 Times in 780 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: SQL to VBA and back again

Thank you Colin.
I obviously have something wrong with my system. the new version hides the buttons even more.

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.
Attached Images
File Type: png design mode.PNG (15.4 KB, 186 views)
File Type: png form mode.PNG (12.2 KB, 182 views)
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline  
Old 11-14-2017, 09:31 AM   #5
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,052
Thanks: 115
Thanked 3,020 Times in 2,747 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: SQL to VBA and back again

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
Attached Images
File Type: png Capture.PNG (23.0 KB, 1655 views)
Attached Files
File Type: zip SQL2VBA.zip (76.6 KB, 274 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


I know that you know that we both know nothing

Last edited by isladogs; 12-23-2018 at 04:43 AM.
isladogs is offline  
Old 09-28-2018, 02:42 AM   #6
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,052
Thanks: 115
Thanked 3,020 Times in 2,747 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: SQL to VBA and back again

I've just updated this to v3.03.
This fixes an issue with the Copy to Clipboard API in 64-bit Access.
Attached Files
File Type: zip Sql2vba.zip (70.4 KB, 215 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


I know that you know that we both know nothing
isladogs is offline  
The Following User Says Thank You to isladogs For This Useful Post:
eizik_g (11-10-2018)
Old 10-08-2018, 10:47 PM   #7
Scribtor
Newly Registered User
 
Join Date: Sep 2018
Posts: 16
Thanks: 8
Thanked 0 Times in 0 Posts
Scribtor is on a distinguished road
Re: SQL to VBA and back again

Simply awesome!


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

Thanks a bunch for this!!!

Scribtor is offline  
Old 10-09-2018, 01:07 PM   #8
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,052
Thanks: 115
Thanked 3,020 Times in 2,747 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: SQL to VBA and back again

Glad you found it useful....
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


I know that you know that we both know nothing
isladogs is offline  
Old 12-20-2018, 06:45 AM   #9
Cliff67
Newly Registered User
 
Join Date: Oct 2018
Location: Southend-On-Sea, Essex, UK
Posts: 94
Thanks: 34
Thanked 2 Times in 2 Posts
Cliff67 is on a distinguished road
Re: SQL to VBA and back again

Brilliant job, the amount of times I've switched between QBE and SQL view I can't begin to guess

thanks again Colin

Cliff67 is offline  
Closed Thread

Tags
sql , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help I Need Ideas, Setup Local Back-end that Updates Server Back-end panthers1788 General 1 07-02-2013 12:15 AM
Back to back Ashes Series 2013 SpentGeezer Politics & Current Events 4 01-10-2013 08:38 PM
is there any way of getting them back? kevinasam Other Software 1 12-28-2011 03:45 PM
run repors back to back mrrayj60 Reports 4 08-11-2010 11:51 PM
Can't get into back end PaulJK General 3 11-20-2002 12:20 PM




All times are GMT -8. The time now is 08:30 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World