| Title | Use ADO to add and drop columns from a database |
|---|
| Description | This example shows how to use ADO to add and drop columns from a database in Visual Basic 6. The program executes SQL ALTER TABLE statements to add or remove columns. |
|---|
| Keywords | database, data, ADO |
|---|
| Categories | Database |
|---|
|
|
|
The program uses the following code to add a column. It opens a connection to the database and composes an ALTER TABLE SQL statement to add the column. For example, it might execute the statement:
ALTER TABLE People ADD COLUMN NewField VARCHAR(20) DEFAULT ""
It executes the query and closes the connection.
|
|
Private Sub cmdAddField_Click()
Dim conn As ADODB.Connection
Dim query As String
' Open the connection.
Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;" & _
"Data Source=" & txtDatabase.Text
conn.Open
' Add the field.
On Error GoTo AddFieldError
query = "ALTER TABLE " & _
txtTableName.Text & _
" ADD COLUMN " & _
txtFieldName.Text & " " & _
cboDataType.Text
If Len(Trim$(txtDefault.Text)) > 0 Then
query = query & " DEFAULT " & _
txtDefault.Text
End If
conn.Execute query
conn.Close
MsgBox query & vbCrLf & "Ok"
Exit Sub
AddFieldError:
MsgBox "Error " & Err.Number & _
" executing statement:" & vbCrLf & _
" " & query & vbCrLf & Err.Description
conn.Close
Exit Sub
End Sub
|
|
|
The program uses the following code to drop a column. It opens a connection to the database and composes an ALTER TABLE SQL statement to drop the column. For example, it might execute the statement:
ALTER TABLE People DROP COLUMN NewField
It executes the query and closes the connection.
|
|
Private Sub cmdRemoveField_Click()
Dim conn As ADODB.Connection
Dim query As String
' Open the connection.
Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;" & _
"Data Source=" & txtDatabase.Text
conn.Open
' Drop the field.
On Error GoTo DropFieldError
query = "ALTER TABLE " & txtTableName.Text & _
" DROP COLUMN " & txtFieldName.Text
conn.Execute query
conn.Close
MsgBox query & vbCrLf & "Ok"
Exit Sub
DropFieldError:
MsgBox "Error " & Err.Number & _
" executing statement:" & vbCrLf & _
" " & query & vbCrLf & Err.Description
conn.Close
Exit Sub
End Sub
|
| |
|
| |
