SQL to VBA Formatter
Apr 28 2006, 10:28 AM
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.
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:
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:
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..
SQL to VBA Converter.zip ( 108.92K ) Number of downloads: 859
|Search · Go to Top · Lo-Fi Version||Time is now: 22nd August 2014 - 10:32 PM|