Microsoft KB Archive/115833

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

Article ID: 115833

Article Last Modified on 10/29/2003

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition
 * Microsoft Access 2.0 Standard Edition

-



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.

 Start a new project in Visual Basic. Form1 is created by default.  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  From the Run menu, choose Start (ALT, R, S), or press the F5 key to run the program. 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. 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. 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.</li> 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.</li> 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.</li></ol>

Additional query words: 3.00

Keywords: KB115833

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.