IF @@ Rowcount = 0-访问中的SQL语法错误 [英] IF @@Rowcount = 0 -- SQL Syntax Error in Access

问题描述

我在excel中有一个子例程，我想将数据写入Access表.我正在尝试更新表中的行(如果已存在)，如果不存在则添加它.离开

I have a subroutine in excel that I would like to write data to an Access table. I am trying to update a row in the table if it already exists, add it if it does not. Going off of the suggestion given on this MSDN blog by Jeremiah Clark, I have my query that I will execute from my Excel's VBA subroutine:

UPDATE tblName 
SET  [Column1] = 'text', ...(other values)... [ColumnN] = 1234 

WHERE ([Column1] = 'text' AND [Column2] = 'text2')

If @@ROWCOUNT = 0

INSERT INTO  tblName

VALUES   ( [Column1] = 'text', ...(other values)... [ColumnN] = 1234 )

它给我的错误是:

Syntax error (missing operator) in query expression '([Column1] = 'text' AND [Column2] = 'text2')

If @@ROWCOUNT = 0

INSERT INTO  tblName

VALUES   ( [Column1] = 'text', ...(other values)...'.

我对SQL还是很陌生，但是尝试了各种方式将IF行括起来(用括号括起来)，以防评估顺序不符合我的预期，但没有用.查询的第一部分是否未评估，因此无法正确执行@@ ROWCOUNT?

I'm pretty new to SQL, but have tried various ways of bracketing (parentheses-ing) the IF line in case the evaluation order was not what I expected, but that was to no avail. Is the first part of the query not being evaluated and thus @@ROWCOUNT cannot be executed properly?

Edit1 :如果需要的话，使用Access 2003.

Edit1: Using Access 2003 if that matters.

解决方案: 基于 bluefeet的建议(请参阅他的全部响应):

Solution: Based on bluefeet's suggestion (see his entire response):

objDB.Execute sqlStrSelect
recordset.Source = sqlStrSelect
recordset.Open , , adOpenDynamic, adLockOptimistic

If recordset.Fields(0) = 0 Then
  objDB.Execute sqlStrInsert
Else
  objDB.Execute sqlStrUpdate
End If

这依赖于修改后的SELECT查询来获取Access以返回记录数:

This relies on a modified SELECT query to get Access to return the Count of the records:

sqlStrSelect = "SELECT Count(id) FROM table1 WHERE id = 3"

HansUp正确地推测出我正在使用ADO连接，因此执行代码的方式必须与bluefeet最初建议的方式不同.

HansUp correctly surmised that I was using an ADO connection, so executing the code had to be done differently from what bluefeet originally suggested.

推荐答案

@@ROWCOUNT用于SQL Server非Access，但是由于您使用的是VBA，因此您可以执行类似的操作.基本上，将SQL语句创建为字符串，并放入要检查的值.然后首先对表运行查询以查看记录是否存在，如果存在，则执行UPDATE，否则执行INSERT.我在MS Access 2003中对此进行了快速测试，并且可以正常工作.

The @@ROWCOUNT is used for SQL Server not Access but since you are using VBA you could do something similar to this. Basically create your SQL statements as strings putting in your values that you are checking for. Then run query against the table first to see if the record exists, if it does then do the UPDATE if not then do the INSERT. I quickly tested this in MS Access 2003 and it works.

Public Sub test()
    Dim sqlStrUpdate As String
    Dim sqlStrSelect As String
    Dim sqlStrInsert As String
    Dim recordSet As recordSet

    sqlStrUpdate = "UPDATE table1 SET Field1 = " & 5 & " WHERE id = 3"

    sqlStrSelect = "SELECT id FROM table1 WHERE id = 3"

    sqlStrInsert = "INSERT INTO table1 (id, Field1, Field2, Field3) VALUES (3, 5, 0, 0)"

    Set recordSet = CurrentDb.OpenRecordset(sqlStrSelect)

    If recordSet.RecordCount > 0 Then
        DoCmd.RunSQL (sqlStrUpdate)
    ElseIf recordSet.RecordCount = 0 Then
        DoCmd.RunSQL (sqlStrInsert)
    End If

End Sub

正如HansUp指出的那样，您正在从Excel查询，您的代码可能与此类似:

As HansUp pointed out you are querying from Excel, your code could be similar to this:

Public Sub test_new()
Dim cDir_Database         As String
Dim DB_Conn               As New ADODB.Connection         'Access Connection
Dim DB_RSet               As New ADODB.recordSet          'Access Record Set

Dim sqlStrUpdate As String
Dim sqlStrInsert As String

cDir_Database = ".\.\.AccessBD.mdb "

sqlStrUpdate = "UPDATE table1 SET Field1 = " & 10 & " WHERE id = 4"

sqlStrInsert = "INSERT INTO table1 (id, Field1, Field2, Field3) VALUES (4, 5, 0, 0)"

        DB_Conn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & _
                            "DBQ=" & cDir_Database & ";"
        DB_Conn.Open
        DB_Conn.BeginTrans

        DB_RSet.Open "SELECT id FROM table1 WHERE id = 4", DB_Conn, adOpenStatic, adLockReadOnly

        If DB_RSet.RecordCount > 0 Then
            DB_Conn.Execute (sqlStrUpdate)
        ElseIf DB_RSet.RecordCount = 0 Then
            DB_Conn.Execute (sqlStrInsert)
        End If

        DB_RSet.Close
        DB_Conn.CommitTrans
        DB_Conn.Close

End Sub

这已经从Excel 2003到Access 2003进行了测试，并且可以正常工作.您需要将引用添加到Microsoft ActiveX数据对象的Excel文件中.

This has been tested from Excel 2003 to Access 2003 and worked. You need to have the references added to your Excel file for the Microsoft ActiveX Data Objects.

这篇关于IF @@ Rowcount = 0-访问中的SQL语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！