VBA 连接 SQL SERVER 数据库 实例.doc

VBA中用ADO访问SQL SERVER数据库：数据查询为了更好的解释，特提供两个实例：使用数据对象ADO访问SQL数据库，从而进行数据查询，并将查询的数据返回到EXCEL中，由于此程序需用到SQL数据库，所以只供大家参考，不便运行，但大家可以看出其精华！=实例1：= Private Sub CommandButton1_Click() Dim cn As New ADODB.ConnectionDim rst As New ADODB.RecordsetDim R, C, F, I As IntegerDim Sql_text, day1, linenumber, box As StringConst cnnstr = Provider = SQLOLEDB; & _ Data Source = apsgszml04; & _ Initial Catalog = bhl2ken;User ID =sa;Password =;连接数据库 默认SQL Server服务器：LOCAL 用户：sa 密码：空 数据库：Northwind表源：Customersday1 = UserForm1.TextBox1.Textlinenumber = UserForm1.ComboBox1.Textbox = UserForm1.ComboBox2.Textcn.Open cnnstrSql_text = Sql_text & SELECT CONVERT(Char,dbo.TRY123.day,101) as date1, Sql_text = Sql_text & dbo.TRY123.linenumber,dbo.TRY123.box_no,dbo.TRY123.serialnumber,dbo.TRY123.lotnumber Sql_text = Sql_text & FROM dbo.TRY123 Sql_text = Sql_text & WHERE (CONVERT(Char,dbo.TRY123.day,101)= & day1 & and dbo.TRY123.linenumber= & linenumber & and dbo.TRY123.box_no= & box & ) Sql_text = Sql_text & ORDER BY dbo.TRY123.serialnumber 使用SQL数据库查询语言查询 SELECT CONVERT(Char,dbo.TRY123.day,101) as date1,dbo.TRY123.linenumber,dbo.TRY123.box_no,dbo.TRY123.serialnumber,dbo.TRY123.lotnumberFROM dbo.TRY123WHERE (CONVERT(Char,dbo.TRY123.day,101)= & day1 & and dbo.TRY123.linenumber= & linenumber & and dbo.TRY123.box_no= & box & ORDER BY dbo.TRY123.serialnumberrst.Open Sql_text, cn, adOpenStatic, adLockBatchOptimistic 用adOpenStatic + adLockBatchOptimistic打开较快，且占用资源小R = 5 Excel表的行序号C = 3 Excel表的列序号I = 0 SQL表的字段序号F = rst.Fields.Count - 1Worksheets(sheet1).UnprotectWorksheets(sheet1).Cells.ClearContentsWhile Not rst.EOF For I = 0 To F Sheet1.Cells(R, I + 3).Rows.Value = rst.Fields(I).Value Next I R = R + 1 rst.MoveNext 将数据库的数据返回到EXCEL表中WendWorksheets(sheet1).ProtectUserForm1.HideMsgBox (读取完毕)rst.Close 完成后要关闭cn.Close 完成后要关闭注意：以上为直接连接SQL Server 的方法，不用建ODBC数据源Worksheets(sheet1).ActivateEnd Sub=实例2：=sub test() 定义过程名称Dim i As Integer, j As Integer, sht As Worksheet i,j为整数变量；sht 为excel工作表对象变量，指向某一工作表Dim cn As New ADODB.Connection 定义数据链接对象 ，保存连接数据库信息；请先添加ADO引用Dim rs As New ADODB.Recordset 定义记录集对象，保存数据表Dim strCn As String ,strSQL as String 字符串变量strCn = Provider=sqloledb;Server=服务器名称或IP地址;Database=数据库名称;Uid=用户登录名;Pwd=密码; 定义数据库链接字符串下面的语句将读取数据表数据，并将它保存到excel工作表中：画两张表想像一下，工作表为一张两维表，记录集也是一张两维表strSQL = select 字段1,字段2 from 表名称 定义SQL查询命令字符串cn.Open strCn 与数据库建立连接，如果成功，返回连接对象cnrs.Open strSQL, cn 执行strSQL所含的SQL命令，结果保存在rs记录集对象中i = 1Set sht = ThisWorkbook.Worksheets(sheet1) 把sht指向当前工作簿的sheet1工作表Do While Not rs.EOF 当数据指针未移到记录集末尾时，循环下列操作 sht.Cells(i, 1) = rs(字段1) 把当前记录的字段1的值保存到sheet1工作表的第i行第1列 sht.Cells(i, 2) = rs(字段2) 把当前字段2的值保存到sheet1工作表的第i行第2列 rs.MoveNext 把指针移向下一条记录 i = i + 1 i加1，准备把下一记录相关字段的值保存到工作表的下一行Loop 循环rs.Close 关闭记录集，至此，程序将把某数据表的字段1和字段2保存在excel工作表sheet1的第1、2列，行数等于数据表的记录数下面的语句将读取excel工作表数据，并将之简单计算后存入数据库，这里使用上面程序中的一些变量假设分别读取工作表sheet1第5行至第500行的第8列和第9列已存在的数据，然后将它们相乘，并将积存入数据库的某个表strSQL= 清空上面定义的变量for i=5 to 500 循环开始，i从5到500s strSQL=strSQL & insert into 表名(字段) values( & sht.cells(i,8)*sht.cells(i,9) & ) ; 构造SQL命令串next至此生成一串SQL命令串，保存的内容大概为：insert int