进行插入时，MS-Access运行时错误3073 [英] MS-Access run time error 3073 when doing Insert

问题描述

这是我的情况.我有一个Access 2010数据库，分为前端和后端，使用VBA代码处理我的数据.直到上周，我每月进行大批量插入时都没有问题.插入是通过将csv文件读取为Excel电子表格并将数据插入到Access表中来完成的(并非所有数据都会被插入，因此必须单独处理每一行).当我星期五跑步时，我突然开始随机出现3073错误(操作必须使用可更新的查询).它可以在数据库中的任何表上发生.

Here's my scenario. I have an Access 2010 database split into a Front End and a Back End, with VBA code for processing my data. Up until last week, I had no issues when I did my monthly mass inserts. The inserts are done by reading csv files as Excel spreadsheets and inserting the data into the Access tables (not all of the data will be inserted, so each line must be processed separately). When I ran Friday, I all of a sudden started getting random 3073 errors (Operation must use an updateable query). It can happen on any table in the database.

我使用调试重新运行并在错误处理程序中设置断点，并发现是否从断点移回查询执行命令并逐步执行该命令，但未收到错误.这是一个示例插入:

I reran using debug and set break points in my error handler, and discovered if I move from the break point back to the query execute command and step through it, I don't get the error. Here is a sample insert:

INSERT INTO OtherMeasures (HapId, SurveyTypeId, Score, Comments) 
    VALUES (86792, 9, 21.00, '').

以下是创建SQL的VBA代码:

Here the VBA code that creates the SQL:

Private Sub storeOthType(ByVal hapId As Long, ByVal othType As String, _
                    ByVal othScore As Integer, ByVal othComment As String, _
                    ByRef db As DAO.Database)
  Dim sql As String
  On Error GoTo foundError

  Dim typeId As Integer
  typeId = lookupId(othType, "Description", "DataTypes", False, db)

  sql = "INSERT INTO OtherMeasures (HapId, SurveyTypeId, Score, " + _
           Comments) " + _
           "VALUES (" + CStr(hapId) + ", " + CStr(typeId) + ", " + _
                        Format(othScore, "##0.00") + ", '" + _
                        Replace(othComment, "'", "''") + "')"
  db.Execute sql, dbFailOnError
  Exit Sub
foundError:
  MsgBox "Error in storeOthType: " + CStr(Err.Number) + ", " + _
       Err.description + ". SQL: " + sql + "."
  logError "Error in storeOthType: " + CStr(Err.Number) + ", " + _
       Err.description + ". SQL: " + sql + "."
End Sub 'storeOthType

任何人都知道是什么原因造成的?我们正计划将后端迁移到SQL Server，我怀疑它将解决此问题，但是在这种情况发生之前，如果我能够解决这个问题，那就太好了.

Anyone have an idea on what's causing this? We're planning on moving the back end to SQL Server, which I suspect will solve this problem, but until that happens, it would be nice if I was able to resolve this...

推荐答案

表中期望 Null 的空字符串可能使用''.

It could be the use of '' for an empty string where the table expects Null.

您可能会发现此函数很方便，可以避免出现以下情况:

You may find this function handy to avoid exactly that:

' Converts a value of any type to its string representation.
' The function can be concatenated into an SQL expression as is
' without any delimiters or leading/trailing white-space.
'
' Examples:
'   SQL = "Select * From TableTest Where [Amount]>" & CSql(12.5) & "And [DueDate]<" & CSql(Date) & ""
'   SQL -> Select * From TableTest Where [Amount]> 12.5 And [DueDate]< #2016/01/30 00:00:00#
'
'   SQL = "Insert Into TableTest ( [Street] ) Values (" & CSql(" ") & ")"
'   SQL -> Insert Into TableTest ( [Street] ) Values ( Null )
'
' Trims text variables for leading/trailing Space and secures single quotes.
' Replaces zero length strings with Null.
' Formats date/time variables as safe string expressions.
' Uses Str to format decimal values to string expressions.
' Returns Null for values that cannot be expressed with a string expression.
'
' 2016-01-30. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function CSql( _
    ByVal Value As Variant) _
    As String

    Const vbLongLong    As Integer = 20
    Const SqlNull       As String = " Null"

    Dim Sql             As String
    Dim LongLong        As Integer

    #If Win32 Then
        LongLong = vbLongLong
    #End If
    #If Win64 Then
        LongLong = VBA.vbLongLong
    #End If

    Select Case VarType(Value)
        Case vbEmpty            '    0  Empty (uninitialized).
            Sql = SqlNull
        Case vbNull             '    1  Null (no valid data).
            Sql = SqlNull
        Case vbInteger          '    2  Integer.
            Sql = Str(Value)
        Case vbLong             '    3  Long integer.
            Sql = Str(Value)
        Case vbSingle           '    4  Single-precision floating-point number.
            Sql = Str(Value)
        Case vbDouble           '    5  Double-precision floating-point number.
            Sql = Str(Value)
        Case vbCurrency         '    6  Currency.
            Sql = Str(Value)
        Case vbDate             '    7  Date.
            Sql = Format(Value, " \#yyyy\/mm\/dd hh\:nn\:ss\#")
        Case vbString           '    8  String.
            Sql = Replace(Trim(Value), "'", "''")
            If Sql = "" Then
                Sql = SqlNull
            Else
                Sql = " '" & Sql & "'"
            End If
        Case vbObject           '    9  Object.
            Sql = SqlNull
        Case vbError            '   10  Error.
            Sql = SqlNull
        Case vbBoolean          '   11  Boolean.
            Sql = Str(Abs(Value))
        Case vbVariant          '   12  Variant (used only with arrays of variants).
            Sql = SqlNull
        Case vbDataObject       '   13  A data access object.
            Sql = SqlNull
        Case vbDecimal          '   14  Decimal.
            Sql = Str(Value)
        Case vbByte             '   17  Byte.
            Sql = Str(Value)
        Case LongLong           '   20  LongLong integer (Valid on 64-bit platforms only).
            Sql = Str(Value)
        Case vbUserDefinedType  '   36  Variants that contain user-defined types.
            Sql = SqlNull
        Case vbArray            ' 8192  Array.
            Sql = SqlNull
        Case Else               '       Should not happen.
            Sql = SqlNull
    End Select

    CSql = Sql & " "

End Function

请研究在线注释以了解典型用法.

Please study the in-line comments for typical usage.

这篇关于进行插入时，MS-Access运行时错误3073的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！