sql-server - 执行 Excel VBA SQL 查询的最优雅方法是什么？

Question

我正在使用此代码和非常相似的其他代码 工作完美但我想知道是否有更优雅的方式来做到这一点:
(提炼)

Erase ArrUserMetRecCentros
Cnn.Open
CnnQry = _
    "SELECT" & _
    "   APP_METERS_Q_Profiles.ProfileId, " & _
    "   Main_Profile_Info.FieldValue, " & _
    "   APP_METERS_T_Access.[Read], " & _
    "   APP_METERS_T_Access.Write " & _
    "FROM (APP_METERS_Q_Profiles " & _
    "   INNER JOIN Main_Profile_Info ON APP_METERS_Q_Profiles.ProfileId = Main_Profile_Info.ProfileId) " & _
    "   INNER JOIN APP_METERS_T_Access ON APP_METERS_Q_Profiles.ProfileId = APP_METERS_T_Access.APPMETERSQProfileId " & _
    "WHERE " & _
    "   (((Main_Profile_Info.FieldId)=1) AND " & _
    "   ((APP_METERS_T_Access.APPMETERSQUserId)=1) AND " & _
    "   ((APP_METERS_T_Access.APPMETERSTDataTypeId)=1) AND " & _
    "   ((APP_METERS_T_Access.[Read])=-1));"
Set rst = Cnn.Execute(CnnQry)
    With rst
        x = x + 1
        Do Until rst.EOF
            For i = 1 To 4
                ArrUserMetRecCentros(x, i) = rst.Fields(i - 1).Value
            Next i
            .MoveNext
            x = x + 1
        Loop
        ArrUserMetRecCentros(0, 0) = x - 1
    End With
Cnn.Close

这将连接到 MS SQL EXPRESS 服务器并将请求的数据存储在我将在 EXCEL 中使用的数组中。
我不是要你重写我的代码(这是我的工作；))如果有的话，只是一些提示。诸如“不使用 'Set rst=blablabla' 更好的选择是执行 'this' 之类的事情。或者“在不同的公共(public)字符串变量中定义所有查询以保持代码更清晰”等。

非常感谢您收到的任何帮助。
古斯塔沃。

Answer 1

CnnQry = _
    "SELECT" & _
    "   APP_METERS_Q_Profiles.ProfileId, " & _
    "   Main_Profile_Info.FieldValue, " & _
    "   APP_METERS_T_Access.[Read], " & _
    "   APP_METERS_T_Access.Write " & _
    "FROM (APP_METERS_Q_Profiles " & _
    "   INNER JOIN Main_Profile_Info ON APP_METERS_Q_Profiles.ProfileId = Main_Profile_Info.ProfileId) " & _
    "   INNER JOIN APP_METERS_T_Access ON APP_METERS_Q_Profiles.ProfileId = APP_METERS_T_Access.APPMETERSQProfileId " & _
    "WHERE " & _
    "   (((Main_Profile_Info.FieldId)=1) AND " & _
    "   ((APP_METERS_T_Access.APPMETERSQUserId)=1) AND " & _
    "   ((APP_METERS_T_Access.APPMETERSTDataTypeId)=1) AND " & _
    "   ((APP_METERS_T_Access.[Read])=-1));"

那不属于代码。这是一个巨大的字符串文字:你没有语法高亮，没有验证，没有智能感知；您需要手动跟踪括号，超过 20 行继续，它会停止编译，您开始执行 CnnQry = CnnQty & "...some more inline SQL..." 之类的操作

如果它需要参数值，它们可能只是连接起来 - 然后你需要注意单引号，并且你有一个 SQL 注入(inject)漏洞，这不仅是一个严重的安全问题，而且是一个错误，不少于.

内联 SQL 是可憎的，不管它是用什么语言完成的。你拥有的越少越好。

值得我每次尝试输入 CnnQry 时都会打错字。 : 元音是允许的，用 sql没有错任何一个。

您需要在 SQL Server 端进行 SQL 查询，而不是在您的代码中。

CREATE PROCEDURE dbo.GiveMeAGoodName
AS BEGIN
    SELECT
       ...
    FROM ...
        INNER JOIN ...
        INNER JOIN ...
    WHERE ...
END

现在您在 VBA 中的内联 SQL 变为:

Const sql As String = "dbo.GiveMeAGoodName"

VBA 代码应该使用 Command获取 Recordset :

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = Cnn
cmd.CommandText = sql
cmd.CommantType = adCmdStoredProc

'add parameters if needed:
'cmd.Parameters.Append cmd.CreateParameter(...)

Dim results As ADODB.Recordset
Set results = cmd.Execute

请注意，通过上述正确的参数化，您可以让 SQL Server 完成其工作并处理引用，以及 Little Bobby Tables不能造成任何伤害。

使用 Range.CopyFromRecordset将记录集转储到 Range没有循环。