SQL VB Code Generator  ...by NewRAD

Support Page

 

System Requirements:

Make sure your Windows Language settings are set to English before using. You can change them easily in the Control Panel under Regional and Language Settings.

 

Using SQL VB Code Generator

Easy as 1-2-3.

1. First Open a Database

SQL VB Code Generator is layed out to show from left to right list boxes with your databases, tables, and fields, and then SQL commands and operators, etc. 

When running SQL VB Code Generator, it should automatically display a database list on the far left list box.  If you have any 32-Bit ODBC Data Source Names (DSN's) already set up on your PC then they should be in the list.  There are also ODBC Database Drivers in the list  that can be clicked on.  For example, you should see "Microsoft Access Database" in the list. If you click on that and then point to an MS Access database ".mdb" file, then it will display the tables for that database.  If you want to select an Access database for which there is not an ODBC DSN set up for already,

2. Click on stuff to create an SQL statement.

When you double-click at database, it displays the tables in that database. When you double-click a table, it displays the fields in that table.  From there, just start clicking on SQL commands, fields, tables, etc. until your query is built.  Or you can use some of the built-in SQL query generators for some of the repetitive simple queries like list records, or get a "group count by". 

3. Click on "Execute SQL" button

When you finish building your SQL statement in the text box, then click the "Execute SQL" button.

If it is an action query like "INSERT, UPDATE, or DELETE", then it will display the number of records affected in the grid below.

If it is a regular query like a SELECT or SELECT COUNT(*), it will display the recordset results, complete with field headings, in the grid. 

You can right-click on the grid to copy the selected cells to an Excel spreadsheet (columns are tab-delimited), or a text file (like with NotePad), with fixed-widths for each column, for displaying with a fixed-width font, like Courier.

 

Advanced Features

Copy SQL to VB Code

Now that you have created an SQL statement in the text box, you can hit the "Copy SQL to VB" button and it will automatically convert it to a VB string and copy it to the clipboard so it is all ready for pasting into the VB program you are creating. 

For example, if you create the following query:

SELECT State, Count(*)
FROM Publishers
GROUP BY State
ORDER BY Count(*) DESC

Then click on the "Copy SQL to VB" button and the following code:

strSQL = "SELECT State, Count(*) "
strSQL = strSQL & "FROM Publishers "
strSQL = strSQL & "GROUP BY State "
strSQL = strSQL & "ORDER BY Count(*) DESC "

...will be copied to the Windows clipboard ready for you to paste into your VB program.

 

...and yes, you can do reverse.

Paste From VB Code To SQL

Say you need to debug an SQL query inside your VB code.  If the SQL string inside your VB code is in the format below, simply copy that VB code to the clipboard.

strSQL = "SELECT State, Count(*) "
strSQL = strSQL & "FROM Publishers "
strSQL = strSQL & "GROUP BY State "
strSQL = strSQL & "ORDER BY Count(*) DESC "

Then click on the "Paste From VB Code To SQL" menu item under the Edit menu and the following code:

SELECT State, Count(*)
FROM Publishers
GROUP BY State
ORDER BY Count(*) DESC

...will be pasted into the SQL text box in the SQL VB Code Generator program.

Now you can modify and test the SQL string in the convenience of the SQL VB Code Generator environment.  When you have the SQL query debugged, simply click on the "Copy SQL to VB" button and it will convert it back to VB and copy it to the clipboard, ready for pasting into the VB program you are working on. 

This back and forth copy and paste ability can save you a ton of time.

 

SQL Generators

Currently these are only built-in SQL generators, but more will be added soon.

List Records - lists the first 100 records in the selected table.

SELECT TOP 100 *
FROM Publishers

Count - gives the record count in the selected table.

SELECT Count(*)
FROM Publishers

Count By - gives the record count grouped by the selected field on the selected table.

SELECT State, Count(*)
FROM Publishers
GROUP BY State
ORDER BY State

Count By Desc - gives the record count grouped by the selected field but displayed in descending order.

SELECT State, Count(*)
FROM Publishers
GROUP BY State
ORDER BY Count(*) DESC

 

VB Code Generators

Select - Creates a Select query to select a record from the selected table, based on the field selected, in this case the field PubID, and then fills a visual basic form with the results. The text boxes should have the same name as each field in the table but with a "txt" prefix.  For example: the data "Address" field in the database would get inserted into the in the text box named "txtAddress" on a VB form .           

            strSQL = "SELECT * FROM Publishers "
            strSQL = strSQL & "WHERE PubID = " & gPubID

            strSQL = strSQL & "ORDER BY PubID

            gRS.Open strSQL, gDb

            If Not gRS.EOF Then

                        gRS.MoveFirst

                        txtName = gRS("Name")

                        txtCompany Name = gRS("Company Name")

                        txtAddress = gRS("Address")

                        txtCity = gRS("City")

                        txtState = gRS("State")

                        txtZip = gRS("Zip")

                        txtTelephone = gRS("Telephone")

                        txtFax = gRS("Fax")

                        txtComments = gRS("Comments")

            End If

            Set gRS = Nothing

Insert - Creates an Insert query that inserts a record into the selected table and then fills the record with values from text boxes on a visual basic form.  

                strSQL = "INSERT INTO Publishers(Name, [Company Name], Address, City, State, "

                strSQL = strSQL & "VALUES ("

                strSQL = strSQL & "'" & Pad(txtName) & "', '" & Pad(txtCompany Name) & "', '" & Pad(txtAddress) & "', "

                strSQL = strSQL & "'" & Pad(txtCity) & "', '" & Pad(txtState) & "', '" & Pad(txtZip) & "', "

                strSQL = strSQL & "'" & Pad(txtTelephone) & "', '" & Pad(txtFax) & "', '" & Pad(txtComments) & "') "

                strSQL = StringReplace(strSQL, "'',", "Null,")

                strSQL = StringReplace(strSQL, "'')", "Null,")

                gDb.Execute strSQL

 

Update - Creates an Update query that updates a record in the selected table and then fills the record with values from text boxes on a visual basic form.  

                strSQL = "UPDATE Publishers SET "

                strSQL = strSQL & "Name = '" & Pad(txtName) & "', Company Name = '" & Pad(txtCompany Name) & "', "

                strSQL = strSQL & "Address = '" & Pad(txtAddress) & "', City = '" & Pad(txtCity) & "', "

                strSQL = strSQL & "State = '" & Pad(txtState) & "', Zip = '" & Pad(txtZip) & "', "

                strSQL = strSQL & "Telephone = '" & Pad(txtTelephone) & "', Fax = '" & Pad(txtFax) & "', "

                strSQL = strSQL & "Comments = '" & Pad(txtComments) & "' "

                strSQL = strSQL & "WHERE PubID = " & gPubID

                strSQL = StringReplace(strSQL, "'',", "Null,")

                strSQL = StringReplace(strSQL, "'' ", "Null ")

                gDb.Execute strSQL

 

Insert / Update creates code typically underneath a "Save" button on an Add/Edit VB Form.  If in "Add Mode" then it will do an Insert into the table.  If the user is in "Edit Mode" instead, then the code will Update the existing record based on the field selected, in this case "PubID".

                On Error GoTo ErrorSave

                OpenConnection App.Path & "\" & App.Title & ".mdb

                If gAddMode = True Then

                                strSQL = "INSERT INTO Publishers(Name, [Company Name], Address, City, State, "

                                strSQL = strSQL & "VALUES ("

                                strSQL = strSQL & "'" & Pad(txtName) & "', '" & Pad(txtCompany Name) & "', '" & Pad(txtAddress) & "', "

                                strSQL = strSQL & "'" & Pad(txtCity) & "', '" & Pad(txtState) & "', '" & Pad(txtZip) & "', "

                                strSQL = strSQL & "'" & Pad(txtTelephone) & "', '" & Pad(txtFax) & "', '" & Pad(txtComments) & "') "

                                strSQL = StringReplace(strSQL, "'',", "Null,")

                                strSQL = StringReplace(strSQL, "'')", "Null,")

                                gDb.Execute strSQL

                Else

                                strSQL = "UPDATE Publishers SET "

                                strSQL = strSQL & "Name = '" & Pad(txtName) & "', Company Name = '" & Pad(txtCompany Name) & "', "

                                strSQL = strSQL & "Address = '" & Pad(txtAddress) & "', City = '" & Pad(txtCity) & "', "

                                strSQL = strSQL & "State = '" & Pad(txtState) & "', Zip = '" & Pad(txtZip) & "', "

                                strSQL = strSQL & "Telephone = '" & Pad(txtTelephone) & "', Fax = '" & Pad(txtFax) & "', "

                                strSQL = strSQL & "Comments = '" & Pad(txtComments) & "' "

                                strSQL = strSQL & "WHERE PubID = " & gPubID

                                strSQL = StringReplace(strSQL, "'',", "Null,")

                                strSQL = StringReplace(strSQL, "'' ", "Null ")

                                gDb.Execute strSQL

                End If

                CloseDb

                frmList.FillGrid

                Unload Me

                Set frmAddEdit = Nothing

 

ResumeSave:

                Exit Sub:

 

ErrorSave:

                MsgBox "Error No: " & Err & " " & Error

                Resume ResumeSave

                Resume 0

 

FillGrid - Creates a Select query based on the table selected in the list box, and then creates the additional VB code needed to fill the grid with queries results.               

 

On Error GoTo ErrorFillGrid

                CenterForm Me

               

                OpenConnection App.Path & "\" & App.Title & ".mdb

               

                strSQL = "SELECT * FROM Publishers "

                strSQL = strSQL & "ORDER BY PubID

                gRS.Open strSQL, gDb

                Grid1.Cols = gRS.Fields.Count

                If Not gRS.EOF Then

                                gRS.MoveFirst

                                Grid1.Rows = gRS.RecordCount + 1

                                Grid1.Row = 1

                                Grid1.Col = 0

                                Grid1.RowSel = Grid1.Rows - 1

                                Grid1.ColSel = gRS.Fields.Count - 1

                                Grid1.Clip = StringReplace(gRS.GetString, Chr(13) & Chr(10), "")

                End If

               

                For N = 0 to gRS.Fields.Count - 1

                                Grid1.TextMatrix(0, N) = gRS.Fields(N).Name

                Next

               

                Grid1.ColWidth(0) = 800

                Grid1.ColWidth(1) = 4000

                Grid1.ColWidth(2) = 4000

                Grid1.ColWidth(3) = 4000

                Grid1.ColWidth(4) = 4000

                Grid1.ColWidth(5) = 4000

                Grid1.ColWidth(6) = 4000

                Grid1.ColWidth(7) = 4000

                Grid1.ColWidth(8) = 4000

                Grid1.ColWidth(9) = 4000

               

                For N = 0 To Grid1.Cols - 1

                                Grid1.ColAlignment(N) = flexAlignLeftCenter

                Next

               

                Grid1.AllowUserResizing = flexResizeColumns

               

                Set gRS = Nothing

                CloseDb

               

ResumeFillGrid:

                Exit Sub:

 

ErrorFillGrid:

                MsgBox "Error No: " & Err & " " & Error

                Resume ResumeFillGrid

                Resume 0

 

 


newrad.com