|
|
|
| Title | Use ADO to create a database table |
|---|
| Keywords | ADO, CREATE TABLE, database |
|---|
| Categories | Database |
|---|
|
|
|
Connect to the database. Then use the Connection object's Execute method to execute an SQL CREATE TABLE statement.
|
|
Private Sub Command1_Click()
Dim db_file As String
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim num_records As Integer
' Get the database name.
db_file = App.Path
If Right$(db_file, 1) <> "\" Then db_file = db_file & _
"\"
db_file = db_file & "People.mdb"
' Open a connection.
Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & db_file & ";" & _
"Persist Security Info=False"
conn.Open
' Drop the Employees table if it already exists.
On Error Resume Next
conn.Execute "DROP TABLE Employees"
On Error GoTo 0
' Create the Employees table.
conn.Execute _
"CREATE TABLE Employees(" & _
"EmployeeId INTEGER NOT NULL," & _
"LastName VARCHAR(40) NOT NULL," & _
"FirstName VARCHAR(40) NOT NULL)"
' Populate the table.
conn.Execute "INSERT INTO Employees VALUES (1, " & _
"'Anderson', 'Amy')"
conn.Execute "INSERT INTO Employees VALUES (1, 'Baker', " & _
" 'Betty')"
conn.Execute "INSERT INTO Employees VALUES (1, 'Cover', " & _
" 'Chauncey')"
' Add more records ...
' See how many records the table contains.
Set rs = conn.Execute("SELECT COUNT (*) FROM Employees")
num_records = rs.Fields(0)
conn.Close
MsgBox "Created " & num_records & " records", _
vbInformation, "Done"
End Sub
|
|
|
Using this method, you can perform all database creation tasks. You can drop, create, and modify table. The one
thing you cannot do is create the database itself. You can, however, copy an existing database, remove all
of its tables, and then create the tables you need.
For information on database programming in VB .NET, see my book
Visual Basic .NET Database Programming.
|
| |
|
| |
|
|
