|  | Click Here!

Search the site:
EXPERT SEARCH

| Programming Languages
| Databases
| Security
| Web Services
| Network Services
| Middleware
| Components
| Operating Systems
| User Interfaces
| Groupware & Collaboration
| Content Management
| Productivity Applications
| Hardware
| Fun & Games

EarthWeb sites
Crossnodes
Datamation
Developer.com
DICE
EarthWeb.com
EarthWeb Direct
ERP Hub
Gamelan
GoCertify.com
HTMLGoodies
Intranet Journal
IT Knowledge
IT Library
JavaGoodies
JARS
JavaScripts.com
open source IT
RoadCoders
Y2K Info

#

- Appendix A
  - The SQL-VB5 Project
  - - The Benefits of SQL-VB5
    - Listing A.1. A sample SQL script.
    - Designing SQL-VB5
      - Figure A.1.
    - Creating the SQL-VB5 Forms
    - SQLVBMain Form
      - Figure A.2.
    - Creating the SQLVBChild Child Form
      - Figure A.3.
    - Listing A.2. Adding code to the Form_Load event.
    - Listing A.3. Adding code to the Form_Resize event.
      - Creating the SQLVBAbout Form
        - Figure A.4.
    - Listing A.4. Coding the declarations for the SQLVBAbout form.
    - Listing A.5. Coding the GetKeyValue function.
    - Listing A.6. Coding the StartSysInfo subroutine.
    - Listing A.7. Adding code to the cmdSysInfo_Click event.
      - Adding the SQLVBMain Code
    - Listing A.8. Coding the LoadCmdBtns subroutine.
    - Listing A.9. Adding code to the cmdBtn_Click event.
    - Creating the SQL-VB5 Main Module
      - Declaring Global Variables
    - Listing A.10. Adding the global variables.
      - Creating SQLMain
    - Listing A.11. Coding the SQLMain routine.
      - Creating SQLFileOpen
    - Listing A.12. Coding the SQLFileOpen routine.
      - Creating SQLFileClose
    - Listing A.13. Coding the SQLFileClose routine.
      - Creating SQLFileProcess
    - Listing A.14. Coding the SQLFileProcess routine.
      - Creating the Support Routines
    - Listing A.15. Coding the GetToken routine.
    - Listing A.16. Coding the SQLDBConnect method.
    - Listing A.17. Coding the SQLdbVersion subroutine.
    - Listing A.18. Coding the SQLQuietFlag subroutine.
    - Listing A.19. Coding the SQLdbOpen routine.
    - Listing A.20. Coding the SQLdbClose routine.
    - Listing A.21. Coding the SQLdbMake routine.
    - Listing A.22. Coding the SQLDoCommand routine.
    - Listing A.23. Coding the ShowTable routine.
    - Listing A.24. Coding the LoadNotePadFile routine.
    - Listing A.25. Coding the ErrMsg routine.
    - Listing A.26. Coding the InitApp routine.
      - Adding the Sub Main Routine
    - Creating the SQLVBGEN Module
    - Listing A.27. Coding the general declaration section of SQLVBGEN.
      - Coding the AutoGen Method
    - Listing A.28. Coding the AutoGen subroutine.
      - The AutoGenDBOpen Method
    - Listing A.29. Coding the AutoGenDBOpen subroutine.
      - The AutoGenSQVOpen method
    - Listing A.30. Coding the AutoGenSQVOpen subroutine.
      - The CreateScript method
    - Listing A.31. Coding the CreateScript subroutine.
      - The WrScriptHeader and WrScriptFooter Methods
    - Listing A.32. Coding the WrScriptHeader function.
      - The WrCreateDB Method
    - Listing A.33. Coding the WrCreateDB function.
      - The WrCreateTable method
    - Listing A.34. Coding the WrCreateTable function.
      - The WrCreateField and FieldTypeName Methods
    - Listing A.35. Coding the WrCreateField function.
    - Listing A.36. Coding the FieldTypeName function.
      - The WrCreateIndex method
    - Listing A.37. Coding the WrCreateIndex function.
      - The wrCreateRelation Method
    - Listing A.38. Coding the wrCreateRelation function.
    - Compiling and Testing SQL-VB5
      - Figure A.5.
      - Figure A.6.
      - Figure A.7.
    - Creating the SQL-VB5 Object Model Interface
      - Adding Properties to the Object Model
    - Listing A.40. Adding properties to the Application class.
      - Adding Methods to the Object Model
    - Listing A.41. Coding the Start and CloseApp subroutines.
    - Testing the Object Model
      - Figure A.8.
    - Listing A.42. Adding code to the Command1_Click event.
    - Modifying SQL-VB5

## 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:

- Open an ASCII file that contains valid SQL script statements.

- Process the open file sequentially and perform all SQL statements in the file, including all SQL DDL and SQL DML keywords.

- Display any result sets created by the SQL statements (`SELECT_FROM`) in data grids that can be reviewed by the user. To keep the project relatively simple, allow users to only view the results of queries, not update them.

- Provide an MDI interface so that more than one result set can be viewed at a time.

- Provide a VBA-compliant object model interface so that `SQL-VB5` can be called from within other programs.

- Provide the ability to read an MDB file and generate a valid `SQL-VB5` script to re-create the database structure, including all tables, indexes, and relationships.

- Provide a simple About box to display program information.

- Provide direct access to editing SQL script files without having to exit the `SQL-VB5` program.

- Allow users to add comment lines in the `SQL-VB5` script for documentation purposes.

- Provide reasonable error trapping and reporting to aid in debugging SQL scripts.

- Allow users to open any Microsoft Jet-supported database format and allows users to create new Microsoft Jet-format databases.

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:

- SQLVBMain: An MDI form to enclose all activity.

- SQLVBChild: A child form that displays the result sets.

- SQLVBAbout: A simple form that displays the About box.

- `SQLVBMOD`: A Visual Basic code module that contains all the methods for interpreting an `SQL-VB5` script.

- `SQLVBGEN`: A Visual Basic code module that contains all the methods for generating an `SQL-VB5` script from the existing MDB.

- `SQLVBCLASS`: A Visual Basic code module that contains the primary startup routine (`Main`).

- `Application`: A Visual Basic class module that encapsulates the object model interface.

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

- `SQLFileOpen`: To open the ASCII file selected by the user.

- `SQLFileProcess`: To process the SQL commands in the file.

- `SQLFileClose`: To safely close the ASCII file upon completion.

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:

- Add the ability to create non-Microsoft Jet database files such as dBASE, FoxPro, and Paradox. To do this, you need to add additional parameters to the `dbMake` script line along with corresponding code in the `SQLdbMake` routine to handle the added information.

- Add the ability to edit data within the grids that appear in the main form. You need to review the DBGrid control and set some additional parameters before you launch the SQLVBChild form.

- Make `SQL-VB5` an Add-In application. This involves adding a class module to the project and declaring an SQL-VB5 class with at least one method that loads and processes a script. Check the Visual Basic documentation on creating OLE-enabled applications for more information on how to create a Visual Basic Add-In.

| Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details.