如何利用MS访问表对Server表进行批量更新
我需要从MS ACCESS表中更新Server中的数百万条记录。我使用MS作为前端，使用SQL作为链接表。因此，据我所知，我需要创建传递查询来执行这样的操作：
UPDATE SQLtbl SET SQLtbl.col1 = MDBtbl.Col1从SQLtbl内部连接到SQLtbl.ID = MDBtbl.ID，其中SQLtbl.col1 = MDBtbl.Col1
它的工作非常慢，所以我需要以某种方式将int转换成批量更新--请通知，谢谢
回答 2
Stack Overflow用户
发布于 2014-10-09 23:10:02
类似于最近的一个相关问题here，这是另一种情况，在这种情况下，处理ODBC链接表就像对待本机访问表一样可能导致性能缓慢。
对于两个名为SQLtbl和MDBtbl的相同访问表，每个表的行数为9999行，执行以下代码花费了大约5.5分钟：
Sub UpdateViaJoin()
Dim con As ADODB.Connection
Dim t0 As Single
Set con = CurrentProject.Connection
con.CommandTimeout = 0
t0 = Timer
con.Execute _
"UPDATE " & _
"SQLtbl INNER JOIN MDBtbl " & _
"ON SQLtbl.ID = MDBtbl.ID " & _
"SET SQLtbl.Col1 = MDBtbl.Col1"
Debug.Print Format(Timer - t0, "0.0") & " seconds"
Set con = Nothing
End Sub为了查看连接本身是否有问题，我运行了以下操作，只需5分钟就可以完成：
Sub UbdateViaDLookup()
Dim cdb As DAO.Database
Dim t0 As Single
Set cdb = CurrentDb
t0 = Timer
cdb.Execute _
"UPDATE SQLtbl SET Col1 = DLookup(""Col1"", ""MDBtbl"", ""ID="" & ID)"
Debug.Print Format(Timer - t0, "0.0") & " seconds"
Set cdb = Nothing
End Sub另一方面，使用传递查询和原生times准备语句的下列代码始终在2秒内运行(即快100倍以上)：
Sub UpdateViaPassThroughQuery()
Dim cdb As DAO.Database, rst As DAO.Recordset, qdf As DAO.QueryDef
Dim SQL As String, statementHandle As Long, i As Long, updateList As String
Dim t0 As Single
Set cdb = CurrentDb
t0 = Timer
SQL = "SET NOCOUNT ON;"
SQL = SQL & "DECLARE @statementHandle int;"
SQL = SQL & "EXEC sp_prepare @statementHandle OUTPUT, N'@P1 nvarchar(50), @P2 int', N'UPDATE SQLtbl SET Col1=@P1 WHERE ID=@P2';"
SQL = SQL & "SELECT @statementHandle;"
Set qdf = cdb.CreateQueryDef("")
qdf.Connect = cdb.TableDefs("SQLtbl").Connect
qdf.SQL = SQL
qdf.ReturnsRecords = True
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
statementHandle = rst(0).Value
rst.Close
Set rst = cdb.OpenRecordset("SELECT ID, Col1 FROM MDBtbl", dbOpenSnapshot)
i = 0
updateList = ""
Do Until rst.EOF
i = i + 1
updateList = updateList & "EXEC sp_execute " & statementHandle & ", N'" & Replace(rst!Col1, "'", "''") & "', " & rst!id & ";"
If i = 1000 Then
qdf.SQL = updateList
qdf.ReturnsRecords = False
qdf.Execute
i = 0
updateList = ""
End If
rst.MoveNext
Loop
If i > 0 Then
qdf.SQL = updateList
qdf.ReturnsRecords = False
qdf.Execute
End If
rst.Close
Set rst = Nothing
qdf.SQL = "EXEC sp_unprepare " & statementHandle & ";"
qdf.ReturnsRecords = False
qdf.Execute
Set qdf = Nothing
Debug.Print Format(Timer - t0, "0.0") & " seconds"
Set cdb = Nothing
End Sub编辑
要调整上面的代码以处理Nulls，您需要更新行.
updateList = updateList & "EXEC sp_execute " & statementHandle & ", N'" & Replace(rst!Col1, "'", "''") & "', " & rst!id & ";"..。敬..。
updateList = updateList & "EXEC sp_execute " & statementHandle & ", " & _
FormatArgForPrepStmt(rst!Col1) & ", " & _
rst!id & ";"..。并添加一个小的格式化函数，如下所示：
Private Function FormatArgForPrepStmt(item As Variant) As String
If IsNull(item) Then
FormatArgForPrepStmt = "NULL"
Else
Select Case VarType(item)
Case vbString
FormatArgForPrepStmt = "N'" & Replace(item, "'", "''") & "'"
Case vbDate
FormatArgForPrepStmt = "N'" & Format(item, "yyyy-mm-dd Hh:Nn:Ss") & "'"
Case Else
FormatArgForPrepStmt = CStr(item)
End Select
End If
End FunctionStack Overflow用户
发布于 2014-10-13 20:15:37
海报上清楚地写着：
“I understand I need to crate pass-though queries”.因此，我们需要解决如何创建和运行pass查询。
不过，简单的解决方案是将查询保存为pass。实际上，您可以将现有查询“更改”为pass--尽管。
在“设计”视图中打开现有查询，然后按“传递”按钮。
例如：
如果您将查询保存为pass-但，则运行服务器端t。
请注意，按下“通”按钮后，也会弹出属性表。我在属性表中圈出了“通过”按钮和ODBC设置。实际上，您可以通过剪切+粘贴从链接表复制odbc连接字符串。
因此:不需要在代码中设置连接字符串，也不需要编写VBA代码。事实上，几乎没有必要重写sql，它应该像本地to代码那样工作。
因此，我们在这里实现了相当理想的目标：
您可以重用现有的查询。
您不必重新编写查询。
您不必采用并编写大量代码。
你甚至不需要了解VBA
您不会在现有的应用程序中引入一个全新的ADO对象模型库。现有的应用程序不太可能使用ADO对象库。现在，您必须将这个全新的对象库引入到现有的应用程序中。因此，添加这个新的引用和VBA代码将产生很大的破坏并将bug引入现有代码库的可能性，尤其是如果现有代码没有限定DAO记录集的话。
我认为要运行一次传递--尽管更新，使用一次简单的查询要简单得多，而且风险更大--只需在带上单击就可以引入整个新的ADO引用和对象libriares，然后引入一堆VBA ADO代码来简单地运行一个简单的更新查询。
https://stackoverflow.com/questions/26284071
复制相似问题
腾讯云开发者
