VBA连接SQLSERVER数据库实例

VBA连接SQLSERVER数据库实例

VBA 连接 SQL SERVER 数据连连例

VBA中用ADO连连SQL SERVER数数据连,据连连

连了更好的解连～特提供连例,使用据连象两个数ADO连连SQL数从数并将据连～而连行据连连～连连的

数据返回到EXCEL中～由于此程序需用到SQL数参运据连～所以只供大家考～不便行～但大

家可以看出其精连,

====================================================连例1, ==================================================== Private Sub CommandButton1_Click() Dim cn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim R, C, F, I As Integer

Dim Sql_text, day1, linenumber, box As String Const cnnstr = \\

\'连接据连数

' 默连SQL Server服连器,LOCAL '用连,sa '密连,空

'数据连,Northwind

'表源,Customers

day1 = UserForm1.TextBox1.Text linenumber = UserForm1.ComboBox1.Text box = UserForm1.ComboBox2.Text cn.Open cnnstr

Sql_text = Sql_text & \date1,\

Sql_text = Sql_text & \

dbo.TRY123.linenumber,dbo.TRY123.box_no,dbo.TRY123.serialnumber,dbo.TRY123.lotnum

ber\

Sql_text = Sql_text & \

Sql_text = Sql_text & \'\

and dbo.TRY123.linenumber= '\dbo.TRY123.box_no= '\

Sql_text = Sql_text & \SQL数据连连连连言连连

'SELECT CONVERT(Char,dbo.TRY123.[day],101) as

date1,dbo.TRY123.linenumber,dbo.TRY123.box_no,dbo.TRY123.serialnumber,dbo.TRY123.l

otnumber '

'FROM dbo.TRY123

'WHERE (CONVERT(Char,dbo.TRY123.[day],101)= \dbo.TRY123.linenumber= \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 - 1 Worksheets(\

Worksheets(\While 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表中 Wend

Worksheets(\UserForm1.Hide 'MsgBox (\连取完连\rst.Close '完成后要连连