ADO 数据手册.doc

ADO 数据手册1-3 利用SQL语句创建数据库和数据表首先引用：microsoft activeX data objects 2.X library 和 microsoft ado ext.2.x for ddl and security”代码：public sub1_3()dim mycat as new adox.catalog 定义ADOX的Catalog对象变量Dim mycmd as new mand 定义Command对象变量dim mydata as string 定义数据库名称变量dim mytable as string 定义数据表名称变量dim SQL as string设置要创建的数据库名称（包括完整路径）mydata=thisworkbook.path & “学生成绩管理.mdb”设置要创建的数据表名称mytable=”期末成绩”删除已经存在的数据库文件on error resume nextkill mydataon error goto 0创建数据库文件mycat.create “provider=microsoft.jet.oledb.4.0;Data source=” & mydata设置数据库连接set mycmd.activeconnection=mycat.activeconnection设置创建数据表的SQL语句 SQL = CREATE TABLE & myTable _ & (学号 text(10),姓名 text(6),性别 text(1),班级 text(10), _ & 数学 Single,语文 Single,物理 Single,化学 Single, _ & 英语 Single,总分 Single)利用execute方法创建数据表with mycmd .commandtext=sql .execute, , adcmdtextend with释放变量set mycat=nothingset mycmd=nothing弹出信息 msgbox “创建数据库成功！” & vbcrlf & “数据库文件名为：” & mydata & vbcrlf & “数据表名称为：” & mytable & vbcrlf & “保存位置：” & thisworkbook.path,vbinformation,”创建数据库”end sub注：有两种方法来创建数据表： 利用ADODB.Command对象的commandtext属性和execute方法：dim mycmd as new mandset mycmd.activeconnection=mycat.activeconnectionwith mycmd .commandtext=SQL .execute, , adcmdtextend with 利用ADODB.Connection对象的execute方法来生成几个记录集Dim cnn as new adodb.connectiondim rs as new adodb.recordsetset cnn=mycat.activeconnectionset rs=cnn.execute(sql) 1-6 在已有的数据库中创建数据表（SQL，Command对象） 引用:microsoft activex data objects2.x library和microsoft ado ext.2.x for ddl and security 代码：public sub1_6()dim mycat as new adox.catalog 定义adox的catalog对象变量dim mycmd as new mand 定义command对象变量dim mydata as string 定义数据库名称变量dim mytable as string 定义数据表名称变量dim sql as string 设置数据库名称（包括完整路径）mydata=thisworkbook.path & “学生成绩管理.mdb”设置要创建的数据表名称mytable=”期末成绩”建立与数据库的连接mycat.activeconnection=”provider=microsoft.jet.oledb.4.0;”_ & “data source=” & mydata删除数据库中已经存在的数据表mycat.tables.delete mytable设置数据库连接set mycmd.activeconnection=mycat.activeconnection设置创建数据表的SQL语句SQL=”CREATE TABLE” & mytable_ & “(学号 text(10),姓名 text(6),性别 text(1),班级 text(10),” _ & “数学 single,语文 single,物理 single,化学 single, “_ & “英语 single,总分 single)”利用Execute方法创建数据表With mycmd .commandtext=sql .execute, , adcmdtextEnd with释放变量Set mycat=nothingSet mycmd=nothing弹出信息Msgbox “数据表创建成功！”,vbinformation,”创建数据表”End sub 1-7 在已有的数据库中创建数据表（SQL，Recordset对象） 引用：Microsoft ActiveX Data Objects 2.X library 代码：public sub1_7()dim cnn as new adodb.connection 定义connection对象变量dim rs as new adodb.recordset 定义Recordset对象变量dim mydata as string 定义数据库名称变量dim mytable as string 定义数据表名称变量dim sql as string设置数据库名称(包括完整路径)mydata=thisworkbook.path & “学生成绩管理.mdb”设置要创建的数据表名称mytable=”期末成绩”建立与数据库的连接with cnn .provider=”microsoft.jet.oledb.4.0” .open mydataend with删除数据库中已经存在的数据表SQL=”drop table” & mytableset rs=cnn.execute(sql)设置创建数据表的SQL语句sql=”create table” & mytable _ & “(学号 text(10),姓名 text(6), 性别 text(1), 班级 text(10),” _ & “数学 single, 语文 single, 物理 single, 化学 single,” _ & “英语 single, 总分 single)”利用execute方法创建数据表 set rs=cnn.execute(sql)关闭数据库 cnn.close释放变量set mycat=nothingset rs=nothingset cnn=nothing弹出信息msgbox “数据表创建成功!”, vbinformation,”创建数据表”end sub1-11 利用工作表数据创建数据表（ADO+SQL）引用：microsoft activeX data objects 2.X library代码：Public Sub1_11() Dim cnn As New ADODB.Connection 定义connection对象变量 Dim rs As New ADODB.Recordset 定义recordset对象变量 Dim ws As Worksheet 定义worksheet对象变量 Dim i As Long Dim myData As String, SQL As String myData = ThisWorkbook.Path & 学生成绩管理.mdb 指定数据库文件 判断是否有保存数据表资料的工作表存在On Error Resume Next Set ws = Worksheets(数据表设计) On Error GoTo 0 If ws Is Nothing Then MsgBox 没有数据表资料存在！, vbCritical, 警告 Exit Sub End If ws.Activate 建立与数据库的连接 Set cnn = New ADODB.Connection With cnn .Provider = microsoft.jet.oledb.4.0 .Open myData End With 删除已经存在的数据表 On Error Resume Next SQL = drop table & ws.Range(B1).Value Set rs = cnn.Execute(SQL) On Error GoTo 0 生成创建数据表的SQL语句字符串 SQL = create table & ws.Range(B1).Value & ( For i = 4 To ws.Range(A65536).End(xlUp).Row SQL = SQL & ws.Cells(i, 1).Value & & ws.Cells(i, 2).Value If ws.Cells(i, 3).Value 0 Then SQL = SQL & ( & ws.Cells(i, 3).Value & ) End If If ws.Cells(i, 4).Value = 是 Then SQL = SQL & primary key End If SQL = SQL & , Next SQL = Left(SQL, Len(SQL) - 1) & ) 创建数据表 Set rs = cnn.Execute(SQL) MsgBox 数据表创建成功！, _ vbOKOnly + vbInformation, 创建数据表 关闭连接，并释放变量cnn.Close Set ws = Nothing Set rs = Nothing Set cnn = NothingEnd Sub1-13 利用已有的数据表创建新数据表（ADO）引用：microsoft activeX data objects 2.x libraryPublic Sub1_13() On Error GoTo hhh Dim cnn As New ADODB.Connection 定义connection对象变量 Dim rs As New ADODB.Recordset 定义recordset对象变量 Dim myData As String 定义数据库名称变量 Dim myTable As String 定义新数据表名称变量 Dim myOldTable As String 定义现有的数据表名称变量 Dim SQL As String 设置数据库名称（包括完整路径） myData = ThisWorkbook.Path & 学生成绩管理.mdb 设置要创建的数据表名称 myTable = 期中成绩 设置已有的数据表名称 myOldTable = 期末成绩 建立与建数据库的连接 With cnn .Provider = Microsoft.Jet.OLEDB.4.0 .Open myData End With 设置创建数据表的SQL语句 SQL = select * into & myTable & from & myOldTable 利用Execute方法创建数据表Set rs = cnn.Execute(SQL) 关闭数据库连接，并释放变量cnn.Close Set myCat = Nothing Set rs = Nothing Set cnn = Nothing 弹出信息MsgBox 数据表创建成功！, _ vbOKOnly + vbInformation, 创建数据表 Exit Subhhh: MsgBox 创建数据表出现错误! & vbCrLf _ & 错误为: & Err.Description, vbCritical, 错误End Sub扩展：利用ADOX来创建结构与现有数据表完全相同的空白数据表 public sub1_13_1() on error goto hhh dim cnn as new adodb.connection 定义connection对象变量 dim rs as new adodb.recordset 定义recordset对象变量 dim mycat as new adox.catalog 定义ADOX的Catalog对象变量 dim mytbl as new table 定义table对象变量 dim mydata as string 定义数据库名称变量 dim mytable as string 定义新数据表名称变量 dim myoldtable as string 定义现有的数据表名称变量 dim sql as string mydata=thisworkbook.path & “学生成绩管理.mdb” 设置数据库名称 mytable=”期中成绩” 设置要创建的数据表名称 myoldtable=”期末成绩” 设置已有的数据表名称 建立与建数据库的连接 with cnn .provider=”microsoft.jet.oledb.4.0” .open mydata end with 创建记录集 sql=”select top 1*from” & myoldtable set rs=cnn.execute(sql) 创建新的空的数据表 mycat.activeconnection=cnn =mytable for I=0 to rs.fieldas.count-1 with rs.fields(i) mytbl.columns.append .name, .type, .definedsize end with next I mycat.tables.append mytbl 弹出信息 msgbox “数据表创建成功！”,vbinformation,”创建数据表” goto xxxhhh: msgbox”创建数据表出现错误！” & vbcrlf_ & “错误为：” & err.description,vbcritical,”错误”xxx: 关闭数据库连接，并释放变量 cnn.close set rs=nothing set cnn=nothing set mycat=nothing set mytbl=nothingend sub整段代码说明：上例首先创建一个现有数据表的查询数据集（仅查询第一条记录），然后利用数据集各个字段的name属性、type属性和definedsize属性获取字段的名称，类型和数据长度，从而创建一个结构完全相同的空白新表。2-1 检查数据表是否存在（ADO） 引用：microsoft activeX data objects 2.x library Public Sub2_1() Dim mydata As String 定义数据库名称变量 Dim mytable As String 定义数据表名称变量 Dim cnn As ADODB.Connection 定义ADO的connection对象变量Dim rs As ADODB.Recordset 定义ADO的Recordset对象变量指定数据库名称 mydata = ThisWorkbook.Path & 客户管理.mdb 指定要查询的数据表名称mytable = 发票管理建立与数据库的连接 Set cnn = New ADODB.Connection With cnn .Provider = microsoft.jet.oledb.4.0 注 .Open mydata 注End With创建数据表记录集 Set rs = cnn.OpenSchema(adSchemaTables) 开始查询是否存在该数据表Do Until rs.EOF If LCase(rs!table_name) = LCase(mytable) Then MsgBox 数据表 存在！ GoTo hhh End If 继续下一条记录 rs.MoveNext Loop MsgBox 数据表 不存在！hhh: rs.Close cnn.Close Set rs = Nothing Set cnn = NothingEnd Sub 解释说明：Connection对象：用于建立与数据源的连接，通过连接可以从应用程序中访问数据源。（只有连接后才能使用其它对象）常用属性：1 Connectionstring属性：指示用于建立到数据源的连接的信息2 povider属性：用来指示connection对象的提供者的名称。注3 open方法：用于打开到数据源的连接 注语法：cnn.open connectionstring,userID,Password,Options 连接信息的字符串，建立 ， 密码， 连接时要使用的用户名称字符串4 Close方法：用于关闭打开的数据库连接5 State属性：用于检查Connection对象是打开的还是关闭的，或是否处于连接状态。返回值：adStateOpen:对象已打开 adStateClosed:对象已关闭 adStateConnecting:正在连接6 OpenSchema方法：允许用户打开包含模式信息的数据集Recordset.Recordset将以只读、静态游标的模式打开set recordset=connection.openschema(Querytype,criteria,schemaID)参数Querytype指定任意的SchemaEnum值，以表示要运行的模式查询的类型常见类型：要检查数据表时，值为adschematables要检查数据表中的字段时，值为adschemacolumns要检查数据表中的索引时，值为adschemaindexes要检查数据表中的主键时，值为adschemaPrimarykeys参数Criteria为可选参数，用于限制模式查询的结果（略）参数SchemaID为OLEDB规范未定义的提供者模式查询的GUID（略） Command 对象：发出命令（例如SQL语句）操作数据源。可在数据源中添加、删除和更新数据，或在表中查询数据。 Recordset对象：只代表记录集，是基于某个连接的表或Command对象的执行结果，在ADO中，所有对数据源的操作几乎都是由其完成。其功能为可以浏览记录，修改，添加，删除特定记录。1 Open方法：打开一个recordset对象。语法：recordset.open source,activeconnection,cursortype,locktype,optionsrecordset代表recordset对象source(可选):可以是command对象、SQL语句、数据库的表名等。Activeconnection（可选）:variant或string型，variant用于connection对象变量的名称；string包含connection string参数。cursortype（可选）：用于指定当打开recordset时提供者应使用的游标类型，默认为adopenforwardonly.locktype(可选)：用于确定打开recordset时提供者应使用的锁定类型。如要对数据库进行修改，删除，更新等操作，则locktype参数必须设定为adlockoptimistic,默认为adlockreadonly(只读)。2 close方法：关闭recordset对象recordset.close1. updata方法：用于保存对recordset对象的当前行或record对象的fields集合所做的更改。recordset.updata fields,valuesrecord.fields.updaterecord为recordset中的行（即某个记录）.fields(可选):表示要修改的字段名称或序号位置。values(可选)表示新记录中字段的值。2. BOF属性和EOF属性。BOF属性表示当前记录的位置在recordset对象的第一个记录之前，EOF属性表示当前记录的位置在recordset对象的最后一个记录之后。BOF，EOF属性返回boolean值。当记录指针指向某个记录时，BOF属性和EOF属性值均为False，表示没有到达记录集的开始处和结束处。当记录指针达到记录集的开始处时，BOF为True,而EOF为false；当记录指针达到记录集的结束处时，EOF为True,而BOF为false.如记录集中没有记录，那么BOF和EOF均为True.3. MovefirstMovelastMovenextMoveprevious方法移动到指定的recordset对象中的第一个、最后一个、下一个或上一个记录并使其成为当前记录。recordset.movefirst|movelast|movenext|moveprevious4. addnew方法：用于创建可更新的recordset对象的新记录。recordset.addnew fieldlist,valuesfieldlist(可选)：为新记录中字段的单个名称，名称数组或序号位置数组；values（可选）：为新记录中字段的单人值或值的数组7 delete方法：用于删除当前记录或记录组recordset.delete affectrecordsaffectrecords为affectenum值（当前记录）用于确定delete方法影响的记录数目。8. LockType属性：指示recordset对象中使用的游标的类型，可设置或返回cursortypeenum值，默认值为adlockreadonly(只读记录),如要对记录进行修改，删除，添加等操作，则需要将其设为adlockoptionistic或adlockpessimistic9. Fields集合：表示包含recordset或record对象的所有field对象，每个field对象对应于recordset中的一列，如fields(0)表示recordset中的第一列（第一个字段，fields(1)表示recordset中的第二列（第二个字段）类推；引用字段方法：使用记录集的fields(n)属性，其中n是一个记录中字段从左到右排列序号，第一个字段的序号为0，第二个字段的序号为1，.类推；使用记录集的fields(n)属性，使用字段名，即fields(“字段名”),如fields(“编号”)直接在记录集对象中引用字段名称，如rs!姓名直接在记录集对象中引用字段名称，使用（），如rs(“编号”).10. recordcount属性：返回long值，表示recordset中的记录数目。 ADO访问数据库的基本步聚：1先声明connection对象变量和recordset对象变量2建立与数据库的连接，通过声明connection对象来完成的。dim cnn as adodb.connection 声明一个connection对象变量cnnset cnn=new adodb.connection 初始化该对象变量，即创建一个新的connection对象使用open方法来建立与数据库的连接with cnn .provider=”microsoft.jet.oledb.4.0” .open thisworkbook.path & “客户管理.mdb”end with 3. 在建立与数据库的连接后，声明并初始化一个新的recordset对象，打开该对象，就可以使用recordset对象的有关属性和方法来查询、浏览、编辑、删除记录。dim rs as adodb.recordsetset rs=new adodb.recordsetrs.open SQL语句或数据表名，cnn，adopenkeyset，adlockoptimistic 4. 当记录集使用完毕，应该将其关闭，以便从内存中删除recordset对象和connection对象。 rs.close cnn.close set rs=nothing set cnn=nothing 拓展： 使用recordset对象的find方法来查找某个数据表是否存在：用于在recordset记录集中搜索满足指定条件的行。语法： find (criteria,skiprows,searchdirection,start) criteria指定用于搜索的列名、比较操作符和值。 skiprows(可选)默认值为0，指定当前行或start书签的行偏移量以开始搜索，默认从当前行开始搜索； searchdirection(可选):指定搜索应从当前开始，还是从搜索方向的下一个有效行开始，如值为adsearchforward，不成功的搜索将在recordset的结尾处停止。如值为adsearchbackward，不成功的搜索将在recordset的开始处停止。 start(可选)用于标记搜索的开始位置。 Public Sub2_1_1() Dim mydata As String Dim mytable As String Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset mydata = ThisWorkbook.Path & 客户管理.mdb mytable = 发票管理 Set cnn = New ADODB.Connection With cnn .Provider = microsoft.jet.oledb.4.0 .Open mydata End With Set rs = cnn.OpenSchema(adSchemaTables) rs.Find TABLE_NAME= & mytable & If rs.EOF Then MsgBox 数据表 不存在！ Else MsgBox 数据表 存在！ End If rs.Close cnn.Close Set rs = Nothing Set cnn = NothingEnd Sub实例2-5 获取数据库中所有表的名称和类型（ADO） 引用：microsoft activex data objects 2.x library Public Sub2_5() 将access库中的表信息读入到excel文件中 Dim i As Integer Dim mydata As String 定义数据库名称变量 Dim rs As New ADODB.Recordset 定义ADODB的recordset变量 mydata = ThisWorkbook.Path & 客户管理.mdb 指定数据库文件 建立与数据库的连接Set cnn = New ADODB.Connection With cnn .Provider = microsoft.jet.oledb.4.0 .Open mydataEnd With准备工作表 Cells.Clear i = 2 Range(A1:B1) = Array(表名称, 表类型) 开始获取数据库中所有表的名称和类型Set rs = cnn.OpenSchema(adSchemaTables) Do Until rs.EOF Cells(i, 1) = rs!table_name 获取表名称 Cells(i, 2) = rs!table_type 获取表类型 继续下一条记录rs.MoveNext i = i + 1Loop关闭记录集和数据库 rs.Closecnn.Close释放变量 Set rs = Nothing Set cnn = NothingEnd Sub实例2-8 获取数据库中所有数据表名称（ADO） 引用：microsoft activex data objects 2.x library Public Sub2_8() Dim i As Integer Dim mydata As String 定义数据库名称变量 Dim cnn As ADODB.Connection 定义adodb的connection对象变量 Dim rs As ADODB.Recordset 定义adodb的recordset对象变量 mydata = ThisWorkbook.Path & 客户管理.mdb 指定数据库文件 建立与数据库的连接Set cnn = New ADODB.Connection With cnn .Provider = microsoft.jet.oledb.4.0 .Open mydataEnd With准备工作表 Cells.Clear i = 2 Range(A1) = 数据表名称 开始获取数据库中所有数据表的名称和类型Set rs = cnn.OpenSchema(adSchemaTables) Do Until rs.EOF If rs!TABLE_TYPE = TABLE Then 如表的类型为TABLE,则 注：TABLE必须为大写 或使用下面的语句 If Left(rs!TABLE_NAME, 4) MSys Then MSys区分大小写，即在数据库中表名为MSys*的表名 Cells(i, 1) = rs!TABLE_NAME i = i + 1 End If 下一条记录 rs.MoveNextLoop关闭记录集和数据库 rs.Closecnn.Close释放变量 Set rs = Nothing Set cnn = NothingEnd Sub实例2-12 检查某字段是否存在（ADO） 引用：microsoft activeX data objects 2.x library Public Sub2_12() Dim mydata As String 定义数据库字符串变量 Dim mytable As String 定义数据表字符串变量 Dim mycolumn As String 定义字段字符串变量 Dim cnn As ADODB.Connection 定义connection对象变量 Dim rs As ADODB.Recordset 定义recordset对象变量 mydata = ThisWorkbook.Path & 客户管理.mdb 指定数据库 mytable = 客户信息 指定数据表mycolumn = 客户名称 指定字段名称建立与数据库的连接 Set cnn = New ADODB.Connection With cnn .Provider = microsoft.jet.oledb.4.0 .Open mydataEnd With创建字段记录集Set rs = cnn.OpenSchema(adSchemaColumns) 设置为adschemacolumns就是获取字段信息 开始检查该字段是否存在Do Until rs.EOF If LCase(rs!column_name) = LCase(mycolumn) Then MsgBox 在数据表中存在字段！ GoTo hhh End If rs.MoveNext Loop MsgBox 在数据表中不存在字段！hhh: rs.Close cnn.Close Set rs = Nothing Set cnn = NothingEnd Sub 扩展：使用connection对象的find方法来查找某个字段是否存在Public Sub2_12_1() Dim mydata As String Dim mytable As String Dim mycolumn As String Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset mydata = ThisWorkbook.Path & 客户管理.mdb mytable = 客户信息 mycolumn = 客户名称 Set cnn = New ADODB.Connection With cnn .Provider = microsoft.jet.oledb.4.0 .Open mydata End With Set rs = cnn.OpenSchema(adSchemaColumns) rs.Find column_name= & mycolumn & If rs.EOF Then MsgBox 在数据表中不存在字段！ Else MsgBox 在数据表中存在字段！ End If rs.Close cnn.Close Set rs = Nothing Set cnn = NothingEnd Sub实例2-16 获取数据库中某数据表的所有字段信息（ADO） 引用：microsoft activeX data objects 2.x library Public Sub2_16() Dim mydata As String 定义数据库字符串变量 Dim mytable As String 定义数据表字符串变量 Dim cnn As ADODB.Connection 定义connection对象变量 Dim rs As ADODB.Recordset 定义recorset对象变量 Dim myField As ADODB.Field 定义field对象变量 Dim FieldType As String, FieldLong As Integer mydata = ThisWorkbook.Path & 客户管理.mdb 指定数据库mytable = 客户信息 指定数据表建立与数据库的连接 Set cnn = New ADODB.Connection With cnn .Provider = microsoft.jet.oledb.4.0 .Open mydataEnd With查询数据表 Set rs = New ADODB.Recordset rs.Open mytable, cnn, adOpenKeyset, adLockOptimistic 准备工作表ActiveSheet.Cells.Clear ActiveSheet.Range