This appendix contains a step-by-step explanation of the creation of the SQL-VB5 Interpreter project. This program is already on the CD-ROM included with your copy of the book. The SQL-VB5 program in this lesson allows you to use an ASCII editor to create SQL scripts that SQL-VB5 can read and process. SQL-VB5 can handle fully commented, multiline SQL scripts. You'll find that SQL-VB5 is a very handy data management tool.
NOTE: You do not need to construct this project from scratch. It is already on the CD-ROM. However, you may want to go through this chapter as an added tutorial in constructing SQL-enabled applications in Visual Basic.
You may often need to quickly generate sample database layouts for a programming project. You may even need to build some test data to run through data editing or reporting routines. The SQL-VB5 program enables you to do all that. The SQL-VB5 program is able to read SQL scripts you create with the Windows Notepad application (or any other ASCII editor). Listing A.1 is a sample SQL script that can be processed by SQL-VB5.
// // load and read data tables // // open a database dbOpen C:\TYSDBVB5\SQLVB5\SCRIPTS\BOOKS5.MDB // open some tables to view SELECT * FROM Authors; SELECT * FROM Titles; SELECT * FROM Publishers; SELECT * FROM BookSales; SELECT * FROM Buyers; SELECT * FROM [Publisher Comments]; // // eof //
Listing A.1 opens a database and then displays several data tables on the screen.
This same script could perform any valid SQL statement and show the results on the
screen for the user to review or edit.
The advantage of generating database layouts using SQL-VB5 is that you have some documentation on the database structure that you can refer to in the future. You can also use SQL-VB5 to generate test SELECT queries and other SQL statements before you put them into your Visual Basic programs. Finally, SQL-VB5 is an excellent tool for exploring SQL and your databases.
Before jumping into code mode, let's lay out some general design parameters for the SQL-VB5 project. SQL-VB5 should be able to do the following:
That last item may be a surprise to some. Remember that Microsoft Access SQL has no keyword for opening, closing, or creating a database! You add your own script keywords to handle this.
To accomplish all this, you need three forms, three standard code modules, and one class module:
The SQLVBMain form needs some menu items and a CommonDialog control to handle the Open File dialog that runs the SQL scripts. The SQLVBChild form needs a Data control and a DBGrid control to handle the result sets. The SQLVBAbout needs a couple of Label controls and a single OK command button.
The SQLVBMOD code module needs three main routines and a host of supporting routines. The three main routines are
The SQLVBMOD needs an error routine; some special routines to handle the database OPEN, CLOSE, and CREATE commands; a routine to handle the SQL DML commands (SELECT_FROM); and a routine to handle the SQL DDL commands (CREATE TABLE, for example). You can add these as you go along.
The SQLVBGEN module needs routines to read the selected MDB and then write out the SQL-Visual Basic code that represents the tables, fields, indexes, and relationships defined in the MDB. It also has a handful of routines to handle script headers and footers, comment lines, and saving the finished script to the disk.
The SQLVBCLASS module has a single Sub Main() used to start the application.
The application class module has the properties and methods needed to
allow external VBA programs to access and run SQLVB methods. Other programs can create
their own instance of SQL-VB5 and then run all the primary commands. The
object model for SQL-VB5 is described in Figure A.1 and Table A.1.
Figure
A.1. The SQL-VB5 object model.
Table A.1. The SQL-VB5 object model.
| Object Type | Name | Description |
| Properties | DBName | Name of the MDB database to open. |
| Script | Name of the SQL-Visual Basic script to open. | |
| Methods | Start | Starts SQL-VB5 in interactive mode. Same as launching the EXE from Explorer. |
| CloseApp | Closes SQL-VB5 interactive session. | |
| Run | Runs the SQL-VB5 script found in the Script property. | |
| Generate | Generates an SQL-VB5 script from the MDB database found in DBName. | |
| Create | Starts SQL-VB5 text editor to create a new SQL-VB5 script using the name in the Script property. | |
| Edit | Starts the SQL-VB5 text editor to edit an existing script using the name in the Script property. |
NOTE: If you haven't already done so, start up Visual Basic 5.0 and prepare a new Standard EXE project.
The first thing you do is define the MDI form for the project. This form provides the interface to the Notepad editor for managing script files. It also enables users to run existing scripts to see the results. Because it is a multidocument interface, you need to add some menu options to enable users to arrange the forms within the workspace. Finally, you add access to an About box through the menu.
Add an MDI form to your project by selecting Project | Add MDI Form from the Visual Basic main menu. This form contains a few controls that allow the user to open an ASCII file to edit or run, arrange the various child forms open within the SQLVBMain MDI form, and show the About box upon request. Use Table A.2 and Figure A.2 as guides as you build the form.
NOTE: In Table A.2, be sure to place the common dialog, label, and command button controls directly "on" the picture controls. Visual Basic does not allow standard controls to be placed directly on an MDI form. You can, however, place Picture controls on an MDI form, and then place your standard controls on the Picture controls.
Figure
A.2. Laying out the SQLVBMain MDI form.
Table A.2. Visual Basic controls for the SQLVBMain MDI form.
| Control | Property | Setting |
| VB.MDIForm | Name | SQLVBMain |
| BackColor | &H8000000C& | |
| Caption | "SQL-VB Interpreter" | |
| ClientHeight | 4140 | |
| ClientLeft | 1065 | |
| ClientTop | 1800 | |
| ClientWidth | 5910 | |
| WindowState | 2 `Maximized | |
| VB.PictureBox | Name | Picture2 |
| Align | 2 `Align Bottom | |
| Height | 420 | |
| Left | 0 | |
| Top | 3720 | |
| Width | 5910 | |
| VB.Label | Name | lblProgress |
| BorderStyle | 1 `Fixed Single | |
| Height | 255 | |
| Left | 60 | |
| Top | 60 | |
| Width | 9375 | |
| VB.PictureBox | Name | Picture1 |
| Align | 1 `Align Top | |
| Height | 495 | |
| Left | 0 | |
| Top | 0 | |
| Width | 5910 | |
| MSComDlg.CommonDialog | Name | CommonDialog1 |
| Left | 4980 | |
| Top | 0 | |
| Threed.SSCommand | Name | cmdBtn |
| Height | 255 | |
| Index | 0 | |
| Left | 2160 | |
| Top | 0 | |
| Width | 255 | |
| RoundedCorners | 0 `False | |
| Threed.SSCommand | Name | cmdBtn |
| Height | 255 | |
| Index | 1 | |
| Left | 120 | |
| Top | 0 | |
| Width | 255 | |
| RoundedCorners | 0 `False | |
| Threed.SSCommand | Name | cmdBtn |
| Height | 255 | |
| Index | 2 | |
| Left | 1680 | |
| Top | 120 | |
| Width | 255 | |
| RoundedCorners | 0 `False | |
| Threed.SSCommand | Name | cmdBtn |
| Height | 255 | |
| Index | 3 | |
| Left | 480 | |
| Top | 120 | |
| Width | 255 | |
| RoundedCorners | 0 `False | |
| Threed.SSCommand | Name | cmdBtn |
| Height | 255 | |
| Index | 4 | |
| Left | 1080 | |
| Top | 0 | |
| Width | 255 | |
| RoundedCorners | 0 `False | |
| Threed.SSCommand | Name | cmdBtn |
| Height | 255 | |
| Index | 5 | |
| Left | 720 | |
| Top | 120 | |
| Width | 255 | |
| RoundedCorners | 0 `False |
| Property | Name | Setting |
| Menu | Name | mnuFile |
| Caption | "&File" | |
| Menu | Name | mnuFileNew |
| Caption | "&New..." | |
| Menu | Name | mnuFileEdit |
| Caption | "&Edit..." | |
| Menu | Name | mnuFileClose |
| Caption | "&Close" | |
| Menu | Name | mnuFileSpc03 |
| Caption | "-" | |
| Menu | Name | mnuFileAuto |
| Caption | "&Generate..." | |
| Menu | Name | mnuFileRun |
| Caption | "&Run..." | |
| Menu | Name | mnuFileSp01 |
| Caption | "-" | |
| Menu | Name | mnuFileExit |
| Caption | "E&xit" | |
| Menu | Name | mnuWindows |
| Caption | "&Windows" | |
| WindowList | -1 `True | |
| Menu | Name | mnuWindowsItem |
| Caption | "&Cascade" | |
| Index | 0 | |
| Menu | Name | mnuWindowsItem |
| Caption | "Tile &Horizontal" | |
| Index | 1 | |
| Menu | Name | mnuWindowsItem |
| Caption | "Tile &Vertical" | |
| Index | 2 | |
| Menu | Name | mnuWindowsItem |
| Caption | "&Arrange" | |
| Index | 3 | |
| Menu | Name | mnuHelp |
| Caption | "&Help" | |
| Menu | Name | mnuHelpAbout |
| Caption | "&About |
The final step in completing the SQLVBMain form is adding the Visual Basic code that activates the various menu options selected by the user. Because most of that code calls other routines you have not yet written, skip the Visual Basic code for now; you get back to it at the end of the project.
Before continuing with the lesson, save this form as SQLVBMAI.FRM and save the project as SQLVB5.VBP.
The SQLVBChild child form displays any result set created by SQL statements in
the script being processed. You need two controls on this form--a data control and
a data-bound grid control. Add a new form to your project by selecting Project |
Add Form from the Visual Basic main menu. Use Table A.4 and Figure A.3 as guides
for creating SQLVBChild.
Figure
A.3. Laying out the SQLVBChild form.
Table A.4. Visual Basic controls for the SQLVBChild child form.
| Control | Property | Setting |
| Form | Name | SQLVBChild |
| Height | 3690 | |
| Width | 7485 | |
| MDIChild | -1--True | |
| Data | Name | Data1 |
| Height | 300 | |
| Left | 120 | |
| Top | 2820 | |
| Width | 7095 | |
| Visible | False | |
| DBGrid | Name | DBGrid1 |
| Height | 2535 | |
| Left | 120 | |
| DataSource | Data1 | |
| Top | 120 | |
| Width | 7095 |
TIP: To add code to one of the form events, double-click any empty location of the form to bring up the Visual Basic code window for SQLVBChild. The first event you should see is the Form_Load event. You can use the drop-down list box on the right to locate other events for the form object.
Open the Visual Basic code window for the Form_Load event and add the Visual Basic program code in Listing A.2.
Private Sub Form_Load()
`
Me.Data1.Refresh
DoEvents
`
` set the captions
Me.Caption = Trim(strGlobalSelect)
Me.Data1.Caption = Trim(strGlobalSelect)
Me.DBGrid1.Caption = Trim(strGlobalSelect)
`
End Sub
The code in Listing A.2 first refreshes the data control and yields to let Windows
catch up with any pending messages, then sets the Caption properties.
The Form_Unload event contains a single line of code. This line clears up the main (SQLVBMain) form's menu display.
Private Sub Form_Unload(Cancel As Integer) ` SQLVBMain.mnuWindows.Visible = False ` End Sub
Next, add code to the Form_Activate event. This updates the MDI form menus and rebinds the data from the data control to the grid display.
Private Sub Form_Activate()
`
SQLVBMain.mnuWindows.Visible = True
DBGrid1.ReBind
`
End Sub
The last code piece needed for the SQLVBChild form is the code behind the Form_Resize event (see Listing A.3). This code automatically resizes the DBGrid and Data controls whenever the user resizes the form. Note the If test that occurs at the start of the routine. Whenever a form is minimized, the Form_Resize event occurs. Attempts to resize a minimized form result in Visual Basic errors, so check to make sure the form is not minimized before you continue with the routine.
Private Sub Form_Resize()
`
If Me.WindowState <> 1 Then
With DBGrid1
.Width = Me.ScaleWidth
.Left = 1
.Top = 1
.Height = Me.ScaleHeight
End With
End If
`
End Sub
After you have entered these pieces of code, save the form as SQLVBCHI.FRM.
It's a good idea to save the project at this time, too.
The last form you need for this project is the SQLVBAbout form. This is the form
that lists the name and version of the program and its authors, and so on. Use Table
A.5 and Figure A.4 as guides as you create this form for your project.
Figure
A.4. Laying out the SQLVBAbout form.
Table A.5. Visual Basic controls for the SQLVBAbout form.
| Control | Property | Setting |
| VB.Form | Name | frmAbout |
| BorderStyle | 3 `Fixed Dialog | |
| Caption | "About MyApp" | |
| ClientHeight | 3555 | |
| ClientLeft | 2340 | |
| ClientTop | 1935 | |
| ClientWidth | 5730 | |
| VB.CommandButton | Name | cmdOK |
| Cancel | -1 `True | |
| Caption | "OK" | |
| Default | -1 `True | |
| Height | 345 | |
| Left | 4245 | |
| Top | 2625 | |
| Width | 1260 | |
| VB.CommandButton | Name | cmdSysInfo |
| Caption | "&System Info..." | |
| Height | 345 | |
| Left | 4260 | |
| Top | 3075 | |
| Width | 1245 | |
| VB.Image | Name | Image1 |
| Height | 2173 | |
| Left | 120 | |
| Stretch | -1 `True | |
| Top | 180 | |
| Width | 2343 | |
| VB.Line | Name | Line1 |
| BorderColor | &H00808080& | |
| BorderStyle | 6 `Inside Solid | |
| Index | 1 | |
| X1 | 84.515 | |
| X2 | 5309.398 | |
| Y1 | 1687.583 | |
| Y2 | 1687.583 | |
| VB.Label | Name | lblDescription |
| Caption | "App Description" | |
| ForeColor | &H00000000& | |
| Height | 1170 | |
| Left | 2610 | |
| Top | 1125 | |
| Width | 2985 | |
| VB.Label | Name | lblTitle |
| Caption | "Application Title" | |
| Font | Name="MS Sans Serif" | |
| Size=12 | ||
| ForeColor | &H00000000& | |
| Height | 480 | |
| Left | 2640 | |
| Top | 240 | |
| Width | 2985 | |
| VB.Line | Name | Line1 |
| BorderColor | &H00FFFFFF& | |
| BorderWidth | 2 | |
| Index | 0 | |
| X1 | 98.6 | |
| X2 | 5309.398 | |
| Y1 | 1697.936 | |
| Y2 | 1697.936 | |
| VB.Label | Name | lblVersion |
| Caption | "Version" | |
| Height | 225 | |
| Left | 2640 | |
| Top | 780 | |
| Width | 2985 | |
| VB.Label | Name | lblDisclaimer |
| Caption | " WARNING: ..." |
|
| ForeColor | &H00000000& | |
| Height | 825 | |
| Left | 255 | |
| Top | 2625 | |
| Width | 3870 |
First, add the code from Listing A.4 to the general declarations section of the form.
Option Explicit
` Reg Key Security Options...
Const READ_CONTROL = &H20000
Const KEY_QUERY_VALUE = &H1
Const KEY_SET_VALUE = &H2
Const KEY_CREATE_SUB_KEY = &H4
Const KEY_ENUMERATE_SUB_KEYS = &H8
Const KEY_NOTIFY = &H10
Const KEY_CREATE_LINK = &H20
Const KEY_ALL_ACCESS = KEY_QUERY_VALUE + KEY_SET_VALUE + _
KEY_CREATE_SUB_KEY + KEY_ENUMERATE_SUB_KEYS + _
KEY_NOTIFY + KEY_CREATE_LINK + READ_CONTROL
` Reg Key ROOT Types...
Const HKEY_LOCAL_MACHINE = &H80000002
Const ERROR_SUCCESS = 0
Const REG_SZ = 1 ` Unicode nul terminated string
Const REG_DWORD = 4 ` 32-bit number
Const gREGKEYSYSINFOLOC = "SOFTWARE\Microsoft\Shared Tools Location"
Const gREGVALSYSINFOLOC = "MSINFO"
Const gREGKEYSYSINFO = "SOFTWARE\Microsoft\Shared Tools\MSINFO"
Const gREGVALSYSINFO = "PATH"
Private Declare Function RegOpenKeyEx Lib "advapi32" Alias "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, ByVal samDesired As Long, ByRef phkResult As Long) As Long
Private Declare Function RegQueryValueEx Lib "advapi32" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, ByRef lpType As Long, ByVal lpData As String, ByRef lpcbData As Long) As Long
Private Declare Function RegCloseKey Lib "advapi32" (ByVal hKey As Long) As Long
Note that you can place API declarations directly in a form as long as you precede
these declaration lines with the Private keyword.
Next, create a new method called GetKeyValue to use these API calls and constants. Enter the code from Listing A.5 into the project.
Public Function GetKeyValue(KeyRoot As Long, KeyName As String, SubKeyRef As String, ByRef KeyVal As String) As Boolean
Dim i As Long ` Loop Counter
Dim rc As Long ` Return Code
Dim hKey As Long ` Handle To An Open ÂRegistry Key
Dim hDepth As Long `
Dim KeyValType As Long ` Data Type Of A ÂRegistry Key
Dim tmpVal As String ` Tempory Storage ÂFor A Registry Key Value
Dim KeyValSize As Long ` Size Of Registry ÂKey Variable
`------------------------------------------------------------
` Open RegKey Under KeyRoot {HKEY_LOCAL_MACHINE...}
`------------------------------------------------------------
rc = RegOpenKeyEx(KeyRoot, KeyName, 0, KEY_ALL_ACCESS, hKey) ` Open Registry ÂKey
If (rc <> ERROR_SUCCESS) Then GoTo GetKeyError ` Handle Error...
tmpVal = String$(1024, 0) ` Allocate Variable ÂSpace
KeyValSize = 1024 ` Mark Variable Size
`------------------------------------------------------------
` Retrieve Registry Key Value...
`------------------------------------------------------------
rc = RegQueryValueEx(hKey, SubKeyRef, 0, _
KeyValType, tmpVal, KeyValSize) ` Get/Create Key ÂValue
If (rc <> ERROR_SUCCESS) Then GoTo GetKeyError ` Handle Errors
If (Asc(Mid(tmpVal, KeyValSize, 1)) = 0) Then ` Win95 Adds Null ÂTerminated String...
tmpVal = Left(tmpVal, KeyValSize - 1) ` Null Found, Extract From String
Else ` WinNT Does NOT ÂNull Terminate String...
tmpVal = Left(tmpVal, KeyValSize) ` Null Not Found, ÂExtract String Only
End If
`------------------------------------------------------------
` Determine Key Value Type For Conversion...
`------------------------------------------------------------
Select Case KeyValType ` Search Data ÂTypes...
Case REG_SZ ` String Registry ÂKey Data Type
KeyVal = tmpVal ` Copy String Value
Case REG_DWORD ` Double Word ÂRegistry Key Data Type
For i = Len(tmpVal) To 1 Step -1 ` Convert Each Bit
KeyVal = KeyVal + Hex(Asc(Mid(tmpVal, i, 1))) ` Build Value Char. ÂBy Char.
Next
KeyVal = Format$("&h" + KeyVal) ` Convert Double ÂWord To String
End Select
GetKeyValue = True ` Return Success
rc = RegCloseKey(hKey) ` Close Registry Key
Exit Function ` Exit
GetKeyError: ` Cleanup After An Error Has Occured...
KeyVal = "" ` Set Return Val To ÂEmpty String
GetKeyValue = False ` Return Failure
rc = RegCloseKey(hKey) ` Close Registry Key
End Function
Now add one more new routine to call the GetKeyValue method. Add StartSysInfo
to your project and enter the code in Listing A.6.
Public Sub StartSysInfo()
On Error GoTo SysInfoErr
Dim rc As Long
Dim SysInfoPath As String
` Try To Get System Info Program Path\Name From Registry...
If GetKeyValue(HKEY_LOCAL_MACHINE, gREGKEYSYSINFO, gREGVALSYSINFO, SysInfoPath) Then
` Try To Get System Info Program Path Only From Registry...
ElseIf GetKeyValue(HKEY_LOCAL_MACHINE, gREGKEYSYSINFOLOC, gREGVALSYSINFOLOC, SysInfoPath) Then
` Validate Existance Of Known 32 Bit File Version
If (Dir(SysInfoPath & "\MSINFO32.EXE") <> "") Then
SysInfoPath = SysInfoPath & "\MSINFO32.EXE"
` Error - File Can Not Be Found...
Else
GoTo SysInfoErr
End If
` Error - Registry Entry Can Not Be Found...
Else
GoTo SysInfoErr
End If
Call Shell(SysInfoPath, vbNormalFocus)
Exit Sub
SysInfoErr:
MsgBox "System Information Is Unavailable At This Time", vbOKOnly
End Sub
Finally, Listing A.7 shows the code for the Form_Load event and the two
command button click events. Add this to your form.
Private Sub cmdSysInfo_Click()
Call StartSysInfo
End Sub
Private Sub cmdOK_Click()
Unload Me
End Sub
Private Sub Form_Load()
`
Me.Caption = "About " & App.Title
lblVersion.Caption = "Version " & App.Major & "." & App.Minor & "." & ÂApp.Revision
lblTitle.Caption = App.Title
lblDescription = App.FileDescription
Image1.Picture = SQLVBMain.Icon
Me.Icon = SQLVBMain.Icon
lblDisclaimer = ""
`
End Sub
Now save the form as SQLVBABO.FRM and save the project.
Now that you have created all three forms, you can go back to SQLVBMain and add the code behind the menu options. This is also the time when you add code that calls the Windows Notepad program from within SQLVB.
First, add the code from Listing A.8. This arranges the command button bar on the top of the form.
Public Sub LoadCmdBtns()
`
Dim Top As Integer
Dim Left As Integer
Dim Width As Integer
Dim Height As Integer
`
Top = 45
Height = Picture1.Height * 0.75
Width = Height * 1.1
`
For x = 0 To 5
cmdBtn(x).Top = Top
cmdBtn(x).Left = (x * Width) + 45
cmdBtn(x).Width = Width * 0.9
cmdBtn(x).Height = Height
cmdBtn(x).BevelWidth = 2
cmdBtn(x).RoundedCorners = False
Next
`
cmdBtn(0).Picture = LoadPicture(App.Path & "\pics\new.bmp")
cmdBtn(1).Picture = LoadPicture(App.Path & "\pics\open.bmp")
cmdBtn(2).Picture = LoadPicture(App.Path & "\pics\save.bmp")
cmdBtn(3).Picture = LoadPicture(App.Path & "\pics\sum.bmp")
cmdBtn(4).Picture = LoadPicture(App.Path & "\pics\camera.bmp")
cmdBtn(5).Picture = LoadPicture(App.Path & "\pics\undo.bmp")
`
cmdBtn(0).ToolTipText = "New"
cmdBtn(1).ToolTipText = "Edit"
cmdBtn(2).ToolTipText = "Close"
cmdBtn(3).ToolTipText = "Generate"
cmdBtn(4).ToolTipText = "Run"
cmdBtn(5).ToolTipText = "Exit"
`
End Sub
Note that this routine sets the picture, tool tip, size, and actual position of each
of the command bar buttons.
Now add the following code to the Form_Load and Form_Unload events.
Private Sub MDIForm_Load()
LoadCmdBtns ` set up buttons
End Sub
Private Sub MDIForm_Resize()
LoadCmdBtns
lblProgress.Width = Me.ScaleWidth * 0.98
End Sub
Next, add the code from Listing A.9 to the cmdBtns_Click event. This handles all the command button selections.
Private Sub cmdBtn_Click(Index As Integer)
`
Select Case Index
Case Is = 0
mnuFileNew_Click
Case Is = 1
mnuFileEdit_Click
Case Is = 2
mnuFileClose_Click
Case Is = 3
mnuFileAuto_Click
Case Is = 4
mnuFileRun_Click
Case Is = 5
mnuFileExit_Click
End Select
`
End Sub
Next, add the code for the File | Close menu selection.
Private Sub mnuFileClose_Click()
`
On Error Resume Next
SQLFileClose
InitApp
`
End Sub
Add code behind the Help menu option that shows off the SQLVBAbout form. To open the code window for the About menu option, select Help | About. When the code window pops up, insert the following code:
Private Sub mnuHelpAbout_Click()
frmAbout.Show vbModal
End Sub
Now add code that gives the user the ability to control the multiple child forms within the SQLVBMain MDI form. Select Windows | Cascade and insert the following code:
Private Sub mnuWindowsItem_Click(Index As Integer)
`
Me.Arrange Index
`
End Sub
The Arrange method requires a single parameter. This value determines whether the windows are cascaded, tiled, arranged as icons, and so on. Because you built the menu as a control array (with indexes), the Index parameter passed to this menu tells Visual Basic which operation was requested. All you need to do is call the method and pass the parameter.
Now add the code behind the File | Exit menu option. This code safely closes down all open child windows before exiting to the operating system.
Private Sub mnuFileExit_Click() Unload Me End Sub
Select File | Run and add the following code line. Notice that the code line starts with the comment character. This tells Visual Basic to treat this line as a comment, not as executable code. You have it "commented out" right now because you haven't created the SQLMain routine yet. You do that in the next section when you create the SQL-VB5 Main code module.
Private Sub mnuFileRun_Click() SQLMain ` call main job w/o parm End Sub
Now add the following code to the File | Generate menu item. This calls the routines that generate a new script from an existing MDB.
Private Sub mnuFileAuto_Click()
AutoGen ` call routine to read MDB and create SQV
End Sub
The following two segments of code should be added behind the File | New and File | Edit menu options. The code calls a routine that you build in the SQL-VB5 Main module, so you have commented out the calls for now to prevent Visual Basic from reporting an error at compile time.
Private Sub mnuFileEdit_Click() LoadNotePadFile "Edit Existing SQLVB File"
End Sub
Private Sub mnuFileNew_Click() LoadNotePadFile "Create New SQLVB File" End Sub
Now that all the code is added, save this form and save the project. As a test, you can run the project. You can't do much except view the About box and exit, but you can check for compile errors.
The SQLVBMOD code module contains the major portion of the system. It's here that you add the routines that can read and execute the SQL statements found in the ASCII file. You also add routines to handle any errors that occur along the way. Even though this module does a lot, you have only slightly more than 10 routines to define before you complete the project.
First, you need to declare a set of variables to be used throughout the entire project. These variables contain information about the script being processed, any forms that are open, and so forth. Add a module to the project by selecting Project | Add Module from the Visual Basic main menu. Set its Name property to SQLVBMOD and enter the lines in Listing A.10 into the declarations section. The meaning and use of these variables becomes clearer as you build the various routines within the module.
` ` general declarations ` Global strSQLFile As String Global intGlobalErr As Integer Global intSQLFlag As Integer Global intDBFlag As Integer Global intSQLFileHandle As Integer Global strSQLLine As String Global intLine As Integer Global strLine As String Global strGlobalSelect As String Global strGlobalDBName As String Global db As Database Global ws As Workspace Global intForms As Integer Global TblForms() As Form Global strConnect As String Global strVersion As String Global blnSQLQuiet As Boolean
The top-most routine in this module is the SQLMain routine. This routine has only three tasks: open the script file, process the script commands, and close the script file. Let's write a module that does all that. To add a new procedure to the module, select Tools | Add Procedure from the Visual Basic main menu. Enter SQLMain(cRunFile) as the name, select the Sub radio button, and select the Public radio button. Now enter the code in Listing A.11.
Sub SQLMain(Optional cRunFile As Variant)
`
` main loop for interpreting SQL ASCII file
`
If IsMissing(cRunFile) = True Then
cRunFile = ""
End If
`
InitApp ` clean up environment
SQLFileOpen CStr(cRunFile) ` open the script
If intGlobalErr = False Then
SQLFileProcess ` process the script
End If
SQLFileClose ` close the script
`
cRunFile = "" ` clear passed parm
`If intGlobalErr = False Then
` MsgBox "Script Completed", vbInformation
`End If
End Sub
The routine in Listing A.11 does all the things mentioned earlier and adds two more
actions. You perform some application initialization. You set an error condition
during the SQLFileOpen routine in case something goes wrong when you open
the file. Then you can check that error condition before you try to run the SQLFileProcess
routine. Also, once the script processing is complete, you show the user a friendly
little message box.
Let's start building the next level of routines. The first is the SQLFileOpen routine. Use the CommonDialog control to get the filename from the user. If a filename was selected, open that file for processing, and then return to SQLMain. Notice that you have built in an error trap to catch any problems that may occur during file selection and opening.
Select Tools | Add Procedure from the Visual Basic main menu and set the name to SQLFileOpen(cSQLFile). Make this a Public Sub procedure. Now enter the code in Listing A.12 in the procedure window.
Sub SQLFileOpen(strSQLFile As String)
`
` open the SQV script file
`
On Error GoTo SQLFileOpenErr
`
If Len(Trim(strSQLFile)) = 0 Then
SQLVBMain.CommonDialog1.DialogTitle = "Load SQLVB File"
SQLVBMain.CommonDialog1.DefaultExt = "SQV"
SQLVBMain.CommonDialog1.Filter = "SQLVB File|*.SQV"
SQLVBMain.CommonDialog1.ShowOpen
strSQLFile = SQLVBMain.CommonDialog1.filename
End If
`
If Len(Trim(strSQLFile)) = 0 Then
intGlobalErr = True
intSQLFlag = False
GoTo SQLFileOpenExit
End If
`
intSQLFileHandle = FreeFile(0)
Open strSQLFile For Input As intSQLFileHandle
intGlobalErr = False
intSQLFlag = True
GoTo SQLFileOpenExit
`
SQLFileOpenErr:
If Err <> 32755 Then
ErrMsg Err, Error$, intLine, strSQLFile, "SQLFileOpen"
End If
InitApp
intGlobalErr = True
`
SQLFileOpenExit:
`
End Sub
Let's skip over the SQLProcess routine and write the SQLFileClose routine next. The only task this routine has to complete is to safely close the script file upon completion. Create a Public Sub procedure called SQLFileClose and enter the code in Listing A.13.
Sub SQLFileClose()
`
` close the SQV text file
`
On Error GoTo SQLFileCloseErr
`
If intGlobalErr = False Then
If intSQLFileHandle <> 0 Then
Close (intSQLFileHandle)
End If
intSQLFlag = False
End If
GoTo SQLFileCloseExit
`
SQLFileCloseErr:
ErrMsg Err, Error$, intLine, strLine, "SQLFileClose"
InitApp
`
SQLFileCloseExit:
`
End Sub
Now you get to the heart of the program--SQLFileProcess. This routine reads each line of the script file and performs whatever processing is necessary to build and execute the SQL statements in the script. You also add a few lines that show the user the script lines as they are processed. Also, remember that the script file has regular SQL statements, special database CONNECT, VERSION, QUIET, OPEN, CREATE, and CLOSE words, and comments. This processing routine has to handle each of these differently. Of course, you need an error handler, too.
Create a Public Sub procedure called SQLFileProcess and enter the code in Listing A.14. Don't be discouraged by the length of this piece of code--it won't take you long to enter it into the project.
Sub SQLFileProcess()
`
` main loop for processing ASCII file lines
`
On Error GoTo SQLFileProcessErr
`
Dim cToken As String
`
If intSQLFlag = False Then
GoTo SQLFileProcessExit
End If
`
strSQLLine = ""
While Not EOF(intSQLFileHandle)
If intGlobalErr = True Then
GoTo SQLFileProcessExit
End If
`
Line Input #intSQLFileHandle, strLine
intLine = intLine + 1
strLine = Trim(strLine) + " "
If Len(strLine) <> 0 Then
cToken = GetToken(strLine)
If Right(cToken, 1) = ";" Then
cToken = Left(cToken, Len(cToken) - 1)
End If
`
SQLVBMain.lblProgress.Caption = strLine
DoEvents
Select Case UCase(cToken)
Case Is = "//"
` no action - comment line
Case Is = "DBCONNECT"
SQLdbConnect
Case Is = "DBVERSION"
SQLdbVersion
Case Is = "DBOPEN"
SQLdbOpen
Case Is = "DBMAKE"
SQLdbMake
Case Is = "DBCLOSE"
SQLdbClose
Case Is = "DBQUIET"
SQLQuietFlag
Case Else
strSQLLine = strSQLLine + strLine
If Right(strLine, 2) = "; " Then
SQLDoCommand
strSQLLine = ""
End If
End Select
End If
Wend
GoTo SQLFileProcessExit
`
SQLFileProcessErr:
ErrMsg Err, Error$, intLine, strLine, "SQLFileProcess"
InitApp
`
SQLFileProcessExit:
`
End Sub
Several things are happening in Listing A.14. Let's review the routine more closely.
After setting up the error trap and initializing variables, the main While..Wend
loop starts. This loop reads a line from the script file opened by SQLFileOpen,
updates a line counter, removes any trailing or leading spaces from the line, and
then adds a single space at the end of the line. This single space is added to help
the GetToken function do its work.
The SQL-VB5 program processes each line of script word by word. The first word in each command line is used to determine how SQL-VB5 processes the line. The GetToken function returns the first word in the line (you learn more about GetToken a bit later). Next, you show the current script line to the user by updating SQLVBMain.lblProgress. Notice that you added the DoEvents command right after updating the label. This forces your program to pause a moment, and that allows Windows time to send the message that ultimately updates the SQLVBMain form.
Once the main form is updated, the program must handle the word it pulled from
the script line. Usually, the word is an SQL keyword and SQL-VB5 can add
it to the cSQLLine variable for eventual processing. However, there are
several words that require special handling. These words are listed in Table A.6
along with comments about how they are handled.
Table A.6. Script words that require special handling.
| Script Word | Handling Comments |
| // | This is the comment word. If a line begins with this keyword, ignore the rest of the line and get the next line in the script. You must leave at least one space between the // and the comment. For example, //comment would be rejected by SQL-VB5, but // comment is just fine. |
| DBCONNECT | This is the keyword that you can use to set the CONNECT property of the database. This allows you to open non-Microsoft Jet databases or even ODBC data sources. Consult the Microsoft Visual Basic documentation for valid syntax here. Basically, anything that works in Visual Basic's Connect property works here, too. |
| DBVERSION | This is the value that sets the version of database to be created. Valid values are 1.1, 2.0, and 3.0. |
| DBOPEN | This is the OpenDatabase word. If a line starts with this keyword, call a special routine (SQLdbOpen) that executes a Visual Basic OpenDatabase operation. |
| DBMAKE | This is the CreateDatabase word. If a line starts with this keyword, call a special routine (SQLdbMake) that executes a Visual Basic CreateDatabase operation. |
| DBQUIET | Use this keyword to suppress the display of SQL-VB5 when you call it from other programs (using the object model). If this value is set to True, no screens appear; only the script is processed. |
| DBCLOSE | This is the CloseDatabase word. If a line begins with this keyword, call a special routine (SQLdbClose) that executes a Visual Basic Close operation on a database object. |
This process is repeated until the program reaches the end of the script file. At that time, the routine exits SQLFileProcess and returns to the SQLMain routine.
Now would be a good time to save the SQLVBMOD code module and save the project. You can't run the program at this point because you added references to several routines that do not yet exist. You add those final routines in the next section.
Now that you have entered all the main routines, you need to add several support routines. Almost all these support routines are called directly from SQLFileProcess. You concentrate on those first and add others as needed.
The first routine called from SQLFileProcess is GetToken. This routine takes a line of script and returns the first word in the list. You use this word (often referred to as a token) as a way to determine how SQLFileProcess handles each line of script. Because GetToken returns a value, it is a function. To create a Visual Basic function, select Insert | Procedure. Enter the function name as GetToken(cString As String) As String and select the Function radio button. Now enter the code in Listing A.15 in the code window.
Function GetToken(cString As String) As String
`
` get a token from the input line
`
Dim intTemp As Integer
`
intTemp = InStr(cString, " ")
If intTemp > 0 Then
GetToken = Left(cString, intTemp - 1)
Else
GetToken = ""
End If
`
End Function
The comments in the code explain things pretty well. You use the Visual Basic InStr
function to locate the first occurrence of a space within the script line, and then
use that position to grab a copy of the first word in the line. If you can't find
a word, you return an empty string.
Now let's add the three "setup" values you can use in your scripts: DBCONNECT, DBVERSION and DBQUIET. These three keywords do not actually execute any real actions, but they do set values used by the other action words DBOPEN and DBMAKE.
First, add the code for the DBCONNECT keyword from Listing A.16. This code just accepts the Connect string from the script and saves it to an internal variable.
Public Sub SQLdbConnect()
`
` set global connect property
`
strConnect = strSQLLine
`
End Sub
Now, add the code from Listing A.17 to handle the DBVERSION keyword.
Public Sub SQLdbVersion()
`
` set global version value
`
Dim strTemp As String
`
strTemp = GetToken(strSQLLine)
`
Select Case UCase(strTemp)
Case "1.0"
strVersion = dbVersion10
Case "1.1"
strVersion = dbVersion11
Case "2.0"
strVersion = dbVersion20
Case "3.0"
strVersion = dbVersion30
End Select
`
End Sub
Next, add the code for the DBQUIET keyword. This sets a value that can suppress
form displays. This is handy for performing script runs where you do not want to
see any GUI display. Enter the code from Listing A.18.
Public Sub SQLQuietFlag()
`
Dim strTemp As String
`
strTemp = GetToken(strSQLLine)
`
If UCase(strTemp) = "YES" Then
blnSQLQuiet = True
Else
blnSQLQuiet = False
End If
`
End Sub
The next three routines you add handle the DBOPEN, DBMAKE, and
DBCLOSE script words. These are all non-SQL commands that you need in order
to open, create, and close Microsoft Access Jet databases. The first one you add
is the routine that handles opening a Microsoft Access Jet database. Use the Visual
Basic menu to create a Public Sub routine named SQLdbOpen and enter
the code in Listing A.19.
Sub SQLdbOpen()
`
` open an existing database
`
On Error GoTo SQldbOpenErr
`
Dim strOpen As String
Dim intTemp As Integer
`
strLine = Trim(strLine) ` drop any spaces
intTemp = InStr(strLine, " ") ` locate first embedded space
strOpen = Mid(strLine, intTemp + 1, 255) ` get rest of line
`
` if line ends w/ ";", dump it!
If Right(strOpen, 1) = ";" Then
strOpen = Left(strOpen, Len(strOpen) - 1)
End If
`
` now try to open database
Set ws = DBEngine.CreateWorkspace("wsSQLVB", "admin", "")
Set db = ws.OpenDatabase(strOpen, False, False, strConnect)
strGlobalDBName = strOpen
intDBFlag = True
GoTo SQldbOpenExit
`
SQldbOpenErr:
ErrMsg Err, Error$, intLine, strLine, "SQldbOpen"
InitApp
`
SQldbOpenExit:
`
End Sub
Listing A.19 performs three tasks. First, it strips the DBOPEN keyword off
the script line. Second, if a semicolon (;) appears at the end of the line, the routine
drops it. What's left is the valid database filename in the variable cOpen.
The routine then attempts to open this file using the stored Connect string. Once
that's done, the routine returns to SQLFileProcess.
The next routine to add handles the DBCLOSE command. This is a simple routine. Its only job is to close the Microsoft Access Jet database. This routine also closes any open child forms and clears flag variables. Create a Public Sub called SQLdbClose and add the code in Listing A.20.
Sub SQLdbClose()
`
` close open database
`
On Error Resume Next ` ignore errors here
`
db.Close
`
For x = 0 To intForms
Unload TblForms(x)
Next x
`
intForms = 0
intDBFlag = False
`
End Sub
The final routine to handle special commands is the routine that processes the DBMAKE
keyword to create new Microsoft Access Jet databases. This one works much like the
DBOPEN routine except that there are a few additional chores when creating
a new file. Create a Public Sub called SQLdbMake and enter the
code in Listing A.21.
Sub SQLdbMake()
`
` make a new database
`
On Error GoTo SQLdbMakeErr
`
Dim strMake As String
Dim intTemp As Integer
`
strLine = Trim(strLine) ` drop any spaces
intTemp = InStr(strLine, " ") ` locate first embedded space
strMake = Mid(strLine, intTemp + 1, 255) ` get rest of line
`
` if line ends w/ ";", dump it!
If Right(strMake, 1) = ";" Then
strMake = Left(strMake, Len(strMake) - 1)
End If
`
` try to open it (to see if it already exists)
nSQLMakeHandle = FreeFile(0)
Open strMake For Input As nSQLMakeHandle
Close nSQLMakeHandle
`
nResult = MsgBox("ERASE [" + strMake + "]", vbYesNo + vbQuestion, "Database ÂAlready Exists!")
If nResult = vbYes Then
Kill strMake
Else
ErrMsg 0, "Script Cancel - database already Exists", intLine, strLine, Â"SQLdbMake"
InitApp
End If
`
` now try to make a new database
SQLdbMake2:
` create a new db, close it, then open for use
Set ws = DBEngine.CreateWorkspace("wsSQLVB2", "admin", "")
Set db = ws.CreateDatabase(strMake, dbLangGeneral, strVersion)
db.Close
Set db = ws.OpenDatabase(strMake, False, False, strConnect)
strGlobalDBName = strMake
intDBFlag = True
GoTo SQLdbMakeExit
`
SQLdbMakeErr:
If Err = 53 Then
Resume SQLdbMake2
Else
ErrMsg Err, Error$, intLine, strLine, "SQLdbMake"
InitApp
End If
`
SQLdbMakeExit:
`
End Sub
A few things in this routine deserve attention. First, the routine drops the first
word from the script line (the DBMAKE word). Then it strips the semicolon
off the end of the line, if necessary. Then, instead of performing the create operation,
the routine first tries to open the file. This is done to see if it already exists.
If it does, you can issue a warning before you clobber that multimegabyte database
that the user has been nursing for the last few months. If no error occurs when you
try to open the file, the routine sends out a message warning the user and asking
if it's okay to erase the existing file. If the answer is Yes, the file is erased.
If the answer is No, a message is displayed, and script processing is halted.
Now, if an error occurs during the attempt to open the file, you know that the file does not exist. The local error handler is invoked and the first thing it checks is whether the error was caused by an attempt to open a nonexistent file. If so, the error handler sends the routine to the file creation point without comment. If the error has another cause, the global error handler is called and the program is halted.
Finally, after all the file creation stuff is sorted out, the routine executes the Visual Basic CreateDatabase operation and returns to the SQLFileProcess routine. Notice that you declared two parameters during the CreateDatabase operation. The first parameter (vbLangGeneral) tells Visual Basic to use the general rules for sorting and collating data. The second parameter (strVersion) can be set by the user with the DBVERSION keyword.
The last routine called from SQLFileProcess handles the execution of SQL statements. Create a Public Sub called SQLDoCommand and enter the code in Listing A.22.
Sub SQLFileProcess()
`
` main loop for processing ASCII file lines
`
On Error GoTo SQLFileProcessErr
`
Dim cToken As String
`
If intSQLFlag = False Then
GoTo SQLFileProcessExit
End If
`
strSQLLine = ""
While Not EOF(intSQLFileHandle)
If intGlobalErr = True Then
GoTo SQLFileProcessExit
End If
`
Line Input #intSQLFileHandle, strLine
intLine = intLine + 1
strLine = Trim(strLine) + " "
If Len(strLine) <> 0 Then
cToken = GetToken(strLine)
If Right(cToken, 1) = ";" Then
cToken = Left(cToken, Len(cToken) - 1)
End If
`
SQLVBMain.lblProgress.Caption = strLine
DoEvents
Select Case UCase(cToken)
Case Is = "//"
` no action - comment line
Case Is = "DBCONNECT"
SQLdbConnect
Case Is = "DBVERSION"
SQLdbVersion
Case Is = "DBOPEN"
SQLdbOpen
Case Is = "DBMAKE"
SQLdbMake
Case Is = "DBCLOSE"
SQLdbClose
Case Is = "DBQUIET"
SQLQuietFlag
Case Else
strSQLLine = strSQLLine + strLine
If Right(strLine, 2) = "; " Then
SQLDoCommand
strSQLLine = ""
End If
End Select
End If
Wend
GoTo SQLFileProcessExit
`
SQLFileProcessErr:
ErrMsg Err, Error$, intLine, strLine, "SQLFileProcess"
InitApp
`
SQLFileProcessExit:
`
End Sub
Sub SQLDoCommand()
`
` handle SQL Command
`
On Error GoTo SQLDoCommandErr ` set error trap
`
Dim cTemp As String ` holds token
`
` skip errors if you're deleting objects
cTemp = GetToken(Trim(strSQLLine)) ` get first word
Select Case UCase(cTemp)
Case Is = "DELETE" ` don't report error
On Error Resume Next
Case Is = "DROP" ` don't report error
On Error Resume Next
Case Is = "ALTER" ` don't report error
On Error Resume Next
End Select
`
` check for queries that return a view
Select Case UCase(cTemp)
Case Is = "TRANSFORM"
ShowTable strSQLLine, strGlobalDBName ` show view form
Case Is = "SELECT"
If InStr(UCase(strSQLLine), " INTO ") <> 0 Then
ws.BeginTrans
db.Execute strSQLLine, dbSeeChanges + dbFailOnError ` execute make-Âtable SQL
ws.CommitTrans
db.Close
Set db = Nothing
Set db = ws.OpenDatabase(strGlobalDBName, False, False, strConnect)
Else
db.Close
Set db = Nothing
ShowTable strSQLLine, strGlobalDBName ` show view form
Set db = ws.OpenDatabase(strGlobalDBName, False, False, strConnect)
End If
Case Else
ws.BeginTrans
db.Execute strSQLLine, dbSeeChanges + dbFailOnError ` execute SQL
ws.CommitTrans
db.Close
Set db = Nothing
Set db = ws.OpenDatabase(strGlobalDBName, False, False, strConnect)
End Select
GoTo SQlDoCommandExit ` exit routine
`
` local error handler
SQLDoCommandErr:
ErrMsg Err, Error$, intLine, strLine, "SQLDoCommand"
On Error Resume Next
ws.Rollback
InitApp
`
` routine exit
SQlDoCommandExit:
`
End Sub
Even though it looks as though several things take place in this routine, only three
tasks are being handled by SQLDoCommand. First, you get the first word in
the script line, and then you have to make a couple of decisions on how to properly
execute the SQL statement.
If the first word is DELETE, DROP, or ALTER, you turn off the local error handler. This is done for convenience. You want to be able to create scripts that can use the SQL words DELETE, DROP, and ALTER to remove table objects from the database. Because the objects may not exist, you could get errors that can halt the script processing. To make life simple, SQL-VB5 ignores these errors. Once you write a few SQL-VB5 scripts, you'll appreciate this feature.
Next, you have to check for the SQL keywords that can return result sets. These are TRANSFORM and SELECT. These keywords should be handled differently from SQL statements that do not return result sets. If you see TRANSFORM, you call the ShowTable routine to load and display the SQLVBChild child form on the screen. If you see SELECT, you make one additional check. If the line contains the INTO keyword, you have an SQL statement that creates a new table. Using the INTO keyword means that the SELECT statement does not return a result set. If there is no INTO in the SQL statement, you hand the statement off to the ShowTable routine. If the line starts with any other SQL keyword, you simply execute the command using the Visual Basic Execute method on the database.
The SQLDoCommand routine calls the ShowTable routine, so you need to add that routine to the project. This is a simple routine that updates some variables, creates a new instance of the SQLVBChild child form, and shows the new form. Create a Public Sub called ShowTable and enter the code in Listing A.23.
Sub ShowTable(cSQL As String, strDB As String)
`
` show a selected table
`
strGlobalSelect = strSQLLine
strGlobalDBName = strDB
`
intForms = intForms + 1
ReDim Preserve TblForms(intForms) As Form
Set TblForms(intForms) = New SQLVBChild
Load TblForms(intForms)
TblForms(intForms).Caption = CStr(intForms)
TblForms(intForms).Data1.DatabaseName = strGlobalDBName
TblForms(intForms).Data1.RecordSource = strGlobalSelect
TblForms(intForms).Data1.Refresh
TblForms(intForms).Show
TblForms(intForms).WindowState = vbMinimized
TblForms(intForms).WindowState = vbNormal
`
End Sub
The only fancy stuff in this module involves the creation of new Form objects. Remember
that you created a global array called TblForms in the declaration section of the
module? This routine increases the size of the array by 1 each time it is invoked.
Also, this routine uses the Visual Basic SET command to create a new instance
of the SQLVBChild child form. This new instance is a copy of SQLVBChild that has
its own "life" once it is created and loaded. By making copies in this
way, you can create multiple, independent versions of the SQLVBChild form to display
various datasets.
You need to add another support routine. This one handles the loading of the scripts into the Windows Notepad for editing. This is called from the SQLVBMain MDI form. Create a Public Sub called LoadNotePadFile(cLoadMsg As String) and enter the code in Listing A.24.
Sub LoadNotePadFile(cLoadMsg As String, Optional strScript As String)
`
` load notepad as an editor
`
On Error GoTo LoadNotePadFileErr:
`
Dim cEditFile As String
Dim nAppID As Long
`
If IsMissing(strScript) = False And strScript <> "" Then
cEditFile = strScript
Else
SQLVBMain.CommonDialog1.DialogTitle = cLoadMsg
SQLVBMain.CommonDialog1.DefaultExt = "SQV"
SQLVBMain.CommonDialog1.Filter = "SQLVB File|*.SQV"
SQLVBMain.CommonDialog1.ShowOpen
cEditFile = SQLVBMain.CommonDialog1.filename
End If
`
If Len(cEditFile) <> 0 Then
nAppID = Shell("NotePad " + cEditFile, 1)
AppActivate (nAppID)
End If
GoTo LoadNotePadFileExit
`
LoadNotePadFileErr:
ErrMsg Err, Error$, 0, cEditFile, "LoadNotePadFile"
InitApp
`
LoadNotePadFileExit:
`
End Sub
Most of this code should look familiar. The first part of the routine in Listing
A.24 sets up and activates the CommonDialog object to allow the user to
select an existing file or create a new file. Once this is done, the routine forces
Windows to load a new instance of the Notepad application, and then gives that application
the focus. Now the user sees the Notepad application (with the selected file loaded,
too!). The SQL-VB5 application resumes processing once it gains the focus
again.
The next routine you need to add to SQLVBMOD is the global error handler. This routine (shown in Listing A.25) simply displays the error messages and waits for the user to click the OK button before it returns to the calling routine. Create a Public Sub called ErrMsg and enter the code in Listing A.25.
Sub ErrMsg(nErr As Integer, cError As String, intLine As Integer, strLine As ÂString, cModule As String)
`
` report an error to user
`
Dim cMsg As String
`
cMsg = "ErrNo:" + Chr(9) + Str(nErr) + Chr(13)
cMsg = cMsg + "ErrMsg: " + Chr(9) + cError + Chr(13)
cMsg = cMsg + "LineNo:" + Chr(9) + Str(intLine) + Chr(13)
cMsg = cMsg + "Text: " + Chr(9) + strLine
`
If blnSQLQuiet = False Then
MsgBox cMsg, vbCritical, cModule
End If
`
End Sub
No real magic in this routine. Listing A.25 is passed the Visual Basic error number
and error message, the script line number and script line text, and the name of the
SQL-VB5 routine that experienced the error. All this is formatted into a
readable (if not entirely welcome) message that is displayed to the user. Notice
that you used the tabs (Chr(9)) and carriage returns (Chr(13))
to make the information easier to read.
The routine in Listing A.26 handles all the initialization chores for the start of a script. It is also called whenever an error is reported and when the program is exited. Create a Public Sub procedure called InitApp and enter the code in Listing A.26.
Sub InitApp()
`
` set up app values
`
On Error Resume Next ` ignore any errors here
`
` close all child forms
For x = 0 To intForms
Unload TblForms(x)
Set TblForms(x) = Nothing
Next x
`
` close open database
If intDBFlag = True Then
db.Close
Set db = Nothing
Set ws = Nothing
End If
`
` close open script file
If intSQLFlag = True Then
Close (intSQLFileHandle)
End If
`
` reset flags & stuff
nSQLFile = ""
intSQLFlag = False
intDBFlag = False
nGlobalErr = False
blnSQLQuiet = False
intLine = 0
strConnect = "" ` "Access;"
strVersion = dbVersion30
`
End Sub
There is one final routine you need to add to the project to make SQL-VB5 start. It's the sub Main method. This starts the entire application, instead of the SQLVBMain form. Add another code module to your project and set its Name property to SQLVBClass. Now add the following code to the module:
Public Sub Main()
`
Dim strCmd As String
strCmd = Command$
`
` ignore system commands
If Left(strCmd, 1) = "-" Then
Exit Sub
End If
`
` see if user passed a file
If Trim(strCmd) <> "" Then
SQLMain strCmd
Else
SQLVBMain.Show
End If
`
End Sub
This routine allows you to send a script file on the command line when you start the program. First, SQL-VB5 checks to see if the first character of the command is a hyphen (-). This designates a system command and is ignored by SQL-VB5. For example, when you start SQL-VB5 from the OLE request, the -Embedded command is passed to SQL-VB5 by the operating system. This lets you write special code that executes the first time someone attempts to reference the application.
Next, if there is a valid command on the line, it is passed to the script processor for immediate attention. Otherwise, the MDI form is shown to the user.
Now you need to change the Startup property of the project. Select Project | Properties and set the Startup Form to Sub Main. Save the project before continuing.
WARNING: It is very important that you set the Startup Form to Sub Main. If you forget to do this, your code runs just fine as a standalone application, but does not accept command-line parameters and cannot run as an OLE Server object.
That's the last routine in the SQLVBMOD code module. Save this module and save the project before you continue. In the next section, you add the code that reads an existing MDB and generates a valid SQL-Visual Basic script.
Because you have the entire Microsoft DAO at your disposal, it is possible to read an existing MDB and generate a valid SQL-Visual Basic script that can be used to re-create the database structure. This can be done by walking through all the collections of table, index, relationship, and field objects. In this section you create the code that does just that.
First, add a new BAS module to your project. Set its Name property to SQLVBGEN. Then add the code from Listing A.27 to the general declarations section of the module.
Option Explicit Dim cDBName As String Dim cWrLine As String Dim cWrFile As String Dim objDB As Database Dim nWrFile As Integer Dim EOL As String Dim INDENT As Integer
These are the only module-level variables you need for this section.
The top-level routine (called AutoGen) is used to create the entire script. It accepts two parameters: the MDB name and the SQL script name. Add the code in Listing A.28 to your module.
Public Sub AutoGen(Optional cDB As Variant, Optional cSQV As Variant)
`
` ====================================================
` inputs:
` cDB database name to read
` cSQV script name to write
`
` outputs:
` produces script file
`
` processing:
` Ask for database to load and file to save results.
` Enum all tables, fields, indexes and relations
` ====================================================
`
` some local vars
EOL = Chr(13) + Chr(10)
INDENT = 3
intGlobalErr = False
`
` handle missing parms
If IsMissing(cDB) = True Then
cDB = ""
End If
If IsMissing(cSQV) = True Then
cSQV = ""
End If
`
` we can run silent
If cDB <> "" And cSQV <> "" Then
SQLVBMain.Visible = False
End If
`
AutoGenDBOpen cDB ` get mdb file
`
If intGlobalErr = False Then
AutoGenSQVOpen cSQV ` get sqv file
End If
`
If intGlobalErr = False Then
CreateScript ` create script
End If
`
` let'em know it's done
If intGlobalErr = False Then
If SQLVBMain.Visible = True Then
MsgBox cWrFile + " Script Created", vbInformation
End If
End If
`
` clean up
Close (nWrFile)
nWrFile = 0
cWrFile = ""
cDBName = ""
cDB = ""
cSQV = ""
intGlobalErr = False ` reset
`
End Sub
Most of the code is self-explanatory. Notice that there is logic to determine whether
the user should see any visual displays. This is here because you may want to run
this generator as part of the OLE object model and do not need to see the SQLVBMain
form.
Next, add the AutoGenDBOpen routine from Listing A.29. This opens the MDB.
Public Sub AutoGenDBOpen(Optional cDB As Variant)
On Error GoTo AutoGenDBOpenErr
`
If IsMissing(cDB) = True Or Len(cDB) = 0 Then
SQLVBMain.CommonDialog1.DialogTitle = "Load MDB File"
SQLVBMain.CommonDialog1.DefaultExt = "MDB"
SQLVBMain.CommonDialog1.Filter = "MS Jet Database File|*.MDB"
SQLVBMain.CommonDialog1.Flags = cdlOFNFileMustExist
SQLVBMain.CommonDialog1.CancelError = True
SQLVBMain.CommonDialog1.ShowOpen
cDBName = SQLVBMain.CommonDialog1.filename
Else
cDBName = cDB
End If
`
If Len(cDBName) = 0 Then
intGlobalErr = True
Else
cDBName = Trim(cDBName)
Set objDB = OpenDatabase(cDBName)
End If
GoTo AutoGenDBOpenExit
`
AutoGenDBOpenErr:
If Err <> 32755 Then
ErrMsg Err, Error$, intLine, strLine, "AutoGenDBOpen"
End If
intGlobalErr = True
`
AutoGenDBOpenExit:
End Sub
Now add the AutoGenSQVOpen subroutine from Listing A.30. Notice that, if no name is supplied for the script, SQL-VB5 invents one based on the MDB name.
Public Sub AutoGenSQVOpen(Optional cSQV As Variant)
On Error GoTo AutoGenSQVOpenErr
`
Dim intTemp As Integer
`
If IsMissing(cSQV) = True Or Len(cSQV) = 0 Then
cWrFile = cDBName
Else
cWrFile = cSQV
End If
`
intTemp = InStr(cWrFile, ".")
If intTemp > 0 Then
cWrFile = Mid(cWrFile, 1, intTemp - 1)
End If
cWrFile = cWrFile + ".sqv"
`
If IsMissing(cSQV) = True Or Len(cSQV) = 0 Then
SQLVBMain.CommonDialog1.DialogTitle = "Create SQV File"
SQLVBMain.CommonDialog1.DefaultExt = "SQV"
SQLVBMain.CommonDialog1.Filter = "SQV Script File|*.SQV"
SQLVBMain.CommonDialog1.filename = cWrFile
SQLVBMain.CommonDialog1.Flags = cdlOFNCreatePrompt Or ÂcdlOFNOverwritePrompt
SQLVBMain.CommonDialog1.CancelError = True
SQLVBMain.CommonDialog1.ShowOpen
cWrFile = SQLVBMain.CommonDialog1.filename
End If
`
If Len(cWrFile) = 0 Then
intGlobalErr = True
Else
nWrFile = FreeFile
Open cWrFile For Output As nWrFile
End If
GoTo AutoGenSQVOpenExit
`
AutoGenSQVOpenErr:
If Err <> 32755 Then
ErrMsg Err, Error$, intLine, strLine, "AutoGenDBOpen"
End If
intGlobalErr = True
`
AutoGenSQVOpenExit:
`
End Sub
Now you're ready for the fun stuff. The next method is the one that actually creates the script. Add the code from Listing A.31 to the module.
Public Sub CreateScript()
`
` walk through database objects and produce text file
`
On Error Resume Next
`
Dim objTableDef As TableDef
Dim objIndex As Index
Dim objRelation As Relation
`
` add script header
cWrLine = WrScriptHeader(cDBName)
Print #nWrFile, cWrLine
`
` add database create code
cWrLine = WrCreateDB(cDBName)
Print #nWrFile, cWrLine
`
` add table create code
For Each objTableDef In objDB.TableDefs
cWrLine = WrCreateTable(objTableDef)
If Len(cWrLine) > 0 Then
Print #nWrFile, cWrLine
End If
`
For Each objIndex In objTableDef.Indexes
cWrLine = WrCreateIndex(objIndex, objTableDef.Name)
If Len(cWrLine) > 0 Then
Print #nWrFile, cWrLine
End If
Next
Next
`
` add relation create code
For Each objRelation In objDB.Relations
cWrLine = wrCreateRelation(objRelation)
If Len(cWrLine) > 0 Then
Print #nWrFile, cWrLine
End If
Next
`
` add script footer
cWrLine = WrScriptFooter
Print #nWrFile, cWrLine
`
SQLVBMain.lblProgress = ""
End Sub
Although it looks a bit long, CreateScript is actually a pretty simple routine.
First, it creates a script header, then the database, then all the tables, then the
relationships, and finally, it writes a script footer. Simple, right? Now let's get
into the subprocesses called from this method.
You need to add some code comments at the start and end of all your scripts. Add the WrScriptHeader and WrScriptFooter routines from Listing A.32.
Public Function WrScriptHeader(cMDB) As String
`
` write header for text file
`
Dim cTemp As String
`
cTemp = "// " + String(60, "*") + EOL
cTemp = cTemp + "// DATABASE CREATE SCRIPT FOR [" + cMDB + "]" + EOL
cTemp = cTemp + "// " + String(60, "*") + EOL
cTemp = cTemp + "// SQLGEN Version 5.0 - 03/97(MCA)" + EOL
cTemp = cTemp + "// " + EOL
cTemp = cTemp + "// For use with SQL-VB Interpreter" + EOL
cTemp = cTemp + "//" + EOL
cTemp = cTemp + "// CREATED: " + Format(Date, "General Date") + EOL
cTemp = cTemp + "//" + EOL
cTemp = cTemp + "// " + String(60, "*") + EOL
`
WrScriptHeader = cTemp
`
End Function
Public Function WrScriptFooter()
`
` create end of script footer
`
Dim cTemp As String
`
cTemp = "// " + String(60, "*") + EOL
cTemp = cTemp + "// " + "END OF FILE" + EOL
cTemp = cTemp + "// " + String(60, "*") + EOL
`
WrScriptFooter = cTemp
`
End Function
Next, add the wrCreateDB method from Listing A.33.
Public Function WrCreateDB(cMDB) As String
`
` write the create db line
`
Dim cTemp As String
`
SQLVBMain.lblProgress = "Creating Script Header..."
DoEvents
`
cTemp = EOL
cTemp = cTemp + "// Create new database" + EOL
cTemp = cTemp + "dbMake " + cDBName + ";"
cTemp = cTemp + EOL
`
WrCreateDB = cTemp
`
End Function
The next step is to create each table definition. This is actually a two-step process. First, you need to create the table and its fields. Then you need to create the indexes that belong to the table. The code in Listing A.34 creates the table itself and calls the function to create the field definitions.
Public Function WrCreateTable(tblObject As TableDef) As String
`
` create a write table SQL statement
`
Dim cTemp As String
Dim cTable As String
Dim objField As Field
`
` ignore system tables
cTable = tblObject.Name
If UCase(Left(cTable, 4)) = "MSYS" Then
WrCreateTable = ""
Exit Function
End If
`
` ignore non-native MDB tables
If tblObject.Attributes <> 0 Then
WrCreateTable = ""
Exit Function
End If
`
SQLVBMain.lblProgress = "Creating Tables..."
DoEvents
`
` if it contains spaces, enclose in braces
If InStr(cTable, " ") <> 0 Then
cTable = "[" + cTable + "]"
End If
`
` start SQL line
cTemp = "// Create " + cTable + EOL
cTemp = cTemp + "CREATE TABLE " + cTable + EOL
cTemp = cTemp + Space(INDENT) + "(" + EOL
`
` add each field
For Each objField In tblObject.Fields
cTemp = cTemp + Space(INDENT) + WrCreateField(objField)
cTemp = cTemp + "," + EOL
Next
`
` fix up end of line
If Right(cTemp, Len(EOL) + 1) = "," + EOL Then
cTemp = Left(cTemp, Len(cTemp) - (Len(EOL) + 1)) ` strip last EOL and Âcomma
End If
cTemp = cTemp + EOL
cTemp = cTemp + Space(INDENT) + ");" + EOL ` add final paren and semi-colon
`
WrCreateTable = cTemp ` return result to caller
End Function
Note that the WrCreateTable method skips any tables that start with "MSYS"
or that have their attributes set. This eliminates all non-Jet and all Microsoft
system tables from the definition set.
Now build the WrCreateField routine from Listing A.35.
Public Function WrCreateField(fldObject As Field) As String
`
` create a field line
`
Dim cTemp As String
`
` get field from collection
cTemp = fldObject.Name
`
` if it has a space, enclose in braces
If InStr(cTemp, " ") <> 0 Then
cTemp = "[" + cTemp + "]"
End If
`
` add a spacer
cTemp = cTemp + " "
`
` add the field type
cTemp = cTemp + FieldTypeName(fldObject)
`
` if it's a text field, add the length
If fldObject.Type = dbText Then
cTemp = cTemp + "(" + CStr(fldObject.Size) + ")"
End If
`
` return results
WrCreateField = cTemp
`
End Function
This routine calls the FieldTypeName function to convert the integer type
value into a printable data type name. Add the FieldTypeName function from
Listing A.36 to your project.
Public Function FieldTypeName(fldObject As Field) As String
`
` get field type value
` return field string name
`
Select Case fldObject.Type
Case Is = dbDate
FieldTypeName = "DATE"
Case Is = dbText
FieldTypeName = "TEXT"
Case Is = dbMemo
FieldTypeName = "MEMO"
Case Is = dbBoolean
FieldTypeName = "BOOLEAN"
Case Is = dbInteger
FieldTypeName = "INTEGER"
Case Is = dbLong
FieldTypeName = "LONG"
Case Is = dbCurrency
FieldTypeName = "CURRENCY"
Case Is = dbSingle
FieldTypeName = "SINGLE"
Case Is = dbDouble
FieldTypeName = "DOUBLE"
Case Is = dbByte
FieldTypeName = "BYTE"
Case Is = dbLongBinary
FieldTypeName = "LONGBINARY"
Case Else
FieldTypeName = "UNKNOWN"
End Select
`
If fldObject.Attributes And dbAutoIncrField Then
FieldTypeName = "COUNTER"
End If
End Function
Now that all the field work is done, it's time to build the indexes. Listing A.37 shows the code for the WrCreateIndex method.
Public Function WrCreateIndex(idxObject As Index, cTable As String) As String
`
` create index code line
`
Dim cTemp As String
Dim cIndex As String
Dim fldObject As Field
`
` ignore system table indexes
If UCase(Left(cTable, 4)) = "MSYS" Then
WrCreateIndex = ""
Exit Function
End If
`
If InStr(cTable, " ") <> 0 And Left(cTable, 1) <> "[" Then
cTable = "[" + cTable + "]"
End If
`
` ignore indexes for foreign keys
If idxObject.Foreign = True Then
WrCreateIndex = ""
Exit Function
End If
`
` start SQL line
cTemp = "// Index " + idxObject.Name + EOL
If idxObject.Unique = True Then
cTemp = cTemp + "CREATE UNIQUE INDEX "
Else
cTemp = cTemp + "CREATE INDEX "
End If
`
` if name has spaces, add braces
cIndex = idxObject.Name
If InStr(cIndex, " ") <> 0 Then
cIndex = "[" + cIndex + "]"
End If
cTemp = cTemp + cIndex
`
` prepare to add fields
cTemp = cTemp + " ON " + cTable + EOL
cTemp = cTemp + Space(INDENT) + "("
`
` get each field in index
For Each fldObject In idxObject.Fields
cTemp = cTemp + fldObject.Name
If fldObject.Attributes = dbDescending Then
cTemp = cTemp + " DESC"
Else
cTemp = cTemp + " ASC"
End If
cTemp = cTemp + ","
Next
`
` fix up end of field list
If Right(cTemp, 1) = "," Then
cTemp = Left(cTemp, Len(cTemp) - 1)
End If
cTemp = cTemp + ")"
`
` handle index attributes
If idxObject.Primary = True Then
cTemp = cTemp + " WITH PRIMARY" ` primary key
End If
`
If idxObject.IgnoreNulls = True Then
cTemp = cTemp + " WITH IGNORE NULL" ` ignore nulls
End If
If idxObject.Required = True And idxObject.Primary = False Then
cTemp = cTemp + " WITH DISALLOW NULL" ` disallow nulls
End If
`
` last bit here!
cTemp = cTemp + ";" + EOL
`
WrCreateIndex = cTemp
`
End Function
The code for the WrCreateIndex method is a bit involved. First, this routine
ignores any index that starts with "MSYS." This is assumed to be one of
the Microsoft reserved system indexes. Next, all indexes are built to support foreign
keys. They are handled later in the Relationships collection. Next, the Fields collection
is built (including the ascending/descending attributes) and added to the definition.
Finally, the attribute bits are checked for things like primary key, ignore nulls,
and other settings.
Now it's time to add the wrCreateRelation routine. This builds any relationship definitions into the script. Add the code from Listing A.38 to your module.
Public Function wrCreateRelation(relObject As Relation) As String
`
` create relation/constraint code
`
Dim cTemp As String
Dim fldObject As Field
Dim cTable As String
Dim cForgTable As String
Dim cFields() As String
Dim x As Integer
Dim z As Integer
`
cTable = relObject.Table
cForgTable = relObject.ForeignTable
`
` ignore system tables
If UCase(Left(cTable, 4)) = "MSYS" Then
wrCreateRelation = ""
Exit Function
End If
If UCase(Left(cForgTable, 4)) = "MSYS" Then
wrCreateRelation = ""
Exit Function
End If
`
SQLVBMain.lblProgress = "Creating Relations..."
DoEvents
`
` if it has spaces, add braces
If InStr(cTable, " ") <> 0 Then
cTable = "[" + cTable + "]"
End If
If InStr(cForgTable, " ") <> 0 Then
cForgTable = "[" + cForgTable + "]"
End If
`
` create array of field name/foriegn names
x = 0
For Each fldObject In relObject.Fields
ReDim Preserve cFields(x + 1, 2) As String
cFields(x, 1) = fldObject.Name
cFields(x, 2) = fldObject.ForeignName
x = x + 1
Next
`
cTemp = "// create relation " + relObject.Name + EOL
cTemp = cTemp + "ALTER TABLE " + cForgTable
cTemp = cTemp + " ADD CONSTRAINT " + relObject.Name + EOL
`
` write out local table fields
cTemp = cTemp + Space(INDENT) + "FOREIGN KEY ("
For z = 0 To x - 1
cTemp = cTemp + cFields(z, 2) + ","
Next
cTemp = Left(cTemp, Len(cTemp) - 1) + ") "
`
` write out foreign table/fields
cTemp = cTemp + "REFERENCES " + cTable + "("
For z = 0 To x - 1
cTemp = cTemp + cFields(z, 1) + ","
Next
cTemp = Left(cTemp, Len(cTemp) - 1) + ");" + EOL
`
wrCreateRelation = cTemp
`
End Function
This is much like the index routine. Any definition that has "MSYS" as
part of either table (left or right) is ignored. Notice that the Fields collection
must be traversed for relation objects, too.
That's the end of the generate portion of the script. Next you add the object interface for use as an OLE Server.
All you need to do now is compile the program as an executable and you're done. But first, let's run a test script through the system to make sure all is working properly. You run this test by starting SQL-VB5 from within Visual Basic. If all goes well, you create a final compiled version that runs faster.
Go ahead and run the application. The first test script is called SQLVB01.SQV.
You can find it in the ChapXA\Scripts folder on the CD. This script contains
a set of lines that open a database and then create several result sets to display.
Before you run the first script, you should load it for editing and make sure the
drive letter and path are correct for your desktop setup. To load the SQL script
file, select File | Edit. This brings up the Open File dialog. Locate the SQLVB01.SQV
script file in the TYSDBVB\SQLVB directory on your machine (see Figure A.5).
Figure
A.5. Testing the SQLVB01.SQV
script.
When the file is loaded into Notepad, inspect the script line that opens the database
file. Make sure the path and drive letters match your desktop setup (see Figure A.6).
Make any changes needed and exit Notepad. Make sure you save the script if you made any updates. Now you are ready to run the script.
To run the script, select File | Run and use the File Open dialog box to locate
the SQLVB01.SQV script file. Once you select the file, the program automatically
begins processing the script. The line at the bottom of the screen shows the script
lines as they are processed. The SQLVB01.SQV script opens a database and
creates six result set forms. Figure A.7 shows these six forms after they have been
rearranged on the screen.
Figure
A.6. Editing the script.
Figure
A.7. Viewing the results of SQLVB01.SQV.
If you have problems with the script, review the SQLVB01.SQV file for errors.
You may also have to review the Visual Basic code to check for program errors. If
the script ran without errors, you can check out other aspects of the program, including
the Windows menu and resizing the grid forms.
When you are sure that the program is working properly, you can continue with the next section of the chapter.
Now you're ready to add an object model wrapper to the SQL-VB5 Interpreter. All you need to do is add a class module to the project, define some methods and properties, and you're all set.
First, add a class module to the project (Project | Add Class Module) and set its Name to Application. Now you're ready to add properties and methods to the class.
You need two properties: DBName and Script. Listing A.40 shows the Property Let and Property Get code for both items. Be sure to add the Private variables in the general declaration section, too.
Option Explicit
Private strScript As String
Private strDBName As String
Public Property Get Script() As Variant
Script = strScript
End Property
Public Property Let Script(ByVal vNewValue As Variant)
strScript = vNewValue
End Property
Public Property Get DBName() As Variant
DBName = strDBName
End Property
Public Property Let DBName(ByVal vNewValue As Variant)
strDBName = vNewValue
End Property
Now that you have some properties built, you're ready to add some methods to the model. You can execute these methods from within other Visual Basic-compliant applications.
First add the Start and CloseApp methods. You can use these to start an instance of SQL-VB5 and close it when you're done (see Listing A.41).
Public Sub Start()
`
` starts interactive mode
`
SQLVBMain.Show
`
End Sub
Public Sub CloseApp()
`
` end instance of application
`
Dim frmTemp As Form
`
For Each frmTemp In Forms
Unload frmTemp
Next
End
`
End Sub
The Run method can be used to run SQL-VB5 against the name in the
Script property.
Public Sub Run()
`
` calls Run routine
`
If Trim(strScript) <> "" Then
SQLMain strScript
Else
SQLMain
End If
`
End Sub
The Generate method can read the MDB in the DBName property and create a script with the name in the Script property.
Public Sub Generate()
`
` calls autogen routine
`
If Trim(strDBName) <> "" And Trim(strScript) <> "" Then
AutoGen strDBName, strScript
Else
AutoGen
End If
`
End Sub
You can also add the Edit and Create methods to allow external programs to start the SQL-VB5 editor to edit or build new scripts.
Public Sub Edit()
`
` call edit routine
`
If Trim(strScript) <> "" Then
LoadNotePadFile "", strScript
Else
LoadNotePadFile "Select Script to Edit"
End If
`
End Sub
Public Sub Create()
`
` calls new file routine
`
If Trim(strScript) <> "" Then
LoadNotePadFile "", strScript
Else
LoadNotePadFile "Enter Name of New Script"
End If
`
End Sub
Finally, what interface would be complete if it didn't allow external calls to the About box?
Public Sub About()
`
` calls about box
`
frmAbout.Show vbModal
`
End Sub
That's it for the object model. Save and compile the program. In the next step you build a quick Visual Basic application to test the object model.
Start a new Visual Basic 5.0 Standard EXE, and add a single control array of five
command buttons to the project. Refer to Figure A.8 for captions, size, and location
of the buttons.
Figure
A.8. A test form for the SQL-VB5
object model.
To create an instance of SQL-VB5, you need an object variable and you need
to set that variable to reference the application class of SQL-VB5. To do
this, first add a form-level variable to the general declaration section of the form.
Option Explicit ` Dim objSQLVB5 As Object
Next, you need to add the following code to the Form_Load event. This code makes the object variable refer to the application class of your compiled SQL-VB5.
Private Sub Form_Load()
`
Set objSQLVB5 = CreateObject("SQLVB5.Application")
`
End Sub
Now you need to add code to the command1_Click event of the form. This contains all the code you need to test the SQL-VB5 object model. Enter the code in Listing A.42 into the Command1_Click event.
Private Sub Command1_Click(Index As Integer)
`
Select Case Index
Case 0 ` start
objSQLVB5.Start
Case 1 ` run
objSQLVB5.Run
Case 2 ` about
objSQLVB5.About
Case 3 ` edit
objSQLVB5.Script = "c:\tysdbvb5\source\chapxa\scripts\sqltest.sqv"
objSQLVB5.Edit
Case 4 ` generate
objSQLVB5.DBName = "c:\tysdbvb5\source\data\books5.mdb"
objSQLVB5.Script = "c:\tysdbvb5\source\chapxa\scripts\books5.sqv"
objSQLVB5.Generate
End Select
`
End Sub
Save and run the project. You can now test the various buttons and see how SQL-VB5
responds to your external application.
You now have a very valuable tool to add to your database programming tool kit. You can use SQL-VB5 to generate database layouts for all your projects in the future. You can also use SQL-VB5 to test data integrity options, load test data into existing tables, and even create simple data backup and replication scripts.
You could even add more options to the project. Here are some additional features that you might want to consider: