sql - VBA Excel ADO SQL 更新查询不起作用
问题描述
我是 VBA 和 Excel 脚本的新手,但是,我正在尝试使用它来连接到我创建的 SQL Server。我从用户表单构建了一个通用查询,并创建了一个成功的 SELECT 语句来填充我的工作表。
但是,当我尝试在数据库中更新此信息时,我没有成功。该代码不会引发任何错误,但我无法在数据库中找到我的更改。这是我的尝试:
Private Sub dbUpdate(Query)
Dim conn As ADODB.Connection
Dim recset As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strConn As String
'Create the connection string
strConn = "Provider=SQLNCLI11;Server=IP-Address;Database=Info;Trusted_Connection=yes;DataTypeCompatibility=80;"
'Create the connection and recordset objects
Set conn = New ADODB.Connection
Set recset = New ADODB.Recordset
'Open the connection
conn.Open strConn
'Open the recordset with the query
'Previous attempt, no errors
'recset.Open Query, conn
'Execute the recordset
Set cmd = New ADODB.Command
'The below execution of a query throws errors I believe
cmd.CommandText = Query
Set recset = cmd.Execute
'Close things up
Set recset = Nothing
'recset.Close
conn.Close
Set conn = Nothing
End Sub
我很确定查询是正确的,但如果我仍然无法弄清楚,我会在明天更新。
解决方案
这是一个可能适合您的示例。
Sub ImportDataFromExcel()
Dim rng As Range
Dim r As Long
Dim conn As ADODB.Connection
Dim strConn As String
Dim strSQL As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
"C:\Users\Ryan\Desktop\Coding\Integrating Access and Excel and SQL Server\Access & Excel & SQL Server\" & _
"EXCEL AND ACCESS AND SQL SERVER\Excel & Access\Select, Insert, Update & Delete\Northwind.mdb"
Set conn = New ADODB.Connection
conn.Open strConn
With Worksheets("Sheet1")
lastrow = .Range("A2").End(xlDown).Row
lastcolumn = .Range("A2").End(xlToRight).Column
Set rng = .Range(.Cells(lastrow, 1), .Cells(lastrow, lastcolumn))
End With
'therow = 1
For i = 2 To lastrow
'r = rng.Row
'If r > 1 Then
strSQL = "UPDATE PersonInformation SET " & _
"FName='" & Worksheets("Sheet1").Range("B" & i).Value & "', " & _
"LName='" & Worksheets("Sheet1").Range("C" & i).Value & "', " & _
"Address='" & Worksheets("Sheet1").Range("D" & i).Value & "', " & _
"Age=" & Worksheets("Sheet1").Range("E" & i).Value & " WHERE " & _
"ID=" & Worksheets("Sheet1").Range("A" & i).Value
conn.Execute strSQL
'End If
'r = r + 1
Next i
conn.Close
Set conn = Nothing
End Sub
有这么多不同的版本。希望您可以调整此示例以满足您的特定需求。
推荐阅读
- python - python Popen 不捕获程序的标准输出。不确定为什么
- ruby - “gem install therubyracer”失败并显示错误消息“libv8 需要安装 python 2 才能构建”
- python - 预处理以使用 python opencv 查找两个图像之间的差异
- docker - 几乎`docker pull`命令挂起-重试(5分钟后)-完成
- java - 将一列值转换为键,将另一列值转换为值
- solidity - 我收到了一个像“TypeError:”block.blockhash()“已被弃用,取而代之“blockhash()”之类的错误
- docker - Grails Gradle Docker 插件 bmuschko.github.io/gradle-docker-plugin/ throws 无法设置未知属性“env”
- jquery - 无法将行添加到 jQuery 数据表
- laravel - Laravel Nova - 表单发布前的确认对话框
- sql-server - 在 SSRS 报告中隐藏空值