sql - VBA-SQL 对 Excel 工作表进行更新/插入/选择

Question

简而言之:我正在为我的客户制作一个调度程序，由于限制，它需要位于单个 Excel 文件中(尽可能小)。因此，一个工作表充当 UI，而任何其他工作表将充当表格或设置。

我正在尝试使用 SQL(我是新手)来处理单个工作表(名为“TblEmpDays”)上的计划数据。因此，我需要向该工作表添加/更新记录并检索记录。我得到了 SELECT查询以处理一些任意数据(并粘贴到范围)。但是，我无法获取 INSERT或UPDATE上类。我看到它的结构为 INSERT INTO [<table name>$] (<field names>) VALUES (<data>); 。然而，这给了我一个运行时错误“'-2147217900 (80040e14)' INSERT INTO 语句中的语法错误。”

我使用 VBA 来编写所有这些内容，并创建了一个 SQL 帮助器类来使查询执行更容易。

澄清一下，我的问题是:我需要如何构建 INSERT和UPDATE查询？我缺少什么？我正在尝试发布尽可能多的相关信息，因此如果我错过了任何内容，请告诉我。

SQL 类:

Private pCn ' As Database
Private pResult 'As Recordset
Private pSqlStr As String

Public Property Get Result()
    Result = pResult
End Property

Public Function Init()
    Set pCn = CreateObject("ADODB.Connection")

    With pCn
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                            "Data Source=" & ThisWorkbook.FullName & ";" & _
                            "Extended Properties=""Excel 12.0 Macro;HDR=YES;ReadOnly=False"";"
        .Open
    End With

End Function

Public Function Cleanup()
    If Not (pCn Is Nothing) Then
        pCn.Close
        Set pCn = Nothing
    End If

    If Not pResult Is Nothing Then
        Set pResult = Nothing
    End If

End Function

Public Function CopyResultToRange(rg As Range)
    If Not rg Is Nothing And Not pResult Is Nothing Then
        rg.CopyFromRecordset pResult
    End If
End Function

Public Property Get query() As String
    query = pSqlStr
End Property
Public Property Let query(value As String)
    pSqlStr = value
End Property

Public Function Execute(Optional sqlQuery As String)
    If sqlQuery = "" Then
        sqlQuery = query
    End If

    If Not pCn Is Nothing Then
        Set pResult = pCn.Execute(sqlQuery, , CommandTypeEnum.adCmdText Or ExecuteOptionEnum.adExecuteNoRecords)
    Else
        MsgBox "SQL connection not established"
    End If

End Function

执行函数:

Dim s As SQL ' this is the SQL class '
Dim tbl As String
' rcDay=date string, rcIn & rcOut = time strings, rcVac=boolean string, rcSls=number string'
Dim rcName As String, rcDay As String, rcIn As String, rcOut As String, rcVac As String, rcSls As String
Dim qry As String

tbl = "[TblEmpDays$]"
qry = "INSERT INTO <tbl> (name, date, in, out, vac, sales)" & vbNewLine & _
        "VALUES ('<name>', '<date>', '<in>', '<out>', '<vac>', <sales>);"

' Set rc* vars '

s.Init
s.query = Replace(Replace(Replace(Replace(Replace(Replace(Replace(qry, _
                                    "<tbl>", tbl), _
                                    "<sales>", rcSls), _
                                    "<vac>", rcVac), _
                                    "<out>", rcOut), _
                                    "<in>", rcIn), _
                                    "<date>", rcDay), _
                                    "<name>", rcName)
MsgBox s.query
s.Execute
s.Cleanup

我查遍了也没找到解决办法。我确定我只是没有搜索到正确的短语或简单的内容。

Answer 1

我在这里发布解决方案，因为我无法将他的评论标记为答案。

<小时/>

感谢评论中的@Jeeped，我现在感觉自己像个白痴。事实证明，我的三个字段名称使用了保留字(“名称”、“日期”和“in”)。 这似乎总是一个微妙的细节让我……

我在工作表(表)中重命名了这些字段并更改了适当的代码。我还必须将输入字符串转换为正确的数据类型。我仍在研究其余的细节，但这是新的查询:

qry = "INSERT INTO <tbl> (empName, empDay, inTime, outTime, vac, sales)" & vbNewLine & _
                  "VALUES (CStr('<name>'), CDate('<date>'), CDate('<in>'), CDate('<out>'), " & _
                      "CBool('<vac>'), CDbl(<sales>));"

我需要CDate() (而不是 #*#)这样我就可以传入一个字符串。所以CDate('<date>')而不是#<date>#