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


The information in this article applies to:

  • Microsoft Visual Basic Standard Edition for Windows 3.0
  • Microsoft Access 2.0
This article was previously published under Q115833
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.
Modification Type:MajorLast Reviewed:10/29/2003
Keywords:KB115833
©2003 Microsoft Corporation. All rights reserved.