Part 1:目標
- 從上一篇文章中,我們已經獲取了一個工作表(學生信息表)和4個字段(ID,姓名,學號,性別)
- 今天的文章開始向Access數據庫中錄入一條條數據
- 包括兩條記錄
- 張三,11,男
- 李四,12,男
結果如下
Part 2:代碼
Sub test()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim SQL As String
Dim tblName
Dim dbAddr
dbAddr = ThisWorkbook.Path & "\\學生信息.accdb"
tblName = "學生信息表"
'連接數據庫
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open "Data Source=" & dbAddr
End With
'記錄1
stuName = "張三"
stuNum = 11
stuGender = "男"
SQL = "INSERT INTO " & tblName & " (姓名,學號,性別) VALUES" _
& "(" & Chr(39) & stuName & Chr(39) & "," & stuNum & "," & Chr(39) & stuGender & Chr(39) & ")"
Set rs = cnn.Execute(SQL)
'記錄2
stuName = "李四"
stuNum = 12
stuGender = "男"
SQL = "INSERT INTO " & tblName & " (姓名,學號,性別) VALUES" _
& "(" & Chr(39) & stuName & Chr(39) & "," & stuNum & "," & Chr(39) & stuGender & Chr(39) & ")"
Set rs = cnn.Execute(SQL)
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
Part 3:部分代碼解讀
- INSERT INTO 工作表名稱 (字段1,字段2,字段3) VALUES(字段1對應值,字段2對應值,字段3對應值)
- 中文釋義:向工作表X中的字段1中錄入數據X,字段2中錄入數據X,字段3錄入數據X
- 若字段中對應值為字符串,需在單引號(Chr(39))中'',若是時間,則使用#時間信息#
- ID字段是自增式,無需額外賦值,每有一個新的記錄,會自動+1
閱讀更多 excel技能寶典 的文章