Vba和mssql如何建立数据库连接

MyServer = “192.168.1.22,1433” ‘存放数据的SQL Server 服务器
mydata = “CR_YPPF10” ‘存放数据的SQL Server 数据库

‘***********************修改结束*********************************************************

Set conn = CreateObject(“ADODB.Connection”) ‘建立ADODB连接对象
Set rs = CreateObject(“ADODB.recordset”) ‘建立收集器对象

conn.connectionstring = “Driver={sql server};” _
& “server=” & MyServer & “;” _
& “uid=11;pwd=11;” _
& “database=” & mydata _
& “;AutoTranslate=False”
‘服务器是:WISE-THINK
conn.Open ‘打开连接SQL server

将数组 写入数据库中

For i = 1 To UBound(MyArr1) – 1
For j = 1 To UBound(MyArr4) – 1
If Sheets(“Sheet1”).Cells(i + 1, 6).Value = “完成” Then
GoTo down:
End If
mystr = ” Values( ”
‘mystr = mystr & “‘” & MyArr1(i) & “‘,'” & MyArr2(i) & “‘,'” & MyArr3(j) & “‘,'” & MyArr4(i) & “‘,'” & MyArr5(i) & “‘)”
mystr = mystr & “‘000’,'” & MyArr2(i) & “‘,’销售客户’,'” & MyArr4(j) & “‘,’包含’)”
‘添加数据的SQL语句

SQL = “Insert into sp_kz ” & Arr & ” ” & mystr
conn.Execute SQL
Next j
Sheets(“Sheet1”).Cells(i + 1, 6).Value = “完成”
Next i
MsgBox “数据添加成功!”, vbInformation

将excel表格数据写入数组


x = Range(“A65536”).End(3).Row ‘x的值为A列中最后一个非空单元格行号
ReDim a(x) ‘重新定义数组a,使之上限为x
For i = 1 To x ‘i从A1依次往下循环,直到最后一行

a(i – 1) = Range(“A” & i) ‘将A列单元格值依次存放于数组中

Next

MyArr1 = a()