|
|
|
| Title | Use ADOX to add and drop columns from a database |
|---|
| Description | This example shows how to use ADOX to add and drop columns from a database in Visual Basic 6. |
|---|
| Keywords | database, data, ADO, ADOX |
|---|
| Categories | Database |
|---|
|
|
|
The program uses the following code to add a column. It starts by opening a connection to the database and creating an ADOX catalog representing the database. It gets an ADOX.Table object representing the target table and creates a new ADOX.Column to represent the new column. It sets the column's size and type, and adds it to the table's Columns collection.
|
|
Private Sub cmdAddField_Click()
Dim conn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim data_type As String
Dim data_type_enum As DataTypeEnum
Dim field_len As Integer
Dim pos As Integer
' 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
' Make a catalog for the database.
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = conn
' Get the table.
Set tbl = cat.Tables(txtTableName.Text)
' Create the field.
Set col = New ADOX.Column
col.Name = txtFieldName.Text
pos = InStr(cboDataType.Text, "(")
If pos < 1 Then
data_type = cboDataType.Text
Else
data_type = Left$(cboDataType.Text, pos - 1)
field_len = CInt(Mid$(cboDataType.Text, pos + 1, _
Len(cboDataType.Text) - pos - 1))
End If
Select Case data_type
Case "INTEGER"
col.Type = adInteger
Case "FLOAT"
col.Type = adDouble
Case "REAL"
col.Type = adSingle
Case "VARCHAR"
col.Type = adVarWChar
col.DefinedSize = field_len
Case "CHAR"
col.Type = adWChar
col.DefinedSize = field_len
End Select
' Add the field.
tbl.Columns.Append col
conn.Close
MsgBox "Ok"
Exit Sub
AddFieldError:
conn.Close
MsgBox "Error " & Err.Number & _
" creating field" & vbCrLf & _
Err.Description
Exit Sub
End Sub
|
|
|
The program uses the following code to drop a column. It opens a connection to the database and makes an ADOX catalog representing it. It finds the ADOX.Table object representing the target table and uses its Columns collection's Delete method to remove the column.
|
|
Private Sub cmdRemoveField_Click()
Dim conn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
' 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
' Make a catalog for the database.
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = conn
' Get the table.
Set tbl = cat.Tables(txtTableName.Text)
' Drop the field.
tbl.Columns.Delete (txtFieldName.Text)
conn.Close
MsgBox "Ok"
Exit Sub
DropFieldError:
conn.Close
MsgBox "Error " & Err.Number & _
" creating field" & vbCrLf & _
Err.Description
Exit Sub
End Sub
Private Sub Form_Load()
Dim db_name As String
db_name = App.Path
If Right$(db_name, 1) <> "\" Then db_name = db_name & _
"\"
txtDatabase.Text = db_name & "People.mdb"
End Sub
|
| |
|
| |
|
|
