How to Use the New MS Access 2.0 DDL Additions in VB 3.0
ID: Q115833
|
The information in this article applies to:
-
Microsoft Visual Basic programming system for Windows, version 3.0
-
Microsoft Access 2.0/Visual Basic 3.0 Compatibility Layer
-
Microsoft Access 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.
- 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.
- 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.
- 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.
Additional query words:
3.00
Keywords :
Version :
Platform :
Issue type :