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

2020-12-25 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 做程序要有一個好的人機互動過程,應該注意些什麼?

相關焦點

  • 利用VBA向一個已有的數據表中添加數據記錄的方法
    利用VBA向一個已有的數據表中添加數據記錄的方法大家好,今日我們繼續講解VBA資料庫解決方案的第23講:利用VBA如何向一個已有的數據表中添加記錄。在上一講中我們講了如何把工作表的數據保存到新建的數據表中方案,這是非常好的方法,希望讀者能在實際的工作中多加利用。
  • MySQL中如何針對表進行添加、修改、刪除欄位?
    昨兒和大家一起看了,如何創建表,及建表過程中的注意事項,今天我們繼續針對表的操作來分享,主要是關於如何針對表進行,添加、修改和刪除欄位等內容,具體我們來一起看下。大家可以想一想,隨意發揮哦~若是要刪除欄位呢,我先將dept_id的欄位添加進去,然後練習一下刪除這個欄位,先來看一下表,目前是有dept_id這個欄位的:
  • 如何批量修改資料庫中的數據
    在資料庫解決方案中,我在從實際應用的角度,把工作中可能遇到的各種情況都儘可能的給羅列上,我們先後講了:①如何在資料庫中動態刪除和建立數據表(第14講)②在已有的數據表中刪除、添加、修改欄位的方法(第21講);③根據工作表中的數據,生成資料庫中新的數據表的方法(第22講);④向一個已有的數據表中添加數據記錄的方法(第23講);⑤把工作表中提供的數據在數據表中逐一刪除
  • 怎麼修改Mysql數據表中的視圖
    本篇文章主要給大家介紹mysql數據表中怎麼進行修改視圖的操作。通俗的說,視圖是由SELECT語句組成的查詢定義的虛擬表,也就是由一張或多張表中的數據組成的。然後可以先通過圖文版的Navicat資料庫管理工具,更直觀方便的來查看指定數據表my_v1視圖的信息。如上圖中,my_v1視圖中包含三個欄位id,name,c_id。
  • NET開發-使用SSMS工具和SQL語句創建、修改和刪除數據表
    數據表是一張二維表格,以行和列的形式將數據存儲到資料庫中,可以將數據表想像成Excel中的表格:在數據表中,由行和列的坐標確定某一個具體的數據,如上圖中的A1表示第一行第一列,A是列號,1是行號。然後點擊「保存」按鈕或按下CTRL+S給合鍵,如圖4所示:在圖4中輸入一個表的名稱,這裡輸入BW_Score,如圖5所示:在「對象資源管理器」中刷新一下表文件夾,就可以看到剛才新建的數據表BW_Score和其中創建的欄位。1.2.
  • MySQL資料庫教程-修改數據表結構與刪除表
    但在一些條件下也是可以修改數據表結構的,如剛定義好的新表,本節主要介紹數據表結構的修改語法及實例。01修改表的欄位修改表的欄位主要類型包括刪除欄位、添加新欄位、修改欄位名稱、修改欄位類型四種操作類型。我們以上一篇文章創建的數據表為例對修改表欄位進行說明。
  • 如何用SQL語句添加和修改欄位?
    用SQL語句添加欄位並不難,下面小編整理了SQL添加和修改欄位的基本命令,希望對各位小夥伴有所幫助。增加欄位:alter table 表名 add 欄位名 type not null default 0在指定位置插入新欄位:alter table 表名 add [column] 欄位名 欄位類型 是否可為空 comment '注釋' after
  • 「Python替代Excel Vba」系列(終):vba中調用Python
    本文主要效果如下圖:處理數據的過程在 Python 中進行。輸入條件,輸出結果的過程在 Vba 進行。可以隨意修改匯總方式(求和、平均等)與匯總欄位。可以隨意修改匯總欄位和過濾條件。所有的修改都無需改動代碼。數據源文件與顯示文件是獨立分開的。
  • SQL增加、修改、刪除是對DML語法的查詢、更新
    一、複製原有表CREATE TABLE 新表名 如:mytable AS SELECT * FROM 複製源Oltable ;二、對數據表加入新數據,INSERT INFO 表名稱 [(欄位名稱,欄位名稱2,....)] VALUES (數據,數據2,.....)
  • EXCEL操作資料庫表,自動判斷欄位類型,批量添加數據到資料庫表
    使用SQL語句添加數據時,不同的欄位類型要求不一樣,字符類型要求要用單引號括起來,數字類型則什麼都不用加,時間日期類型要前面後井號"#",如果不按要求寫語句將會出錯。所在在添加數據時要把每個欄位類型都弄清楚,很容易出錯,所以研究了一下自動檢測欄位類型,然後根據不同的類型為每個欄位使用不同的寫法。
  • Excel vba 批量刪除窗體控制項
    文/江覓易見動態創建窗體控制項後,可以進行動態刪除,如果是設計時從工具欄添加的控制項,不能動態刪除,不要問為什麼vba就是這麼規定的,也可以認為這是一個BUG,反正就是不能。效果演示本示例演示了刪除標籤控制項、文本控制項和圖片控制項。單擊頁面建立所有控制項,然後單擊按鈕逐一刪除。刪除圖片控制項後效果。
  • 如何在Element實現表格的增加、修改和刪除
    插入一個表格el-table,並添加表格欄位4、添加一個el-dialog彈窗,並插入一個el-form標籤元素初始化表格數據源對象、彈窗綁定變量6、再次添加彈窗中的form表單對象變量,表單中必填提示
  • 【超詳細】ArcGIS欄位計算器分割欄位中的字符串
    2、案例效果圖:(將OID編號為33的「樺木科鵝耳櫪屬」以「科」為特殊字符,分割成「樺木科」和「鵝耳櫪屬」,其它記錄以此類推)3、案例具體實現:a、由於原始數據是保存在excel中,所以需要將excel數據添加到arcgis中,需要注意的是arcgis只能添加.xls格式的
  • Inner join連接兩個數據表的應用講解
    大家好,我們繼續講解VBA資料庫解決方案,今日講解第57講內容: 內連接(Inner join)應用於兩個數據表的講解。這講的內容還是講內連接的應用,我們在上一講中講了內連接在工作表中的應用,今天的內容是內連接在數據表中的應用。大家要清楚我本書中常用的一些概念,工作表指的是EXCEL文件中的工作表,數據表是指我們資料庫中的數據表,是兩個不同的概念。
  • 「Python替代Excel Vba」系列(二):pandas分組統計與操作Excel
    不過這次我們需要把每個班級成績好的同學給揪出來好好表揚,因此條件如下:找出每個班級的top 3 學生,在原數據表中以綠色底色標記找出每個班級中低於班級平均分的學生,在原數據表中以紅色底色標記上述條件均以
  • 維修任務管理,vba編程如何構建任務管理過程
    No.1 任務管理任務管理是生產過程中的一個很重要的管理過程。在大量的維修任務需要分派的時候,系統的任務管理就帶給我們,很方便的操作。也會使得在工作當中不會變得一團亂麻,而且,任務越多,效率越高。下面介紹一下任務管理過程,如何利用vba代碼來構建一個過程方法。首先要製作一個任務表,信息有任務名稱、任務時間、執行人、設備、單元名稱、遺留問題等等欄位。內容根據不同的工作需求而定。可參考下圖製作自己的任務記錄表。No.2 發布任務通過上圖,可看到,所有的欄位列表也顯示在發布任務操作頁裡了。
  • 如何用VBA代碼添加Form窗體表單,看完全明了,詳細講解
    NO.1使用VBA代碼添加Form窗體表單,對於一些初學者來說,似乎真的很為難。除了對代碼陌生之外,並不知道如何來進行操作添加後的窗體。這也是一件十分困擾的事情。本節將介紹一下,使用vba代碼添加窗體,然後在窗體上添加控制項,如標籤按鈕等等方法。NO.2vba中 VBComponents集合裡包含了模塊、窗體、類模塊。
  • WooCommerce修改帳單和收貨地址欄位
    WooCommerce中有兩種類型的欄位-計費和運送欄位。在這兩個類別中都顯示了某些欄位,例如名字欄位或國家/地區欄位。現在仔細閱讀:我們可以使用 woocommerce_default_address_fields 鉤子在欄位中進行更改-在這種情況下,更改將影響所有內容-修改「我的帳戶」中的帳單和運送欄位頁面以及結帳頁面。
  • 利用VBA代碼刪除空白行,如何刪除重複數據
    大家好,今日內容仍是和大家分享VBA編程中常用的簡單「積木」過程代碼,NO.136-NO.138,內容是:插入多行表格的方法、如何刪除工作表中的空白行、刪除重複數據行等內容。VBA過程代碼137:EXCEL中,如何刪除工作表中的空白行Sub mynz()Dim rRow As LongDim LRow As LongDim i As Long
  • 終於有解了:如何在 Excel 數據透視表的分類欄位內部排序?
    數據透視表中的值雖然可以排序,但是只能有一個排序條件,無法設兩個優先級。如果有兩個行欄位,要求按第一個欄位排序的同時,還要在分類中按第二個欄位再次排序,怎麼才能做到?文字描述大家可能沒什麼感覺,看了案例就明白了。