UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> SQL to VBA Formatter    
 
   
dallr
post Apr 28 2006, 10:28 AM
Post #1

UtterAccess VIP
Posts: 3,075
From: The Land of the Access Ninja's



Hey guys i have created this code to formant SQL to be used in VBA.It only formats SELECT statements and Insert into statement with VALUES()
The main form in the attached download opens to a new record. But I have also included some sample SQL to show you how to enter the data. (This can be found from records 1 to 4)
The JetSQL control on the main form takes the SQL syntax in a certain format. eg.
Select .........
From .........
Where.......
etc
Note: To enter it in the above format when a statement line is finished your have to press the CTRL and Enter keyboard keys simultaneously to move to a new line.
The SQL must not have all Select From and Where, etc on the same line.
If your SQL has strings, or numeric values there is a subform on the R.H.S where you can paste the specific values that need formatting. When passing values to the sql string the combo box ending with "form controls” are used for variables and form controls eg([forms]![YourFormName]![YourControlName].
Once you press converter button the VBA friendly SQL shows up in the VBA Ready control. Even if the new format does not look correct don’t worry about it. Just Copy it into a module and there is where you would see the magic happening.
You can change this:
CODE

SELECT tblOrderDetails.ProdCode, tblOrderDetails.MovieDesc, tblCustomerInformation.FirstName, tblCustomerInformation.LastName, tblOrderDetails.AuthorizedBy, tblOrderDetails.ReturnReason, tblOrderDetails.ActRetDate
FROM tblCustomerInformation INNER JOIN tblOrderDetails ON tblCustomerInformation.CustomerID = tblOrderDetails.CustomerID
WHERE (((tblOrderDetails.ActRetDate) =returneddate)) OR (tblOrderDetails.ProdCode=myID)

Into this at a click of a button:
CODE
Dim myid As Integer
Dim returneddate As Date
Dim strSQL As String
strSQL = "SELECT tblOrderDetails.ProdCode, tblOrderDetails.MovieDesc, tblCustomerInformation.FirstName, tblCustomerInformation.LastName, tblOrderDetails.AuthorizedBy, tblOrderDetails.ReturnReason, tblOrderDetails.ActRetDate"
strSQL = strSQL & " FROM tblCustomerInformation INNER JOIN tblOrderDetails ON tblCustomerInformation.CustomerID = tblOrderDetails.CustomerID"
strSQL = strSQL & " WHERE (((tblOrderDetails.ActRetDate) =#" & returneddate & "#)) OR (tblOrderDetails.ProdCode=" & myid & ")"

Tip: You can create your queries in Access then go to View.......SQL View, and copy the syntax and paste it into Jet SQL control on the mainform of the dowloadble DB.
This can go a long way in helping individuals convert SQL syntax to a VBA useable format much quicker..
Dallr
Attached File(s)
Attached File  SQL to VBA Converter.zip ( 108.92K ) Number of downloads: 859
 
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 22nd August 2014 - 10:32 PM