楼主
wise |
Q:如何使用VBA给SQL server 的表添加多列数据？
A:ALT+F11→》插入模块，在模块中输入以下代码：- Sub InsertmuchData()
- Dim conn As Object, rs As Object
- Dim SQL As String
- Dim Arr As String, MyArr1 As Variant, MyArr2 As Variant, MyArr3 As Variant, MyArr4 As Variant, MyArr5 As Variant
- Dim mystr As String, i As Long
- '***********************需要修改此处的SQL Server 数据库条件******************************
- MyServer = "WISE-THINK" '存放数据的SQL Server 服务器
- mydata = "VBA学习专用" '存放数据的SQL Server 数据库
- '***********************修改结束*********************************************************
- Set conn = CreateObject("ADODB.Connection") '建立ADODB连接对象
- Set rs = CreateObject("ADODB.recordset") '建立收集器对象
- conn.connectionstring = "Driver={sql server};" _
- & "server=" & MyServer & ";" _
- & "uid=;pwd=;" _
- & "database=" & mydata _
- & ";AutoTranslate=False"
- '服务器是:WISE-THINK
- conn.Open '打开连接SQL server
- If MsgBox("是否添加数据?", vbQuestion + vbYesNo) = vbYes Then
- '定义各个变量
- Arr = " ( 品牌 ,套餐名称 ,套餐月费 ,协议期限 ,预存话费 ) "
- MyArr1 = Array("Apple", "小米", "酷派")
- MyArr2 = Array("A类3G基本套餐66元档", "B计划126元3G套餐", "B计划46元3G套餐")
- MyArr3 = Array(89, 69, 49)
- MyArr4 = Array(24, 36, 24)
- MyArr5 = Array(1599, 1899, 899)
-
- '循环添加各个数据
- For i = 0 To UBound(MyArr1)
- mystr = " Values( "
- mystr = mystr & "'" & MyArr1(i) & "','" & MyArr2(i) & "'," & MyArr3(i) & "," & MyArr4(i) & "," & MyArr5(i) & ")"
- '添加数据的SQL语句
- SQL = "Insert into 套餐 " & Arr & " " & mystr
- conn.Execute SQL
- Next i
- MsgBox "数据添加成功!", vbInformation
- Else
- MsgBox "数据添加动作给取消", vbInformation
- End If
- conn.Close
- Set conn = Nothing
- End Sub
|