利用VBA代碼在已有的數據表中刪除,添加,修改欄位

大家好,今日繼續給大家講解VBA資料庫解決方案的第21講,如何利用VBA代碼在已有的數據表中刪除，添加，修改欄位。這個內容是操作資料庫的一項必修的內容，還望大家在實際工作中多利用，所以這節的知識，對於讀者提高自己的資料庫的操作很有幫助，這節的內容同時也涉及到很多的SQL語句操作，對於大家理解通過VBA對資料庫的控制會很有幫助，或許有的朋友剛剛看到我的平臺的文章，對於代碼的熟悉程度不如老朋友，還望能充分結合我之前的書籍《VBA代碼解決方案》多實踐，提高自己對VBA的理解能力，不然對於大段的代碼的理解將是非常吃力的。

實例內容：我們還是利用數據來說話：如下的資料庫的數據表「信息參考」共有兩個的欄位

1

現在要增加一個「電子郵箱」的欄位，先設計這個欄位的長度為10個字符，然後再修改一下到50個字符，當然在增加「電子郵箱」的欄位前要先判斷原數據表中是否存在這個欄位，如果存在要先刪除這個欄位，同時在操作的過程中要做到可視化的控制。

分析：這個實例並不難，但過程是較繁瑣的，我們在做程序前要先理清思路。我們看看要實現這個要求的大概要經過的過程：

1 資料庫和數據表連接的創建和打開。

2 顯示欄位（可視化的要求），同時要判斷是否有「電子郵箱」欄位.

3 如果有「電子郵箱」，那麼刪除，同時顯示一下刪除後的結果（可視化）.

4 刪除後，或者原數據表中沒有這個欄位，那麼建立「電子郵箱」欄位，這時建立的是字符長度是10.

5 顯示建立後的結果（可視化）.

6 修改欄位長度。

7 最後再次顯示結果（可視化）。

上述過程緊扣可視化的要求進行，一步一步的完成，我們在做程序的時候，也要做到這種可視化的要求，要給用戶一個友好的操作，不能呆板的寫代碼，只是考慮到程序的運行時間，要儘可能的多為用戶考慮，操作到哪一步了，要提示給用戶，特別是在數據處理這類單調的工作過程中，更要這樣。

下面看看我們的代碼：

Sub mynzAddFields() '數據表中刪除增加修改欄位

Dim cnADO, rsADO As Object

Dim strPath, strSQL As String

Set cnADO = CreateObject("ADODB.Connection")

Set rsADO = CreateObject("ADODB.RecordSet")

strPath = ThisWorkbook.Path & "\mydata2.accdb"

strTable = "信息參考"

cnADO.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & strPath

tt = False

strSQL = "SELECT * FROM " & strTable

rsADO.Open strSQL, cnADO, 1, 3

Cells.ClearContents

MsgBox "下面將顯示各個欄位，判斷有無[電子郵箱]欄位", vbInformation, "提示"

For i = 0 To rsADO.Fields.Count - 1

Sheets("Sheet1").Cells(1, i + 1) = rsADO.Fields(i).Name

If rsADO.Fields(i).Name = "電子郵箱" Then tt = True

Next i

rsADO.Close

If tt = True Then

MsgBox "原有[電子郵箱]欄位，將刪除", vbInformation, "提示"

strSQL = "ALTER TABLE " & strTable & " DROP 電子郵箱"

cnADO.Execute strSQL

MsgBox "下面將顯示各個欄位，判斷刪除效果", vbInformation, "提示"

Cells.ClearContents

strSQL = "SELECT * FROM " & strTable

rsADO.Open strSQL, cnADO, 1, 3

For i = 0 To rsADO.Fields.Count - 1

Sheets("Sheet1").Cells(1, i + 1) = rsADO.Fields(i).Name

Next i

rsADO.Close

End If

MsgBox "下面將添加[電子郵箱]欄位", vbInformation, "提示"

strSQL = "ALTER TABLE " & strTable & " ADD 電子郵箱 TEXT(10)"

cnADO.Execute strSQL

MsgBox "欄位添加成功,下面將顯示各個欄位，判斷添加效果", vbInformation, "提示"

Cells.ClearContents

strSQL = "SELECT * FROM " & strTable

rsADO.Open strSQL, cnADO, 1, 3

For i = 0 To rsADO.Fields.Count - 1

Sheets("Sheet1").Cells(1, i + 1) = rsADO.Fields(i).Name

Next i

rsADO.Close

MsgBox "添加欄位長度為10個字符，下面將修正為50個字符。", vbInformation, "提示"

strSQL = "ALTER TABLE " & strTable & " ALTER 電子郵箱 TEXT(50)"

cnADO.Execute strSQL

MsgBox "欄位長度修改成功，下面將顯示修改後的記錄", vbInformation, "提示"

Cells.ClearContents

strSQL = "SELECT * FROM " & strTable

rsADO.Open strSQL, cnADO, 1, 3

For i = 0 To rsADO.Fields.Count - 1

Sheets("Sheet1").Cells(1, i + 1) = rsADO.Fields(i).Name

Next i

For i = 1 To rsADO.RecordCount

For j = 0 To rsADO.Fields.Count - 1

Sheets("Sheet1").Cells(i + 1, j + 1) = rsADO.Fields(j)

Next j

rsADO.MoveNext

Next i

rsADO.Close

cnADO.Close

Set rsADO = Nothing

Set cnADO = Nothing

End Sub

代碼截圖：

2

3

代碼的講解：

這裡我只是簡單的講解一下了：

1 Cells.ClearContents

MsgBox "下面將顯示各個欄位，判斷有無[電子郵箱]欄位", vbInformation, "提示"

For i = 0 To rsADO.Fields.Count - 1

Sheets("Sheet1").Cells(1, i + 1) = rsADO.Fields(i).Name

If rsADO.Fields(i).Name = "電子郵箱" Then tt = True

Next i

rsADO.Close

上述代碼是顯示各個欄位，同時判斷，判斷是否有電子郵箱的欄位.

2 If tt = True Then

MsgBox "原有[電子郵箱]欄位，將刪除", vbInformation, "提示"

strSQL = "ALTER TABLE " & strTable & " DROP 電子郵箱"

cnADO.Execute strSQL

MsgBox "下面將顯示各個欄位，判斷刪除效果", vbInformation, "提示"

Cells.ClearContents

strSQL = "SELECT * FROM " & strTable

rsADO.Open strSQL, cnADO, 1, 3

For i = 0 To rsADO.Fields.Count - 1

Sheets("Sheet1").Cells(1, i + 1) = rsADO.Fields(i).Name

Next i

rsADO.Close

End If

如果有這個欄位，上述代碼將刪除這個欄位，這裡利用了ALTER TABLE 命令，這個命令的作用是添加數據表的欄位：

ALTER TABLE 語句用於在已有的表中添加、修改或刪除列。

添加欄位的語法：Alter table tablename add column_name datatype

修改欄位的語法：Alter table tablename alter column_name datatype

刪除欄位的語法：Alter table tablename drop Column_name;

添加、修改、刪除多列的話，用逗號隔開。

3

MsgBox "下面將添加[電子郵箱]欄位", vbInformation, "提示"

strSQL = "ALTER TABLE " & strTable & " ADD 電子郵箱 TEXT(10)"

cnADO.Execute strSQL

MsgBox "欄位添加成功,下面將顯示各個欄位，判斷添加效果", vbInformation, "提示"

Cells.ClearContents

strSQL = "SELECT * FROM " & strTable

rsADO.Open strSQL, cnADO, 1, 3

For i = 0 To rsADO.Fields.Count - 1

Sheets("Sheet1").Cells(1, i + 1) = rsADO.Fields(i).Name

Next i

rsADO.Close

上面的代碼將添加欄位，並顯示添加的結果

4

MsgBox "添加欄位長度為10個字符，下面將修正為50個字符。", vbInformation, "提示"

strSQL = "ALTER TABLE " & strTable & " ALTER 電子郵箱 TEXT(50)"

cnADO.Execute strSQL

MsgBox "欄位長度修改成功，下面將顯示修改後的記錄", vbInformation, "提示"

Cells.ClearContents

strSQL = "SELECT * FROM " & strTable

rsADO.Open strSQL, cnADO, 1, 3

For i = 0 To rsADO.Fields.Count - 1

Sheets("Sheet1").Cells(1, i + 1) = rsADO.Fields(i).Name

Next i

上面的代碼將修改欄位，並顯示添加的結果

5

For i = 1 To rsADO.RecordCount

For j = 0 To rsADO.Fields.Count - 1

Sheets("Sheet1").Cells(i + 1, j + 1) = rsADO.Fields(j)

Next j

rsADO.MoveNext

Next i

rsADO.Close

cnADO.Close

Set rsADO = Nothing

Set cnADO = Nothing

上述代碼再次做數據的顯示。

最後我們再看看程序的運行過程：

8

已經有了電子郵箱的欄位，提示刪除。

9

12

添加欄位，然後顯示最後的結果：

14

修正欄位，最後顯示整個記錄：

以上就是程序的執行過程，後臺程序的運行和提供給用戶的信息同步，很好的解決了人機交流的問題。

今日內容回向：

1 如何在數據表中刪除、添加、修改欄位？

2 做程序要有一個好的人機互動過程，應該注意些什麼？