通过SQL追加邮件分发信息到表时出现MS Access运行时错误3075

Hey, let's tackle that Runtime Error 3075 you're hitting in your Access VBA code. That error almost always points to a SQL syntax issue, and looking at your code snippet, there are a few key culprits we can address right away:

Common Causes of Error 3075 Here #

• Unescaped single quotes: If any of your input parameters (like iTo, iSubject, or iBody) contain a single quote (e.g., O'Neil), it breaks the SQL string structure by closing a quote early.
• Incomplete SQL syntax: Your snippet cuts off mid-iBody value, which would leave the SQL statement unfinished.
• Potential reserved word conflicts: While you wrapped [To] and [From] in brackets (good call, since those are Access reserved words), we still need to ensure all parts of the query align with the target table's schema.

Step-by-Step Fixes #

1. Escape Single Quotes in All String Parameters #

Before inserting values into your SQL string, replace any single quote in the input with two single quotes (Access's way of escaping them). This prevents syntax breaks from user input with apostrophes.

Here's how to adjust your string concatenation:

Function SendToEMail(iTo As String, iFrom As String, icc As String, ibcc As String, _
                     iSubject As String, iBody As String, iSystem As String, iAttachments As String)
    ' Escape single quotes in all string inputs
    Dim escapedTo As String: escapedTo = Replace(iTo, "'", "''")
    Dim escapedFrom As String: escapedFrom = Replace(iFrom, "'", "''")
    Dim escapedCC As String: escapedCC = Replace(icc, "'", "''")
    Dim escapedBCC As String: escapedBCC = Replace(ibcc, "'", "''")
    Dim escapedSubject As String: escapedSubject = Replace(iSubject, "'", "''")
    Dim escapedBody As String: escapedBody = Replace(iBody, "'", "''")
    Dim escapedSystem As String: escapedSystem = Replace(iSystem, "'", "''")
    Dim escapedAttachments As String: escapedAttachments = Replace(iAttachments, "'", "''")
    
    ' Build complete SQL statement with escaped values
    Dim strSQL As String
    strSQL = "INSERT INTO tblEmail ([To], [From], [CC], [BCC], [Subject], [Body], [Create_Time], [System], [Attachments]) " & _
             "IN '\\ahmtroy03\Email.accdb' " & _
             "VALUES ('" & escapedTo & "', '" & escapedFrom & "', '" & escapedCC & "', '" & escapedBCC & "', '" & _
             escapedSubject & "', '" & escapedBody & "', Now(), '" & escapedSystem & "', '" & escapedAttachments & "')"
    
    ' Execute the query with error handling
    CurrentDb.Execute strSQL, dbFailOnError
End Function

2. Use Parameterized Queries (Recommended for Safety & Reliability) #

String concatenation is prone to syntax errors and SQL injection risks. A better approach is to use a parameterized query with DAO, which handles escaping automatically:

Function SendToEMail(iTo As String, iFrom As String, icc As String, ibcc As String, _
                     iSubject As String, iBody As String, iSystem As String, iAttachments As String)
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    
    ' Connect to the external database
    Set db = OpenDatabase("\\ahmtroy03\Email.accdb")
    
    ' Define parameterized INSERT statement
    strSQL = "INSERT INTO tblEmail ([To], [From], [CC], [BCC], [Subject], [Body], [Create_Time], [System], [Attachments]) " & _
             "VALUES (?, ?, ?, ?, ?, ?, Now(), ?, ?)"
    
    Set qdf = db.CreateQueryDef("", strSQL)
    
    ' Assign parameters (order must match the ? placeholders)
    qdf.Parameters(0) = iTo
    qdf.Parameters(1) = iFrom
    qdf.Parameters(2) = icc
    qdf.Parameters(3) = ibcc
    qdf.Parameters(4) = iSubject
    qdf.Parameters(5) = iBody
    qdf.Parameters(6) = iSystem
    qdf.Parameters(7) = iAttachments
    
    ' Execute the query with error handling
    qdf.Execute dbFailOnError
    
    ' Cleanup resources
    qdf.Close
    db.Close
    Set qdf = Nothing
    Set db = Nothing
End Function

3. Verify Additional Details #

• Double-check that the path \\ahmtroy03\Email.accdb is accessible (you have read/write permissions to the network share).
• Confirm that tblEmail in the target database has all the fields you're inserting into, with matching data types (e.g., Create_Time should be a Date/Time field, which Now() handles correctly).
• Ensure your iAttachments parameter doesn't contain invalid characters that might break the SQL, though parameterization eliminates this risk.

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