如何修复VBA中的“Command was not prepared”错误？

执行ws.Range("A2").CopyFromRecordset rs时触发“Command was not prepared”错误，结合你的代码，以下是具体修复方案：

1. 修复SQL语句拼接的语法错误 #

SQL字符串拼接时，换行位置缺少空格，导致关键字连在一起（比如LEAD SOURCE和FROM直接拼接成LEAD SOURCEFROM），会让SQL解析失败。

修改SQL拼接部分，确保每个换行的字符串末尾或开头添加空格：

sql = "SELECT [ACCOUNT CREATE DATE]" & _
                ",[ACCOUNT NAME]" & _
                ",[ACCOUNT NUMBER]" & _
                ",A.[GSFA CUSTOMER ID]" & _
                ",A.[TERRITORY]" & _
                ",A.[SALES TEAM]" & _
                ",C.[LEAD SOURCE] " & _  ' 末尾添加空格
        "FROM [CPR].[dbo].[COMET_ACCOUNT] AS A " & _  ' 末尾添加空格
        "LEFT JOIN [CPR].[dbo].[COMET_CUSTOMER] AS C " & _  ' 末尾添加空格
        "ON A.[GSFA CUSTOMER ID] =  C.[GSFA CUSTOMER ID] " & _  ' 末尾添加空格
        "WHERE CAST(GETDATE() AS INT) - CAST([ACCOUNT CREATE DATE] AS INT) <=7 AND [LEAD SOURCE] = 'Customer Service' " & _  ' 末尾添加空格
        "ORDER BY CAST(GETDATE() AS INT) - CAST([ACCOUNT CREATE DATE] AS INT) DESC"

2. 调整连接字符串的预编译参数 #

连接字符串中的Use Procedure for Prepare=1可能引发预编译相关错误，将其改为Use Procedure for Prepare=0或直接移除该参数：

conn.ConnectionString = "Provider=SQLOLEDB;Initial Catalog=CPR; Data Source=VNSGNWD1027007; Workstation ID=VNSGNWD1023020; User ID=sav; Password=123456789;" & _
                                            "Persist Security Info=True; Use Procedure for Prepare=0; Auto Translate=True; Packet Size=4096;" & _
                                            "Use Encryption for Data=False;Tag with column collation when possible=False"

3. 修复变量声明的语法错误 #

代码中Dim col As Long Dim ws As Worksheet属于语法错误，需拆分为两行：

Dim col As Long
Dim ws As Worksheet

4. 指定记录集的游标类型（可选） #

打开记录集时显式指定游标类型和锁定类型，避免默认设置的兼容性问题：

rs.Open sql, conn, adOpenStatic, adLockReadOnly

修改后的完整代码 #

Private Sub UploadDataFromSQL() 
'Set variables 
Dim conn As New ADODB.Connection 
Dim rs As New ADODB.Recordset 
Dim sql As String 
Dim row As Long 
Dim col As Long
Dim ws As Worksheet

' Create a new connection object
Set conn = New ADODB.Connection
'Create a new recordset object and new worksheet
Set rs = New ADODB.Recordset
'Set up Excel worksheet
Set ws = ThisWorkbook.Sheets("Data")

'Set connection properties'
conn.ConnectionString = "Provider=SQLOLEDB;Initial Catalog=CPR; Data Source=VNSGNWD1027007; Workstation ID=VNSGNWD1023020; User ID=sav; Password=123456789;" & _
                                            "Persist Security Info=True; Use Procedure for Prepare=0; Auto Translate=True; Packet Size=4096;" & _
                                            "Use Encryption for Data=False;Tag with column collation when possible=False"
conn.Open

sql = "SELECT [ACCOUNT CREATE DATE]" & _
                ",[ACCOUNT NAME]" & _
                ",[ACCOUNT NUMBER]" & _
                ",A.[GSFA CUSTOMER ID]" & _
                ",A.[TERRITORY]" & _
                ",A.[SALES TEAM]" & _
                ",C.[LEAD SOURCE] " & _
        "FROM [CPR].[dbo].[COMET_ACCOUNT] AS A " & _
        "LEFT JOIN [CPR].[dbo].[COMET_CUSTOMER] AS C " & _
        "ON A.[GSFA CUSTOMER ID] =  C.[GSFA CUSTOMER ID] " & _
        "WHERE CAST(GETDATE() AS INT) - CAST([ACCOUNT CREATE DATE] AS INT) <=7 AND [LEAD SOURCE] = 'Customer Service' " & _
        "ORDER BY CAST(GETDATE() AS INT) - CAST([ACCOUNT CREATE DATE] AS INT) DESC"

'Execute query'
rs.Open sql, conn, adOpenStatic, adLockReadOnly
    
'Copy the recordset data to the Excel worksheet
ws.Range("A2").CopyFromRecordset rs
 
'Close connection and recordset'
rs.Close
conn.Close

'Clean up
Set rs = Nothing
Set conn = Nothing

End Sub

内容的提问来源于stack exchange，提问作者Phuong Le