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(*) DESCThen 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 PublishersCount - gives the record count in the selected table.
SELECT Count(*)
FROM PublishersCount By - gives the record count grouped by the selected field on the selected table.
SELECT State, Count(*)
FROM Publishers
GROUP BY State
ORDER BY StateCount 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 = " & gPubIDstrSQL = 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