excelvba与数据库access整合笔记

笔记制作：加肥猫 Email: QQ:403479502 20:0021:00 在线1-1 利用 DAO 创建数据库和数据表1. 首先建立对 DAO 对象库 Microsoft DAO3.6 Object Library 的引用.在 VBA 界面下:工具- 引用,选中”Microsoft DAO3.6 Object Library”2. 代码:Public Sub1_1()Dim myDb As DAO.Database 定义 DAO 的 Database(数据库)对象变量Dim myTbl As DAO.TableDef 定义 DAO 的 TableDef(数据表)对象变量Dim myData As String 定义数据库名称变量Dim myTable As String 定义数据表名称变量设置要创建的数据库名称（包括完整路径）myData=ThisWorkbook.Path pwd=12345”)1-2 利用 ADOX 创建数据库和数据表：引用：microsoft ADO Ext.2.X for DDL and Security代码：public sub1_2()dim mycat as new adox.catalog 定义 ADOX 的 Catalog 对象变量dim mytbl as new table 定义 table 对象变量dim mydata as string 定义数据库名称变量dim mytable as string 定义数据表名称变量设置要创建的数据库名称（包括完整路径）mydata=thisworkbook.path data source=” data source=” Data source=” pwd=12345”)1-5 在已有的数据库中创建数据表（ADOX）引用：microsoft ADO Ext.2.x for ddl and security代码：public sub1_5()Dim mycat as new adox.catalog 定义 ADOX 的 catalog 对象变量dim mytbl as new table 定义 table 对象变量dim mydata as string 定义数据库名称变量dim mytable as string 定义数据表名称变量设置数据库名称（包括完整路径）mydata=thisworkbook.path ”_”_“ _笔记制作：加肥猫 Email: QQ:403479502 20:0021:00 在线& “ data source=“ & myData删除已经存在的数据表On Error Resume NextmyCat.Tables.Delete ws.Range(“B1“).ValueOn Error GoTo 0建立索引myIdx.Name = “PrimaryKey“myIdx.PrimaryKey = True开始根据工作表的数据创建数据表With myTable.Name = ws.Range(“B1“).ValueFor i = 4 To ws.Range(“A65536“).End(xlUp).RowSet myColumn = New ColumnWith myColumn.Name = ws.Cells(i, 1).Value.Type = GetConstNo(ws.Cells(i, 2).Value)If ws.Cells(i, 3).Value 0 Then.DefinedSize = ws.Cells(i, 3).Value.Attributes = adColNullableEnd IfEnd With.Columns.Append myColumnIf ws.Cells(i, 4).Value = “是“ ThenmyIdx.Columns.Append ws.Cells(i, 1).ValueEnd IfNextEnd With将表定义进行保存myCat.Tables.Append myTablemyTable.Indexes.Append myIdx弹出信息MsgBox “数据表 创建成功！“, _vbOKOnly + vbInformation, “创建数据表“关闭连接，并释放变量Set ws = NothingSet myIdx = NothingSet myTable = NothingSet myCat = NothingEnd Sub将工作表中定义的数据类型（字符串型）转换为字段类型 VBA 常量，即编制一个自定义函数 GetConstNoFunction GetConstNo(myStr As String) As IntegerSelect Case myStr笔记制作：加肥猫 Email: QQ:403479502 20:0021:00 在线Case “adBigInt“: GetConstNo = 20Case “adBinary“: GetConstNo = 128Case “adBoolean“: GetConstNo = 11Case “adBSTR“: GetConstNo = 8Case “adChapter“: GetConstNo = 136Case “adChar“: GetConstNo = 129Case “adCurrency“: GetConstNo = 6Case “adDate“: GetConstNo = 7Case “adDBDate“: GetConstNo = 133Case “adDBTime“: GetConstNo = 134Case “adDBTimeStamp“: GetConstNo = 135Case “adDecimal“: GetConstNo = 14Case “adDouble“: GetConstNo = 5Case “adEmpty“: GetConstNo = 0Case “adError“: GetConstNo = 10Case “adFileTime“: GetConstNo = 64Case “adGUID“: GetConstNo = 72Case “adIDispatch“: GetConstNo = 9Case “adInteger“: GetConstNo = 3Case “adIUnknown“: GetConstNo = 13Case “adLongVarBinary“: GetConstNo = 205Case “adLongVarChar“: GetConstNo = 201Case “adLongVarWChar“: GetConstNo = 203Case “adNumeric“: GetConstNo = 131Case “adPropVariant“: GetConstNo = 138Case “adSingle“: GetConstNo = 4Case “adSmallInt“: GetConstNo = 2Case “adTinyInt“: GetConstNo = 16Case “adUnsignedBigInt“: GetConstNo = 21Case “adUnsignedInt“: GetConstNo = 19Case “adUnsignedSmallInt“: GetConstNo = 18Case “adUnsignedTinyInt“: GetConstNo = 17Case “adUserDefined“: GetConstNo = 132Case “adVarBinary“: GetConstNo = 204Case “adVarChar“: GetConstNo = 200Case “adVariant“: GetConstNo = 12Case “adVarNumeric“: GetConstNo = 139Case “adVarWChar“: GetConstNo = 202Case “adWChar“: GetConstNo = 130Case Else: GetConstNo = -1End SelectEnd Function笔记制作：加肥猫 Email: QQ:403479502 20:0021:00 在线1-11 利用工作表数据创建数据表（ADO+SQL）引用：microsoft activeX data objects 2.X libraryA B C D1数 据 表 名 称 期 中 成 绩23 字 段 名 称 字 段 类 型 字 段 大 小 是 否 索 引4学 号 adVarWChar 10 是5姓 名 adVarWChar 66性 别 adVarWChar 17班 级 adVarWChar 108数 学 adSingle9语 文 adSingle10物 理 adSingle11化 学 adSingle12英 语 adSingle13总 分 adSingle14考 试 日 期 adDate代码：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 StringmyData = ThisWorkbook.Path & “学生成绩管理.mdb“ 指定数据库文件判断是否有保存数据表资料的工作表存在On Error Resume Next Set ws = Worksheets(“数据表设计“)On Error GoTo 0If ws Is Nothing ThenMsgBox “没有数据表资料存在！“, vbCritical, “警告“Exit SubEnd Ifws.Activate建立与数据库的连接Set cnn = New ADODB.ConnectionWith cnn笔记制作：加肥猫 Email: QQ:403479502 20:0021:00 在线.Provider = “microsoft.jet.oledb.4.0“.Open myDataEnd With删除已经存在的数据表On Error Resume NextSQL = “drop table “ & ws.Range(“B1“).ValueSet 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).RowSQL = SQL & ws.Cells(i, 1).Value & “ “ & ws.Cells(i, 2).ValueIf ws.Cells(i, 3).Value 0 ThenSQL = SQL & “(“ & ws.Cells(i, 3).Value & “)“End IfIf ws.Cells(i, 4).Value = “是“ ThenSQL = SQL & “ primary key“End IfSQL = SQL & “,“NextSQL = Left(SQL, Len(SQL) - 1) & “)“创建数据表Set rs = cnn.Execute(SQL)MsgBox “数据表创建成功！“, _vbOKOnly + vbInformation, “创建数据表“关闭连接，并释放变量cnn.CloseSet ws = NothingSet rs = NothingSet cnn = NothingEnd Sub1-12 利用工作表数据创建数据表（DAO）引用：microsoft DAO 3.6 object library代码：Public Sub1_12()Dim myDb As DAO.Database 定义 database 变量笔记制作：加肥猫 Email: QQ:403479502 20:0021:00 在线Dim myTable As DAO.TableDef 定义 tabledef 变量Dim myIndex As DAO.Index 定义 index 变量Dim ws As Worksheet 定义 worksheet 变量Dim i As LongDim myData As StringmyData = ThisWorkbook.Path & “学生成绩管理.mdb“ 指定数据库文件判断是否有保存数据表资料的工作表存在On Error Resume NextSet ws = Worksheets(“数据表设计“)On Error GoTo 0If ws Is Nothing ThenMsgBox “没有数据表资料存在！“, vbCritical, “警告“Exit SubEnd Ifws.Activate建立与数据库的连接Set myDb = OpenDatabase(myData)删除已经存在的数据表On Error Resume NextmyDb.TableDefs.Delete Range(“B1“).ValueOn Error GoTo 0创建数据表Set myTable = myDb.CreateTableDef(Range(“B1“).Value)创建索引Set myIndex = myTable.CreateIndex(“PrimaryKey“)myIndex.Primary = True开始创建数据表字段等For i = 4 To Range(“A65536“).End(xlUp).RowWith myTable添加字段.Fields.Append .CreateField(Cells(i, 1).Value, _GetConstNo(Cells(i, 2).Value), Cells(i, 3).Value)对文本型字段设置是否允许零长度字符串If Cells(i, 2).Value = “dbText“ ThenIf Cells(i, 4).Value = True Then.Fields(Cells(i, 1).Value).AllowZeroLength = TrueEnd IfEnd If笔记制作：加肥猫 Email: QQ:403479502 20:0021:00 在线设置是否为必填字段If Cells(i, 5).Value = True Then.Fields(Cells(i, 1).Value).Required = TrueElse.Fields(Cells(i, 1).Value).Required = FalseEnd If设置索引 If Cells(i, 6).Value = “是“ ThenmyIndex.Fields.Append myIndex.CreateField(Cells(i, 1).Value)End IfEnd WithNext I将数据表定义保存到数据库myTable.Indexes.Append myIndexmyDb.TableDefs.Append myTable弹出信息MsgBox “数据表创建成功！“, _vbOKOnly + vbInformation, “创建数据表“关闭数据库myDb.Close设置变量Set ws = NothingSet myIndex = NothingSet myTable = NothingSet myDb = NothingEnd Sub将工作表中定义的字段类型字符串转换为字段类型 VBA 常量的函数Function GetConstNo(myStr As String) As IntegerSelect Case myStrCase “dbBoolean“: GetConstNo = 1Case “dbByte“: GetConstNo = 2Case “dbInteger“: GetConstNo = 3Case “dbLong“: GetConstNo = 4Case “dbCurrency“: GetConstNo = 5Case “dbSingle“: GetConstNo = 6Case “dbDouble“: GetConstNo = 7Case “dbDate“: GetConstNo = 8Case “dbBinary“: GetConstNo = 9Case “dbText“: GetConstNo = 10Case “dbLongBinary“: GetConstNo = 11Case “dbMemo“: GetConstNo = 12笔记制作：加肥猫 Email: QQ:403479502 20:0021:00 在线Case “dbGUID“: GetConstNo = 15Case “dbBigInt“: GetConstNo = 16Case “dbVarBinary“: GetConstNo = 17Case “dbChar“: GetConstNo = 18Case “dbNumeric“: GetConstNo = 19Case “dbDecimal“: GetConstNo = 20Case “dbFloat“: GetConstNo = 21Case “dbTime“: GetConstNo = 22Case “dbTimeStamp“: GetConstNo = 23Case Else: GetConstNo = -1End SelectEnd Function1-13 利用已有的数据表创建新数据表（ADO）引用：microsoft activeX data objects 2.x libraryPublic Sub1_13()On Error GoTo hhhDim 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 myDataEnd With设置创建数据表的 SQL 语句SQL = “select * into “ & myTable & “ from “ & myOldTable利用 Execute 方法创建数据表笔记制作：加肥猫 Email: QQ:403479502 20:0021:00 在线Set rs = cnn.Execute(SQL)关闭数据库连接，并释放变量cnn.CloseSet myCat = NothingSet rs = NothingSet cnn = Nothing弹出信息MsgBox “数据表 创建成功！“, _vbOKOnly + vbInformation, “创建数据表“Exit Subhhh:MsgBox “创建数据表出现错误!“ & vbCrLf _& “错误为:“ & Err.Description, vbCritical, “错误“End Sub扩展：利用 ADOX 来创建结构与现有数据表完全相同的空白数据表public sub1_13_1()on error goto hhhdim 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 stringmydata=thisworkbook.path & “学生成绩管理.mdb” 设置数据库名称mytable=”期中成绩” 设置要创建的数据表名称myoldtable=”期末成绩” 设置已有的数据表名称建立与建数据库的连接with vider=”microsoft.jet.oledb.4.0”.open mydataend with创建记录集sql=”select top 1*from” & myoldtableset rs=cnn.execute(sql)创建新的空的数据表笔记制作：加肥猫 Email: QQ:403479502 20:0021:00 在线mycat.activeconnection==mytablefor I=0 to rs.fieldas.count-1with rs.fields(i)mytbl.columns.append .name, .type, .definedsizeend withnext Imycat.tables.append mytbl弹出信息msgbox “数据表 创建成功！”,vbinformation,” 创建数据表”goto xxxhhh:msgbox”创建数据表出现错误！” & vbcrlf_& “错误为： ” & err.description,vbcritical,”错误”xxx:关闭数据库连接，并释放变量cnn.closeset rs=nothingset cnn=nothingset mycat=nothingset mytbl=nothingend sub整段代码说明：上例首先创建一个现有数据表的查询数据集（仅查询第一条记录） ，然后利用数据集各个字段的 name 属性、type 属性和 definedsize 属性获取字段的名称，类型和数据长度，从而创建一个结构完全相同的空白新表。1-14 利用已有的数据表创建新数据表（DAO）引用：microsoft DAO 3.6 object libraryPublic Sub1_14()On Error GoTo hhhDim myDb As DAO.Database 定义 DAO 的 Database(数据库)对象变量Dim myData As String 定义数据库名称变量Dim myTable As String 定义新数据表名称变量Dim myOldTable As String 定义现有的数据表名称变量Dim SQL As String 设置数据库名称（包括完整路径）myData = ThisWorkbook.Path & “学生成绩管理.mdb“笔记制作：加肥猫 Email: QQ:403479502 20:0021:00 在线myTable = “期中成绩“ 设置要创建的数据表名称myOldTable = “期末成绩 “ 设置已有的数据表名称建立与建数据库的连接Set myDb = OpenDatabase(myData)设置创建数据表的 SQL 语句SQL = “select * into “ & myTable & “ from “ & myOldTable利用 Execute 方法创建数据表myDb.Execute SQL弹出信息MsgBox “数据表创建成功！“, _vbOKOnly + vbInformation, “创建数据表“关闭数据库并释放变量myDb.CloseSet myDb = NothingExit Subhhh:MsgBox “创建数据表出现错误!“ & vbCrLf _& “错误为:“ & Err.Description, vbCritical, “错误“End Sub1-15 利用已有的数据表创建新数据表（Access）(前绑定方法引用 Access 对象库)Public Sub1_15()Dim appAccess As New Access.ApplicationDim myData As String 定义数据库名称变量Dim myTable As String 定义新数据表名称变量Dim myOldTable As String 定义现有的数据表名称变量Dim SQL As String设置数据库名称（包括完整路径）myData = ThisWorkbook.Path & “学生成绩管理.mdb“myTable = “期中成绩“ 设置要创建的数据表名称myOldTable = “期末成绩 “ 设置已有的数据表名称appAccess.OpenCurrentDatabase myData 建立与数据库的连接设置创建数据表的 SQL 语句SQL = “select * into “ & myTable & “ from “ & myOldTable 利用 Docmd 对象的 RunSQL 方法创建数据表appAccess.DoCmd.RunSQL SQL弹出信息笔记制作：加肥猫 Email: QQ:403479502 20:0021:00 在线MsgBox “数据表创建成功！“, _vbOKOnly + vbInformation, “创建数据表“关闭数据库并释放变量appAccess.CloseCurrentDatabaseSet appAccess = NothingExit Subhhh:MsgBox “创建数据表出现错误!“ & vbCrLf _& “错误为:“ & Err.Description, vbCritical, “错误“End Sub2-1 检查数据表是否存在（ADO）引用：microsoft activeX data objects 2.x libraryPublic 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.ConnectionWith cnn.Provider = “microsoft.jet.oledb.4.0“ 注 1.Open mydata 注 2End With创建数据表记录集Set rs = cnn.OpenSchema(adSchemaTables)开始查询是否存在该数据表Do Until rs.EOFIf LCase(rs!table_name) = LCase(mytable) ThenMsgBox “数据表 存在！“GoTo hhhEnd If继续下一条记录rs.MoveNextLoopMsgBox “数据表 不存在！“hhh:笔记制作：加肥猫 Email: QQ:403479502 20:0021:00 在线rs.Closecnn.CloseSet rs = NothingSet cnn = NothingEnd Sub解释说明：Connection 对象：用于建立与数据源的连接，通过连接可以从应用程序中访问数据源。 （只有连接后才能使用其它对象）常用属性：1 Connectionstring 属性：指示用于建立到数据源的连接的信息2 povider 属性：用来指示 connection 对象的提供者的名称。 注 13 open 方法：用于打开到数据源的连接 注 2语法：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 语句）操作数据源。可在数据源中添加、删除和更新数据，或在表中查询数据。笔记制作：加肥猫 Email: QQ:403479502 20:0021:00 在线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.close3. updata 方法：用于保存对 recordset 对象的当前行或 record 对象的 fields 集合所做的更改。recordset.updata fields,valuesrecord.fields.updaterecord 为 recordset 中的行（即某个记录） .fields(可选):表示要修改的字段名称或序号位置。values(可选)表示新记录中字段的值。4. BOF 属性和 EOF 属性。BOF 属性表示当前记录的位置在 recordset 对象的第一个记录之前，EOF 属性表示当前记录的位置在 recordset 对象的最后一个记录之后。BOF，EOF 属性返回 boolean 值。当记录指针指向某个记录时，BOF 属性和 EOF 属性值均为 False，表示没有到达记录集的开始处和结束处。当记录指针达到记录集的开始处时，BOF 为 True,而 EOF 为 false；当记录指针达到记录集的结束处时，EOF 为True,而 BOF 为 false.如记录集中没有记录，那么 BOF 和 EOF 均为 True.5. MovefirstMovelastMovenextMoveprevious 方法移动到指定的 recordset 对象中的第一个、最后一个、下一个或上一个记录并使其成为当前记录。recordset.movefirst|movelast|movenext|moveprevious6. addnew 方法：用于创建可更新的 recordset 对象的新记录。recordset.addnew fieldlist,valuesfieldlist(可选)：为新记录中字段的单个名称，名称数组或序号位置数组；values（可选）：为新记录中字段的单人值或值的数组7 delete 方法：用于删除当前记录或记录组recordset.delete affectrecordsaffectrecords 为 affectenum 值（当前记录）用于确定 delete 方法影响的记录数目。笔记制作：加肥猫 Email: QQ:403479502 20:0021:00 在线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(“编号”)直接在记录集对象中引用