How to use ADO technology to operate access database

First of all, let’s introduce the ADO object:

1. The VBA embedded in Access is the main tool for developing database applications using ADO technology. ADO is currently common to Microsoft Data access technology;

2. ADO object model includes nine objects: Connection, Recordset, Record, Command, Parameter, Field, Property, Stream, and Error;

Related recommendations: access database tutorial

3. Introduction to the main ADO objects:

1) Connection object: the highest-level object in the ADO object model, used to realize the connection between the application and the data source ;

2) Command object: Mainly use SQL statements in VBA to access, query and modify data in the database, to achieve operations that cannot be achieved by the Recordset object (data table level operations), you can use DoCmd instead;

3) Recordset object: the most commonly used and important object in ADO, which can access tables and query objects. The returned records are stored in the Recordset object. The main operations are:

① Query the data table The data in the data table;

②Add data in the data table;

③Update the data in the data table;

④Delete specific data in the data table;

Referencing ADO objects in Access

1. Steps for Access to reference ADO:

①Declare and initialize the Connection object;

②Create the Recordset object and complete the programming An operation;

③Close the ADO object;

2. Declare and initialize the Connection object

'Declare the Connection object: generally use cn as the naming prefix of the variable;

Dim coName As ADODB.Connection

'Initialize the Connection object and connect to the current database;

Set cnName = CurrentProject.Connection

3. Declare and open the Recordset object

1) Declare and initialize the Recordset object

Dim rsName As ADODB.Recoreset
set rsName = new ADODB.Recordset

2) Open a Recordset object

Use the Open method of Recordset to open a data table, query object, or directly reference the SQL query statement

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

4. Close the Recordset and Connection object

rsName.Close
cnName.Close
Set rsName = Nothing
Set cnName = Nothing

Reference record fields through Recordset object

1. There are two ways to reference fields: directly refer to the field name in the recordset object; use Fields(n) of the recordset object Attribute reference;

Code = rsName！字段名

'references the first record of this field

Code = rsName .Field(n)

'references the nth record of this field, n starts from 0, you can use a loop to output the required number of records

2. If the recordset field contains spaces or is a reserved word, the field must be enclosed in [ ] when quoting;

Browse records through the Recordset object

1. The Recordset record set object provides 4 methods to browse records

2. The BOF and EOF attributes respectively record whether the pointer is at the beginning and end of the file;

If the recordset pointer points to a record, both BOF and EOF are false;

'Add a button event of the form component: browse to the next record

Priavte Sub ComomndNext_Click()
     rsDemo.MoveNext
     If rsDemo。EOF Then
          rsDemo.MoveFirst
     End If
End Sub

3. LockType of the Recordset object The attribute defaults to adLockReadOnly (read-only)

Edit data through the Recordset object

1, add records using the AddNew method

1) Call The AddNew method of the record set generates an empty record

2) Assign values ​​to each field of the empty record;

3) Use the record set Update method to update and maintain new records;

' Add a record button event, assuming that the rsDemo record set has fields 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. Use the Update method to modify the record

1) Find and Move the recordset pointer to the record that needs to be modified;

2) Modify the values ​​of each field in the record;

3) Use the recordset Update method to update and maintain new records;

'Modify all values ​​1 of the Age field in the record set;

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. Use the Delete method to delete records

1) Move the record set pointer to the record that needs to be deleted;

2) Use the Delete method to delete the current record;

3) Designate a record as the current record

'删除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

Note: After a record is deleted, Access will not automatically download it. When a record becomes the current record, use the MoveNext method to locate the record set pointer to the last record;

Use SQL commands through the Command/DoCmd object

Access provides the DoCmd object, which RunSOL method can use SQL commands in VBA;

DoCmd.RunSQL "SQL命令"

or:

     Dim s AS String
     s = "SQL命令"
     DoCmd.RunSQL s

1. Define data

1) Create data table

Format: Create Table table name (field name data type...)

For example: DoCmd.RunSQL "Create Table graduate student (name text(6), age byte, admission date date)"

2) Add field

Format: Alter Table table name Add field name data type

 如：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文件名）。

The above is the detailed content of How to use ADO technology to operate access database. For more information, please follow other related articles on the PHP Chinese website!