Appendix A

The SQL-VB5 Project

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.

The Benefits of SQL-VB5

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.

Listing A.1. A sample SQL script.

//
// 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.

Designing SQL-VB5

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.


You need one other set of tools to meet the design criteria--the ability to edit scripts from within SQL-VB5. Instead of trying to create your own editor, we show you how you can include the Windows Notepad program as part of your Visual Basic project. This can be done with minimal effort, and it is a great way to take advantage of the software already available on users' desktops. This is perfectly legal as long as you do not provide users with a copy of the NOTEPAD.EXE program. Because all Windows systems have this program already, you're all set.

Creating the SQL-VB5 Forms

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.

SQLVBMain Form

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


Now that you have created the form, you need to add the menu. Table A.3 shows the hierarchy of the menu items you need for the SQLVBMain form.

Table A.3. Menu tree for the SQLVBMain MDI form.
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
" As you build the menu, you need to set two additional properties of the &Windows menu item. Set the Index property to zero and set the WindowList property to True. This forces Visual Basic to create a dynamic list of all the child forms open under the SQLVBMain MDI form window.

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.

Creating the SQLVBChild Child Form

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


You need to add code in four locations within the SQLVBChild form: the Form_Load event, the Form_Activate event, the Form_Unload event, and the Form_Resize event.

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.

Listing A.2. Adding code to the Form_Load event.

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.

Listing A.3. Adding code to the Form_Resize event.

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.

Creating the SQLVBAbout Form

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


This version of the About box offers the user the ability to gather system information. This is done through a set of API calls that read and collate keys from the Registry. You need to add some APIs and code to perform the Registry lookups.

First, add the code from Listing A.4 to the general declarations section of the form.

Listing A.4. Coding the declarations for the SQLVBAbout 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.

Listing A.5. Coding the GetKeyValue function.

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.

Listing A.6. Coding the StartSysInfo subroutine.

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.

Listing A.7. Adding code to the cmdSysInfo_Click event.

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.

Adding the SQLVBMain Code

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.

Listing A.8. Coding the LoadCmdBtns subroutine.

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.

Listing A.9. Adding code to the cmdBtn_Click event.

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.

Creating the SQL-VB5 Main Module

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.

Declaring Global Variables

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.

Listing A.10. Adding the global variables.

`
` 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

Creating SQLMain

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.

Listing A.11. Coding the SQLMain routine.

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.

Creating SQLFileOpen

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.

Listing A.12. Coding the SQLFileOpen routine.

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

Creating SQLFileClose

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.

Listing A.13. Coding the SQLFileClose routine.

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

Creating SQLFileProcess

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.

Listing A.14. Coding the SQLFileProcess routine.

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.


If the word found at the start of the line is not one of those listed in Table A.6, the program assumes that it is a valid SQL word and adds the entire line to the variable cSQLLine. After doing this, the routine checks to see whether the current line ends with a semicolon (;). If so, the program attempts to execute the SQL statement using the SQLDoCommand routine. After executing this routine, the cSQLLine variable is cleared in preparation for the next SQL statement.

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.

Creating the Support Routines

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.

Listing A.15. Coding the GetToken routine.

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.

Listing A.16. Coding the SQLDBConnect method.

Public Sub SQLdbConnect()
    `
    ` set global connect property
    `
    strConnect = strSQLLine
    `
End Sub


Now, add the code from Listing A.17 to handle the DBVERSION keyword.

Listing A.17. Coding the SQLdbVersion subroutine.

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.

Listing A.18. Coding the SQLQuietFlag subroutine.

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.

Listing A.19. Coding the SQLdbOpen routine.

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.

Listing A.20. Coding the SQLdbClose routine.

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.

Listing A.21. Coding the SQLdbMake routine.

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.

Listing A.22. Coding the SQLDoCommand routine.

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.

Listing A.23. Coding the ShowTable routine.

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.

Listing A.24. Coding the LoadNotePadFile routine.

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.

Listing A.25. Coding the ErrMsg routine.

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.

Listing A.26. Coding the InitApp routine.

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

Adding the Sub Main Routine

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.

Creating the SQLVBGEN Module

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.

Listing A.27. Coding the general declaration section of SQLVBGEN.

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.

Coding the AutoGen Method

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.

Listing A.28. Coding the AutoGen subroutine.

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.

The AutoGenDBOpen Method

Next, add the AutoGenDBOpen routine from Listing A.29. This opens the MDB.

Listing A.29. Coding the AutoGenDBOpen subroutine.

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

The AutoGenSQVOpen method

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.

Listing A.30. Coding the AutoGenSQVOpen subroutine.

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

The CreateScript method

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.

Listing A.31. Coding the CreateScript subroutine.

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.

The WrScriptHeader and WrScriptFooter Methods

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.

Listing A.32. Coding the WrScriptHeader function.

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

The WrCreateDB Method

Next, add the wrCreateDB method from Listing A.33.

Listing A.33. Coding the WrCreateDB function.

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 WrCreateTable method

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.

Listing A.34. Coding the WrCreateTable function.

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.

The WrCreateField and FieldTypeName Methods

Now build the WrCreateField routine from Listing A.35.

Listing A.35. Coding the WrCreateField function.

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.

Listing A.36. Coding the FieldTypeName function.

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

The WrCreateIndex method

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.

Listing A.37. Coding the WrCreateIndex function.

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.

The wrCreateRelation Method

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.

Listing A.38. Coding the wrCreateRelation function.

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.

Compiling and Testing SQL-VB5

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.

Creating the SQL-VB5 Object Model Interface

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.

Adding Properties to the Object Model

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.

Listing A.40. Adding properties to the Application class.

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

Adding Methods to the Object Model

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).

Listing A.41. Coding the Start and CloseApp subroutines.

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.

Testing 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.

Listing A.42. Adding code to 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.

Modifying SQL-VB5

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: