Excel VBA将表单用户输入写入Access表失败，求排查原因

Hey there, let's walk through the issues in your code and get it working properly. Here are the key problems and how to resolve them:

Key Issues in Your Code #

• Invalid Connection Initialization: You can't assign a string directly to an ADODB.Connection object. The line Set cnn = "db.accdb.connection" is incorrect—you need to properly initialize the connection, set its connection string, and open it.
• Unused Variable: The wsh As Excel.Application declaration isn't used anywhere in your code; you can safely remove it unless you plan to use it for other Excel operations later.
• Missing Connection Open: Even if you had the right connection setup, you never called cnn.Open to establish the link to your Access database.
• Potential Library Reference: If constants like adOpenKeyset or adLockOptimistic throw errors, you need to add a reference to the Microsoft ActiveX Data Objects x.x Library (go to Tools > References in the VBA editor to enable this).

Corrected Code #

Here's the revised version of your macro with fixes and explanations:

Sub AddDataToAccess()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim dbPath As String
    Dim textboxvar As String ' Adjust data type to match your Access field
    
    ' Update this to your actual Access database file path
    dbPath = "C:\Your\Full\Path\To\db.accdb"
    
    ' Initialize and open the database connection
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";"
    cnn.Open
    
    ' Open the target table for record operations
    Set rst = New ADODB.Recordset
    rst.Open "table", cnn, adOpenKeyset, adLockOptimistic, adCmdTable
    
    ' Add the new record with your input value
    With rst
        .AddNew
        .Fields("column1").Value = textboxvar ' Ensure "column1" exists in your Access table
        .Update
    End With
    
    ' Cleanup: Close objects to avoid memory leaks
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
End Sub

Additional Checks to Ensure Success #

• Verify Database Path: Double-check that dbPath points to the correct location of your Access database.
• Table and Field Names: Make sure "table" is the exact name of your Access table, and "column1" matches the field name in the table.
• Data Type Match: Ensure textboxvar's data type aligns with the data type of "column1" in Access (e.g., use a numeric variable if the field is a number instead of a string).

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