时间戳

首页 > 解决方案 > 我需要在 Access VBA 中创建对 SQL Server 的插入传递查询

sql-server - 我需要在 Access VBA 中创建对 SQL Server 的插入传递查询

问题描述

我需要创建一个到 SQL Server 的传递查询。我有一个有效的传递查询，它只从导航栏或 Access 中的查询设计运行，它只创建一条记录。我有几十条记录要根据 Access 表中的值插入到 SQL Server 表中。因此，我需要在代码中循环访问 Access 表，并为每条记录创建 SQL 字符串并执行它。我已经让循环工作了，只需要正确的代码来连接到 SQL 数据库并执行 SQL 字符串。这是传递查询中有效的 ODBC 连接字符串，其中 PW 和 DB 加星标：“ODBC;DSN=jobboss32;UID=support;PWD=****;DATABASE=****”。下面的代码给出了数据类型转换错误。

Dim strFuturePTOSource, strPassThruInsert, strEmpName, strPTODate, strUpdated As String
Dim datPTODate As Date, lngPTOHours As Long
Dim rs As New ADODB.Recordset, q As QueryDef

    strFuturePTOSource = "qryROBERT"    'temporary data set to test, change to actual after working
    rs.Open strFuturePTOSource, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    With rs
        If Not .BOF And Not .EOF Then
            .MoveLast
            .MoveFirst

            While (Not .EOF)
                strEmpName = rs.Fields("EmpName")
                strEmpName = "'" & strEmpName & "', "
                datPTODate = rs.Fields("PTODate")
                strPTODate = "'" & Format(datPTODate, "mm/dd/yyyy") & "'"
                lngPTOHours = rs.Fields("PTOHrs") * 60
                strUpdated = "'" & Format(Now(), "mm/dd/yyyy") & "'"
                strPassThruInsert = "INSERT INTO Attendance (Attendance, Employee, Work_Date, Regular_Minutes, Attendance_Type, Lock_Times, Source, Last_Updated) VALUES (NewID(), "
                strPassThruInsert = strPassThruInsert & strEmpName & strPTODate & ",lngPTOHours,2,-1,0, '" & Now() & "');"
                Set q = CurrentDb.CreateQueryDef("", strPassThruInsert)
                CurrentDb.QueryDefs(q).Execute
                .MoveNext
            Wend
        End If
        .Close
    End With

标签： sql-servervbams-accessinsertconnection

解决方案

首先，您没有给出使用传递查询插入的任何正当理由。

事实上，因为每个插件都是 100% 且独立且“离散”的插件？

那么，查询处理器设置、解析语法和插入的时间呢？

好吧，您不会在性能上获得任何收益，实际上它的运行速度不会比使用 DAO 记录集的普通 Jane 和标准插入快。

结果：作为一名开发人员，你明知浪费了各种时间来追求这种方法，而这是在 UA 等地方获得此建议之后。

如果你故意在浪费开发人员的时间，并寻求一种花费更多开发人员时间的方法，但不会加快你的插入速度，那么你就是在寻找一种花费更多时间和更多资源的方法，而不是必要的。

我想如果您为此付出了代价，并且不在乎，或者您将其作为学习练习进行，那很好。但是对于一个可行的解决方案，这种方法没有意义，需要更多的代码和时间来开发和修复，而你这样做将充分了解增加的成本和时间，而我看不到或想到的任何好处。

那么这就引出了问题？您为什么要尝试一个解决方案，该解决方案将花费更多时间，花费更多精力并且不会提高性能？对于这种方法的额外好处，您没有给出任何理由。

以下代码比您给定的方法要少得多，简单得多，运行速度更快。更好的是您拥有的日期转换（错误）不是问题。

所以，你必须编造一个很好的理由来解释你为什么在以下情况下仔细阅读这个行动方案：

它不会比使用我在此处发布的代码运行得更快（事实上，这样的 PT 查询会运行得更慢 - 并且相差很大）。

您的日期格式有错误 - 您必须使用 ISO sql server 格式（但是，如果您使用 ODBC 并让记录集进行翻译，那么您拥有的日期和日期时间值的零格式和零重新格式是不需要）。

那么，直到你解释为什么你愿意以全部知识编写更多代码，花费更多时间，并制定一个浪费时间并且不会运行得更快的解决方案？

那么我看不出有什么理由去追求你的方法？对吧？

所以，你需要想出一个很好的理由来解释你为什么坚持这种直通查询方法，以及一种运行速度不会比我在这里发布的更快的方法......事实上，正如我指出，发布的解决方案将运行得更快。（不是我说的快一点——但要快得多——实际上是在多个订单上（不是 %，而是快几倍）。如果你想知道为什么，我可以解释为什么会出现这种情况（更快的速度）。

与此同时，您将不得不弄清楚如何在完全了解追求解决方案的情况下睡觉，这种解决方案将花费更多的开发人员时间，并且不会在速度方面产生任何好处，甚至不会在维护和编写此类代码方面产生任何好处.

此代码消除了您拥有的日期错误转换，并且将比您发布的代码和解决方案运行得更快：

Dim strFuturePTOSource  As String

Dim rstFrom             As DAO.Recordset  ' from table
Dim rstTo               As DAO.Recordset  ' SQL server linked table.

Set rstFrom = CurrentDb.OpenRecordset("qryROBERT", dbOpenDynaset, dbSeeChanges)
Set rstTo = CurrentDb.OpenRecordset("Attendance", dbOpenDynaset, dbSeeChanges)

Do While rstFrom.EOF = False
   With rstTo
      .AddNew
      !Attendance = NewID()
      !Employee = rstFrom!EmpName
      !Work_Date = rstFrom!PTODate
      !Regular_Minutes = 600
      !Attendance_Type = 2
      !Lock_Times = -1
      !Source = 0
      !Last_UPdated = Now
      .Update
   End With
   rstFrom.MoveNext
Loop
rstTo.Close
rstFrom.Close

编辑

鉴于海报已经很好地证明了要使用 PT 查询？

那么这段代码应该可以工作：

我们假设您已经创建了一个有效的 PT 查询。（并且它的返回记录集 = false）。我倾向于在 Access 中创建一个 PT 查询，然后代码中的任何和所有位置都可以很好地重用该 PT 查询。此外，如果 NewID() 是标量函数 (t-sql)，如前所述，它必须以 dbo 为前缀。所以，我们必须使用 dbo.NewID()

因此，“关闭”或我建议的代码是：

Dim rs         As DAO.Recordset
Dim strSQL     As String
Dim strSQLS    As String

strSQLS = "INSERT INTO Attendance (Attendance, Employee, Work_Date, Regular_Minutes, " & _
         "Attendance_Type, Lock_Times, Source, Last_Updated) " & _
         "VALUES (dbo.NewID(),"


Set rs = CurrentDb.OpenRecordset("qryROBERT")

With rs

  Do While .EOF = False
  
     strSQL = strSQLS
     
     strSQL = strSQL & qu(!EmpName) & "," & quDate(!PTODate) & _
              "," & quDate(Date) & "," & (!PTOHrs * 60) & ",2,-1,0," & quDate(Now())
              
     With CurrentDb.QueryDefs("MyPassQuery")
        .SQL = strSQL
        .Execute
     End With
  
     .MoveNext
  Loop
  rs.Close
  
End With

除了上述之外，我还使用了两个辅助函数来格式化日期，因为执行这个“内联”代码很痛苦，所以我有 qu()（用于字符串）和 qudate() 用于日期。

Public Function qudate(myDate As Variant) As String

  ' returns a formatted string of date, ISO format, for sql sesrver.
  ' format is yyyy-mm-dd regardless of local date settings

  If IsNull(myDate) = True Then
     qudate = ""
  Else
     ' use ISO date format
     qudate = "'" & Format(myDate, "yyyy-mm-dd HH:NN:SS") & "'"
  End If

End Function

还有我们的 qu() 函数

Function quS(vText As Variant) As String
  
  ' takes a string and surrounds it with single quotes
  
  If IsNull(vText) = False Then
     If InStr(vText, Chr(34)) > 0 Then
        vText = Replace(CStr(vText), Chr(34), "'")
     End If
  End If

  quS = "'" & vText & "'"
  
End Function

编辑 2

因此，步骤是：从 sql studio，获取一个插入数据的示例。一旦你有一个有效的插入命令，然后使用相同的（和已知的）工作命令并将其剪切 + 粘贴到访问查询中 - 但只需确保访问端的查询已创建并设置为传递。再次测试运行此 PT 查询。如果现有的 sql 在 SSMS 中工作，那么它将在 Access 100% 中完全与 SAME 查询一起工作。一旦那个工作？

所以，无论你给这个查询起什么名字，你都可以用它来代替 MyPassQuery。你可以给它任何你想要的名字，但你必须在你的 VBA 代码中使用正确的（相同的）名称，以便为该 PT 查询使用/设置 sql。因此，每个循环运行实际上都会覆盖查询中的内容，然后您执行 .Execute 来运行它。

好吧，然后你运行你的代码。并在这条线上进行测试？

.SQL = strSQL

做这个：

debug.print strSQL
.SQL = strSQL

因此，当您单步执行该代码时，VBA 正在创建的 sql 必须与您拥有的已知工作 sql 匹配。那么，如果字符串输出有任何语法错误，或者看起来与已知的工作 sql 不是 100% 相同？好吧，那么您必须调整 VBA 代码，直到它吐出您知道有效的相同 sql 字符串。

推荐阅读