ado封装

DataBase

Option ExplicitPrivate dataBase As StringPrivate username As StringPrivate password As StringPrivate conn As ConnectionPrivate sql As StringPrivate serverip As StringPrivate rs As RecordsetPublic Property Let letServerip(ip As String)serverip = ipEnd PropertyPublic Property Let letDataBase(strDataBase As String)dataBase = strDataBaseEnd PropertyPublic Property Let letSql(s As String)sql = sDebug.Print sqlEnd PropertyPublic Property Let letUsername(u As String)username = uEnd PropertyPublic Property Let letPassword(p As String)password = pEnd PropertyPublic Property Get getConnection()Set getConnection = connEnd Property'获取Excel数据库Public Sub getExcelConnection()Set conn = CreateObject("adodb.connection")With conn.Provider = "Microsoft.ace.OLEDB.12.0".ConnectionString = "Data Source=" & dataBase & ";Extended Properties=Excel 12.0".OpenEnd WithEnd Sub'获取Mysql数据库Public Sub getMysqlConnection()Set conn = CreateObject("adodb.connection")With conn.ConnectionString = "DRIVER=MySQL ODBC 5.3 Unicode Driver;SERVER=" & serverip & ";Database=" & dataBase & ";Uid=" & username & ";Pwd=" & password.OpenEnd WithEnd Sub'获取SqlServver数据库Public Sub getSqlServerConnection()Set conn = CreateObject("adodb.connection")With conn.ConnectionString = "Provider=SQLOLEDB;Initial Catalog=" & dataBase & ";User ID=" & username & ";Password=" & password & ";Data Source=" & serverip'Debug.Print "Provider=SQLOLEDB;Initial Catalog=" & dataBase & ";User ID=" & user & ";Password=" & password & ";Data Source=" & serverip.OpenEnd WithEnd Sub'获取Access数据库Public Sub getAccessConnection()Set conn = CreateObject("adodb.connection")With conn.Provider = "Microsoft.ace.OLEDB.12.0".ConnectionString = "Data Source=" & dataBase.OpenEnd WithEnd Sub

DataBaseUtil

Option Explicit'操作数据库Private record As RecordsetPrivate dataBase As New dataBasePrivate sql As StringPrivate conn As ConnectionPrivate Const sqlServerUsername = "sa"Public Property Let letDataBase(db As dataBase)Set dataBase = dbEnd PropertyPublic Property Let letSql(s)sql = sEnd PropertyPublic Property Get getRecordSet()If checkRecordNotIsColse() ThenSet getRecordSet = recordEnd IfEnd Property'初始化方法Private Sub init()loadConnectionloadRecordSetdestroyDataBaseEnd Sub'CursorType游标类型:'Const adOpenForwardOnly = 0'前向游标，为缺省游标，提供最快的运行性能。用它打开recordset，从对至尾顺序取得所有结果。它不支持向后滚动，只允许在结果间单向移动。''Const adOpenKeyset = 1'静态游标，反映第一次打开游标时表中数据的状态，游标无法查明底层表中的数据行是否更新过、删除过或添加了新的数据。不过与只能前移的洲标不同，静态游标可以在结果间前后滚动。''Const adOpenDynamic = 2'键盘驱动的游标，可以查询表中底层数据行的某些变化，但不是全部。它特别是可以准确反映数据是否更新过。但它不能查明其它用户是否曾删除过数据行（删除掉的数据行在recordset中会留下空洞）。键盘驱动的游标支持在结果间前后滚动。''Const adOpenStatic = 3'动态游标，是最丰富的游标类型。游标打开时可以查询其他用户对表的任何改动，而且支持滚动。'LockType加锁类型:'Const adLockReadOnly = 1'缺省的上锁类型，只读方式上锁允许多个用户同时读取同样的数据，但不能改变数据。''Const adLockPessimistic = 2'以悲观上锁方式打开数据对象。该方式假定在你编辑记录时会有其它用户访问数据。此时一旦你开始编辑记录，其它用户就不能访问该数据。''Const adLockOptimistic = 3'以乐观上锁方式打开数据对象。该方式假定在你编辑记录时不会有其它用户访问数据。在完成改变之前，其它用户不能访问该记录。''Const adLockBatchOptimistic = 4'执行多行批处理更新时使用这种类型'Options参数:'Options参数标明用来打开记录集的命令字符串的类型?告诉ADO被执行的字符串内容的有关信息有助于高效地执行该命令字符串?''adCMDTable?被执行的字符串包含一个表的名字?'adCMDText?被执行的字符串包含一个命令文本?'adCMDStoredProc?被执行的字符串包含一个存储过程名?'adCMDUnknown?不指定字符串的内容? (这是缺省值?)'Recordset.State''AdStateClosed 默认，指示对象是关闭的。'adStateOpen 指示对象是打开的?'AdStateConnecting 指示 Recordset 对象正在连接。'AdStateExecuting 指示 Recordset 对象正在执行命令。'AdStateFetching 指示 Recordset 对象的行正在被读取。''VB中的常数 , 可以参考一下:'adStateClosed = 0'adStateOpen = 1'adStateConnecting = 2'adStateFetching = 8'adStateExecuting = 4'Recordset.Find Criteria, [SkipRows], [SearchDirection], [Strat]'criteria:强制性Criteria 参数是定义搜索条件的字符串。该字符串必须包含一个字段（列）名称，一个比较运算符和一个搜索值。'SkipRows;可选的SkipRecords 参数是一个很长的值，它指定在开始搜索之前要跳过当前记录之外的多少条记录。默认值为零，这意味着搜索从当前记录开始。'SearchDirection可以是adSearchForward，表示向前搜索记录；或者adSearchBackward，表示向后搜索记录。'SearchDirectionEnum:'adSearchBackward-1 从指定的起点向后搜索到第一条记录'adSearchForward 1 从指定的起点搜索到最后一条记录'Start:可选的Start参数是一个变体，可以是书签，也可以是BookmarkEnum常量之一，它指示搜索的开始位置。默认值是从当前记录开始。。'BookmarkEnum:'adBookmarkCurrent 0 默认值，从当前记录开始搜索'adBookmarkFirst 1 从第一条记录开始搜索'adBookmarkLast 2 从最后一条记录开始搜索'如果打开相应的记录，当前指针将位于匹配的记录上，如果没有找到记录，那么将位于下面两个位置中的一个：'· 如果是向前搜索，则位于记录集末尾位置的后面，EOF被设置为True。'· 如果是向后搜索，则位于记录集开始位置的前面，BOF被设置为True。'使用书签保存位置'如果没有找到相应的记录，记录的重新定位可以由书签轻松解决，因为可以为当前位置制作书签，如果在查找记录过程中没有找到所需的记录，那么再移回到上次保存的位置。'dim varBookMark as Variant'varBookMark = rsChantList.Bookmark'rsChantList.MoveFirst'rsChantList.Find "ChantName = 'Oma' "'If (rsChantList.BOF = True) Or (rsChantList.EOF = True) Then' MsgBox "Record not found"' rsChantList.Bookmark = varBookMark'End If'/Charles_ke/article/details/88714724'加载记录集Private Sub loadRecordSet()Set record = New Recordsetrecord.CursorLocation = adUseClientBatch 'adUseClientBatchrecord.Open sql, conn, CursorType:=adOpenDynamic, LockType:=adLockBatchOptimistic 'adLockBatchOptimisticEnd Sub'检验recordSet是否不为空Function checkRecordNotIsColse()checkRecordNotIsColse = record.State <> 0End Function'加载链接Private Sub loadConnection()Set conn = dataBase.getConnectionEnd Sub'销毁dataBasePrivate Sub destroyDataBase()Set dataBase = NothingEnd Sub'根据IP后3位和密码获取ConnectionPrivate Sub getSqlServerDataBase(ipLast, password)End Sub'获取Excel为数据库Private Sub getExcelDataBase(path)With dataBase.letDataBase = path.getExcelConnectionEnd WithEnd Sub'获取mdb文件Private Sub getAccessDataBase()With dataBase.letDataBase = accessPath.getAccessConnectionEnd WithEnd Sub'获取mysql的dataBasePrivate Sub getMysqlDataBase(ipLast, dabaBaseName As String)End Sub'执行Sql获取其中数据，并默认放置在[a1]Sub extractDataInR1C1()getFieldName [a1]executeDQLSql [a2]End Sub'链接sqlServer数据库，需要传入sql语句Sub getDataBaseUtilForSqlServer(strSql, ipLast, password)getSqlServerDataBase ipLast, passwordsql = strSqlinitEnd Sub'链接ExcelSub getDataBaseUtilForExcel(strSql, path)getExcelDataBase (path)sql = strSqlinitEnd Sub'链接AccessSub getDataBaseUtilForAccess(strSql)getAccessDataBasesql = strSqlinitEnd Sub'链接mysql数据库，需要传入sql语句 和ip后3位Sub getDataBaseUtilForMysql(strSql, ipLast, dabaBaseName As String)getMysqlDataBase ipLast, dabaBaseNamesql = strSqlinitEnd Sub'添加新的淘宝asinSub saveOrUpdateAsin(sku, asin, site)If checkAsinIsExistInRecordset(asin) ThenupdateAsin sku, asin, siteElseaddNewAsinupdateAsin sku, asin, siteEnd IfEnd Sub'向记录集中添加sku和asinSub addNewAsin()record.AddNewEnd SubSub updateAsin(sku, asin, site)With record.Fields("sku") = sku.Fields("asin") = asin.Fields("site") = siteEnd WithupdateRecordSetEnd Sub'检验recordSet中是否存在asinFunction checkAsinIsExistInRecordset(asinId)record.MoveFirstrecord.Find ("asin=" & asinId)If record.EOF ThencheckAsinIsExistInRecordset = TrueElsecheckAsinIsExistInRecordset = FalseEnd IfEnd Function'单条更新Sub updateRecordSet()record.updateEnd Sub'批量更新到数据库Sub updateRecordSetBatch()record.UpdateBatchEnd Sub'关闭链接Public Sub closeConnection()If checkRecordNotIsColse() Thenrecord.CloseSet record = NothingEnd Ifconn.CloseSet conn = NothingEnd Sub'抽取查询数据到指定单元格Public Sub executeDQLSql(range As range)range.CopyFromRecordset recordEnd Sub'执行DML语句Public Sub executeDMLSql()conn.Execute (sql)End Sub'获取字段名，并放置到指定单元格Public Sub getFieldName(rng As range)Dim fieldNum%Dim fieldsName$For fieldNum = 0 To record.Fields.count - 1fieldsName = fieldsName & record.Fields.item(fieldNum).name & ","Next'Debug.Print fieldsNamerng.Resize(, UBound(Split(fieldsName, ","))) = Application.Transpose(Application.Transpose(Split(fieldsName, ",")))End Sub'以sku为主键,更新结果Public Sub updataRecord(sku As Variant, name As String, param As Variant)With record.Find "ssku=" & sku.Fields(name) = .Fields(name).value & "," & param.MoveFirstEnd WithEnd Sub

如果觉得《vba ado的一些封装》对你有帮助，请点赞、收藏，并留下你的观点哦！