VBA连接SQLSERVER数据库实例.docVIP

VBA 连接 SQL SERVER 数据库实例 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 String Const 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.Text cn.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.serialnumber rst.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