使用ADO操作Access数据库

 ADO对象

1、Access内嵌的VBA是用ADO技术开发数据库应用的主要工具，ADO是目前Microsoft通用的数据访问技术；

2、ADO对象模型包括：Connection、Recordset、Record、Command、Parameter、Field、Property、Stream、Error九个对象；

3、主要的ADO对象介绍：

1）Connection对象：ADO对象模型中的最高级对象，用来实现应用程序与数据源的连接；

2）Command对象：主要在VBA中使用SQL语句访问、查询和修改数据库中的数据，实现Recordset对象无法实现的操作（数据表级别的操作），可以使用DoCmd代替；

3）Recordset对象：ADO最为常用的、重要的对象，可以访问表和查询对象，返回的记录储存在Recorderset对象中，主要执行的操作：

     ①查询数据表中的数据；

     ②在数据表中添加数据；

     ③更新数据表中的数据；

     ④删除数据表中的特定数据；

 在Access中引用ADO对象

1、Access引用ADO的步骤：

     ①声明、初始化Connection对象；

     ②创建Recordset对象，编程完成各种操作；

     ③关闭ADO对象；

2、声明、初始化Connection对象

'声明Connection对象：一般使用cn做为变量的命名前缀；

Dim coName As ADODB.Connection

'初始化Connection对象，连接当前数据库；

Set cnName = CurrentProject.Connection  

3、声明和打开Recordset对象

1）声明、初始化Recordset对象

Dim rsName As ADODB.Recoreset

set rsName = new ADODB.Recordset

2）打开一个Recordset对象

使用Recordset的Open方法可以打开数据表、查询对象、或直接引用SQL查询语句

rsName.Open source, ActiveConnection, CursorType，LockType，Option

常用Option类型
adCmdTable	表类型
adCmdText	命令文本
adCmdStoredProc	过程
adCmdUnKnowm	不指定内容

4、关闭Recordset和Connection对象

rsName.Close

cnName.Close

Set rsName = Nothing

Set cnName = Nothing

  通过Recordset对象引用记录字段

1、引用字段的方法有2种：直接在记录集对象中引用字段名称；使用记录集对象的Fields（n）属性引用；

Code = rsName！字段名

'引用该字段的第一条记录

Code = rsName.Field(n)

'引用该字段的第n条记录，n从0开始，可以用循环输出需要量的记录

2、如果记录集字段包含空格、或者是一个保留字，则引用时必须将该字段用 [  ] 括起来；

  通过Recordset对象浏览记录

1.Recordset记录集对象提供了4种方法浏览记录

MoveFirst	记录指针移动到记录集的第一条记录
MoveNext	移动到当前记录的下一条记录
MovePrevious	移动到当前记录的上一条记录
MoveLast	移动到记录集的最后一条记录

2、BOF、EOF属性分别记录指针是否在文件开始、文件末尾；

    如果记录集指针指向某记录时，BOF和EOF都为false；

'添加一个窗体部件的按钮事件:浏览下一条记录

Priavte Sub ComomndNext_Click()

     rsDemo.MoveNext

     If rsDemo。EOF Then

          rsDemo.MoveFirst

     End If

End Sub 

3、Recordset对象的LockType属性默认为adLockReadOnly（只读）

adLockReadOnly	数据处于只读状态，数据不能改变
adLockPressimistic	保守式锁定，在编辑数据是时锁定数据源记录，直到数据 编辑完成时才释放
adLockOptimistic	开放式锁定，编辑数据时不锁定数据，只在调用Update方法提交数据时才锁定数据源记录
adLockBathOptimistic	开方式更新，应用于批更新模式

 通过Recordset对象编辑数据

1、用AddNew方法添加记录

1）调用记录集AddNew方法，产生一个空记录

2）为空记录的各个字段赋值；

3）用记录集Update方法更新保持新记录；

'添加记录按钮事件,假设rsDemo记录集有字段Id(int),Name(String),Age(int)

Private Sub CommandAdd_Click()

     rsDemo.MoveLast  '记录集指针移动到记录集最后

     rsDemo.AddNew  '添加一条新纪录   

     rsDemo ! Id = "123"

     rsDemo ! Name = "assad"

     rsDemo ! Age = "18" 

     rsDemo.Update

End Sub

2、用Update方法修改记录

1）寻找并将记录集指针移动到需要修改的记录上；

2）对记录中的各个字段的值进行修改；

3）用记录集Update方法更新保持新记录；

'修改记录集中Age字段所有值+1；

Private Sub UpdateAge() 

     rsDemo.MoveFirst

     Do

          Dim Code as Integer

          Code = rsDemo ! Age 

          rsDemo ! Age = Code +1

     Loop Until rsDemo.EOF

     rsDemo.Update

End Sub

3、用Delete方法删除记录

1）将记录集指针移动到需要删除的记录上；

2）使用Delete方法删除当前记录；

3）将某条记录指定为当前记录

'删除rsDemo数据集中Age = "18" 的记录

Private Sub DeleteAge(Dim deleteAge as Integer)

     rsDemo.MoveFirst

     Do

          IF rsDemo ! Age == deleteAge Then

               rsDemo。Delete

               rsDemo。MoveNext

          End IF

     Loop Until rsDemo.EOF

End Sub

注：一条记录被删除后，Access不会自动是下一条记录成为当前记录，这时要用MoveNext方法将记录集指针定位到最后一条记录中；

  通过Command/DoCmd对象使用SQL命令

Access提供了DoCmd对象，其RunSOL方法可以在VBA中使用SQL命令；

     DoCmd.RunSQL "SQL命令"

or：

     Dim s AS String

     s = "SQL命令"

     DoCmd.RunSQL s

1、定义数据

1）创建数据表

 格式：Create Table 表名 (字段名 数据类型.....)

 如：DoCmd.RunSQL "Create Table 研究生(姓名 text(6),年龄 byte,入学日期 date)"

2）增加字段

 格式：Alter Table 表名 Add 字段名 数据类型

 如：DoCmd.RunSQL "Alter Table student Add 学费 currency"

3）改变字段类型

 格式：Alter Table 表名 Alter 字段名 新数据类型

 如：DoCmd.RunSQL "Alter Table student Alter 年龄  integer"

4）改变字段宽度

 格式：Alter Table 表名 Alter 字段名 新宽度

 如：DoCmd.RunSQL "Alter Table studnt Alter 姓名 text(6)" 

5）删除一个字段

 格式：Alter 表名 Drop 字段名

 如: DoCmd.RunSQL "Alter student Drop 年龄"

6）删除一个数据表

 格式：Drop Table 表名

 如：DoCmd.RunSQL "Drop Table student"

7）修改数据表名字

 格式：DoCmd.rename "新表名", acTable, "旧表名"

 如：DoCmd.rename "学生", acTable, "student"

2、编辑数据

1）向表中追加数据

 格式：Insert into 表名 Values（记录.....）

注：字符串型数据用 ‘   ’， 日期型型数据可以 ‘    ’，或 #  #；

 如：DoCmd.RunSQL "Insert into student Values('李达',35,'2003-1-15') "

  或：Dim name As String

         Dim age As Byte, dates As Date
         name = InputBox("输入学生姓名")
         dates = InputBox("输入入学日期")
          ages = 17
          DoCmd.RunSQL "Insert into student Values ('" & name & "'," & age & ",'" & dates & "')"

2）修改表中记录

 格式：Update 表名 set 字段=数值  Where 限定条件

 如：DoCmd.RunSQL "Update student set 年龄=20 Where 姓名='李达'"

3）删除待定记录

 格式：Delete from 表名 where 限定条件

 如： DoCmd.RunSQL "Delete from student where 姓名='李达'" 

3、实现数据完整性约束

1）设置主键

 格式：Alter Table 表名 Add Primary Key (字段名)

 如：DoCmd.RunSQL "Alter Table 导师 Add Primary Key (导师编号)"

2）设置外键

 格式：Alter Table 主表名 Add Foreign Key (字段名) References 从表名

 如：DoCmd.RunSQL "Alter Table 研究生 Add Foreign Key (导师编号) References 导师"

4、执行查询操作

VBA程序中可以用SQL命令完成数据查询操作，但是无法直接将查询结果所返回的记录集按数据表的形式显示，解决方法有：

1）将查询结果的返回记录集生成一个新表保存在数据库中，然后用ADO记录集对象对这个表进行各种操作，完成后再删除这个表；

  Docmd.runSQL "Select 姓名，职称，年龄  Into temp From 导师 Where 职称 in（'教授'，'副教授'）"

   操作.....

   rsTeacher.Close

   Docmd.runSQL "Drop Table temp"

2）将返回的记录集看成保存在内存中的一个临时表，用ADO记录集对象直接打开该查询指令；

  访问当前数据库以外的数据库

1、Access提供了Connection.Open方法以连接另一个数据库

2、格式：Connection对象.Open "Provider=提供者；Date Source=数据库名；User ID=用户；PassWord=密码"

3、其中提供者为Microsoft.Jet.OLEDDB.4.0；数据库名包括数据库所在的路径（包括.mdb文件名）；