How to Use the New MS Access 2.0 DDL Additions in VB 3.0

- Microsoft Visual Basic programming system for Windows, version 3.0 - Microsoft Access 2.0/Visual Basic 3.0 Compatibility Layer - Microsoft Access, version 2.0

NOTE: To run the sample code in this article you must own the Microsoft Access 2.0/Visual Basic 3.0 Compatibility Layer and Microsoft Access version 2.0 in addition to Visual Basic.

SUMMARY

This article contains a sample application that demonstrates how to use the new Data Definition Language (DDL) features added to Microsoft Access version 2.0 and made available to Visual Basic version 3.0 by way of the Compatibility Layer. This article addresses the following DDL features:

   CREATE TABLE
   DROP TABLE
   DROP INDEX
   ALTER TABLE
   CREATE INDEX

MORE INFORMATION

The following sections contain descriptions and syntax statements that came from the Microsoft Access version 2.0 Help file. Please see the Help file for more detailed information. Each syntax for each DDL function should appear on one, single line. To use the DDL functions in Visual Basic, use the Execute method, for example:

   db.Execute "DROP TABLE [Another Table];"

CREATE TABLE

Use CREATE TABLE to create a new table in an existing database in a single step by using the Execute method in Visual Basic. Syntax:

   CREATE TABLE table (field1 type [(size)] [index1] [, field2 type
      [(size)] [index2] [, ...]] [, multifieldindex [, ...]])

DROP TABLE & DROP INDEX

Use DROP TABLE to delete an existing table from a database or DROP INDEX to delete an existing index from a table in a single step by using the Execute method in Visual Basic. Syntax:

   DROP {TABLE table | INDEX index ON table}

ALTER TABLE

Use ALTER TABLE to modify the design of a table after using the CREATE TABLE statement to create it. Syntax:

   ALTER TABLE table {ADD {[COLUMN] field type[(size)] [CONSTRAINT index]
      | CONSTRAINT multifieldindex}
      | DROP {[COLUMN] field I CONSTRAINT indexname} }

CREATE INDEX

Use the CREATE INDEX statement to create a new index on an existing table. Syntax:

   CREATE [ UNIQUE ] INDEX index
      ON table (field[, ...])
      [WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]

Step-by-Step Example for Using New DDL Functions in Visual Basic

The following sample code demonstrates how to use the Execute method with each of the new DDL functions. The new DDL functions operate identically on both Microsoft Access version 1.x and 2.0 databases.

WARNING: Use caution when running these routines. You don't want to accidentally delete a table or index that you really wanted to keep. You should test these routines on sample databases first.

The sample code in this example goes through each of the new DDL routines, one by one. Certain command buttons are invisible at the start, so it is important to follow the instructions exactly.

1. Start a new project in Visual Basic. Form1 is created by default.

2. The contents of the FORM1.FRM file follows. Copy this code into a text editor. Then change the command lines that are shown below as two lines into a single line, and save the file. Then remove the default Form1 form from the Visual Basic project and add the newly saved FORM1.FRM file to the project.

   VERSION 2.00 Begin Form Form1

         Caption         =   "Form1"
         ClientHeight    =   4965
         ClientLeft      =   1095
         ClientTop       =   1485
         ClientWidth     =   8460
         Height          =   5370
         Left            =   1035
         LinkTopic       =   "Form1"
         ScaleHeight     =   4965
         ScaleWidth      =   8460
         Top             =   1140
         Width           =   8580
         Begin ListBox List1
            Height          =   2175
            Left            =   120
            Sorted          =   -1  'True
            TabIndex        =   5
            Top             =   600
            Visible         =   0   'False
            Width           =   2775
         End
         Begin CommandButton Command2
            Caption         =   "Create a new Index for BIBLIO.MDB"
            Height          =   495
            Left            =   3240
            TabIndex        =   4
            Top             =   600
            Visible         =   0   'False
            Width           =   4335
         End
         Begin CommandButton Command4
            Caption         =   "Drop the new Index from BIBLIO.MDB"
            Height          =   495
            Left            =   3240
            TabIndex        =   3
            Top             =   2040
            Visible         =   0   'False
            Width           =   4335
         End
         Begin CommandButton Command3
            Caption         =   "Alter the new Table in BIBLIO.MDB"
            Height          =   495
            Left            =   3240
            TabIndex        =   2
            Top             =   1320
            Visible         =   0   'False
            Width           =   4335
         End
         Begin CommandButton Command5
            Caption         =   "Drop the new Table from BIBLIO.MDB"
            Height          =   495
            Left            =   3240
            TabIndex        =   1
            Top             =   2760
            Visible         =   0   'False
            Width           =   4335
         End
         Begin CommandButton Command1
            Caption         =   "Create a new Table for BIBLIO.MDB"
            Height          =   495
            Left            =   3240
            TabIndex        =   0
            Top             =   0
            Width           =   4335
         End
         Begin Label Label1
            Caption         =   "List of Table Names:"
            Height          =   375
            Left            =   480
            TabIndex        =   6
            Top             =   120
            Visible         =   0   'False
            Width           =   2175
         End
      End
   
      Sub Command1_Click ()
         Dim db As database
         ' Change the following two lines into one single line:
         MySQL = "CREATE TABLE [Another Table] ([First Name] TEXT,
            [Last Name] TEXT);"
         Set db = OpenDatabase("C:\VB\BIBLIO.MDB")
         db.Execute MySQL
         command2.Visible = True
         For i% = 0 To db.TableDefs.Count - 1
            list1.AddItem db.TableDefs(i%).Name
         Next i%
         label1.Visible = True
         list1.Visible = True
         db.Close
         command2.setfocus
      End Sub
   
      Sub Command2_Click ()
         Dim db As database
         ' Change the following two lines into one single line:
         MySQL = "CREATE UNIQUE INDEX MyIndex ON [Another Table] ([Last Name])
            WITH PRIMARY;"
         Set db = OpenDatabase("C:\VB\BIBLIO.MDB")
         db.Execute MySQL
         command3.Visible = True
         list1.Clear
         For i% = 0 To db.TableDefs("Another Table").Indexes.Count - 1
            list1.AddItem db.TableDefs("Another Table").Indexes(i%).Name
         Next i%
         label1.Caption = "List of Index Names for 'Another Table'"
         db.Close
         command3.setfocus
      End Sub
   
      Sub Command3_Click ()
         Dim db As database
         MySQL = "ALTER TABLE [Another Table] ADD COLUMN Salary CURRENCY;"
         Set db = OpenDatabase("C:\VB\BIBLIO.MDB")
         db.Execute MySQL
         command4.Visible = True
         list1.Clear
         For i% = 0 To db.TableDefs("Another Table").Fields.Count - 1
            list1.AddItem db.TableDefs("Another Table").Fields(i%).Name
         Next i%
         label1.Caption = "List of Field Names for 'Another Table'"
         db.Close
         command4.setfocus
      End Sub
   
      Sub Command4_Click ()
         Dim db As database
         MySQL = "DROP INDEX MyIndex ON [Another Table];"
         Set db = OpenDatabase("C:\VB\BIBLIO.MDB")
         db.Execute MySQL
         command5.Visible = True
         list1.Clear
         For i% = 0 To db.TableDefs("Another Table").Indexes.Count - 1
            list1.AddItem db.TableDefs("Another Table").Indexes(i%).Name
         Next i%
         label1.Caption = "List of Index Names for 'Another Table'"
         db.Close
         command5.setfocus
      End Sub
   
      Sub Command5_Click ()
         Dim db As database
         MySQL = "DROP TABLE [Another Table];"
         Set db = OpenDatabase("C:\VB\BIBLIO.MDB")
         db.Execute MySQL
         list1.Clear
         For i% = 0 To db.TableDefs.Count - 1
            list1.AddItem db.TableDefs(i%).Name
         Next i%
         label1.Caption = "List of Table Names"
         db.Close
      End Sub
   
   

3. From the Run menu, choose Start (ALT, R, S), or press the F5 key to run the program.

4. Click the Command1 or 'Create a new Table for BIBLIO.MDB' button to create a new table (Another Table). You will see it added to the list box.

5. Click the Command2 or 'Create a new Index for BIBLIO.MDB' button to create a new index (MyIndex). You will see it added to the list box.

6. Click the Command3 or 'Alter the new Table in BIBLIO.MDB' button to add a new field (Salary) to the new table (Another Table). You will see it and the two original fields listed in the list box.

7. Click the Command4 or 'Drop the new Index from BIBLIO.MDB' button to delete the new index (MyIndex). You will see an empty list box.

8. Click the Command5 or 'Drop the new Table from BIBLIO.MDB' button to delete the new table (Another Table) from the BIBLIO.MDB database. You will see that this table is no longer listed in the list box.