|
Option Explicit
Sub 数据库连接()
'告诉电脑，我们要有ado，引用ado
'创建连接对象
'给对象取名字
Dim con As ADODB.Connection '声明对象变量
'创建对象变量并赋值
Set con = New ADODB.Connection
'建立数据库的连接
'dim con as new adodb.connection
'con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\学生管理.accdb"
'MsgBox "连接成功"
With con
.Provider = "microsoft.ace.oledb.12.0"
.ConnectionString = ThisWorkbook.Path & "\学生管理.accdb"
.Open End With
MsgBox "连接成功"
End Sub
Sub 插入记录()
Dim con As ADODB.Connection '声明对象变量
'创建对象变量并赋值
Set con = New ADODB.Connection
'建立数据库的连接
With con
.Provider = "microsoft.ace.oledb.12.0"
.ConnectionString = ThisWorkbook.Path & "\学生管理.accdb"
.Open End With
MsgBox "连接成功" 'insert into 表名（列1，列2）values（值1，值2，值3）
Dim sql As String
sql = "insert into 院系(院系编号,院系名,电话) values('A09','人文学院','9999')"
con.Execute (sql)
'释放空间变量
con.Close '关闭连接
Set con = Nothing '释放空间
End Sub
Sub 删除记录()
Dim con As New ADODB.Connection
con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\学生管理.accdb"
'Dim sql As String
'sql = "delete from 院系 where 院系编号=''"
Dim sql As String
'sql = "update 院系 set 电话='' where 系名=''"
Dim str As String
str = InputBox("输入性别", "提示")
sql = "updata 学生 set 班级='2班' where 性别='" & str & " '"
con.Execute (sql)
End Sub
Sub 简单查询()
Dim con As New ADODB.Connection
con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\学生管理.accdb"
Dim sql As String
sql = "select * from 学生"
'获取记录集
Dim rs As New ADODB.Recordset
Set rs = con.Execute(sql)
'获取字段名
Dim i As Integer
For i = 0 To rs.Fields.Count - 1
Cells(1, i + 1) = rs.Fields(i).Name
Next
'将记录集rs的记录返回到工作表中 Range("A2").CopyFromRecordset rs
rs.Close: Set rs = Nothing
con.Close: Set con = Nothing
End Sub
|
