將記錄添加到訪問表（不一致的結果）

我正在使用 Excel 中的 VBA 專案作為記錄管理工具。在一種情況下，需要將一條記錄添加到具有不同欄位資訊的同一 Access DB 中的 2 個表中。我能夠成功地添加到第一個表（Tours_Items），它可以通過回圈可用行來添加多條記錄。但是，我無法將以下記錄添加到各自的表 (Tours_Tours) 中。執行代碼時出現以下錯誤：“運行時錯誤'438'：物件不支持此屬性或方法”。

在 Tours_Items 上執行的 VBA 與表名之外的 Tours_Tours 和 Tours_Items 所需的回圈功能幾乎相同，所以我完全被迷惑了，為什么它在更復雜的情況下無法正確執行。潛艇一個接一個地運行；Tours_Items，然后是 Tours_Tours。我什至嘗試只執行 Tours_Tours ，但仍然遇到同樣的錯誤。

這些欄位與 Access DB 欄位正確匹配（我已三重檢查），即使它們不匹配，VBA 也不會引發物件錯誤。Access DB 欄位被適當地配置為接受添加到記錄中的資料型別。

代碼：

Sub DBAddTours_Items()

Dim DBFile, Tbl               As String
Dim SQL                       As String
Dim j, NxtRowItems, NxtItemRw As Integer
Dim con, rs                   As Object
Dim wsToursTtl                As Worksheet

Set wsToursTtl = Worksheets("Tour Subtotal")

    'Fill Items to DB
        'Connect to the Database
            Set con = CreateObject("ADODB.connection")
        'Set File and tables
            DBFile = ThisWorkbook.path & "\" & "DailyInfo.accdb"
            con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile
        'Define
            NxtRowItems = WorksheetFunction.CountA(wsToursTtl.Range("B:B"))
            NxtItemRw = WorksheetFunction.CountA(Sheets("Tours_Items").Range("A:A"))   1
        'Create the ADODB recordset object.
            Set rs = CreateObject("ADODB.Recordset")
        'Define Table and SQL
            Tbl = "Tours_Items"
            SQL = "SELECT * FROM " & Tbl
            'Set the necessary recordset properties.
                With rs
                    .CursorType = 1   'adOpenKeyset on early binding
                    .LockType = 3     'adLockOptimistic on early binding
                'Open the recordset.
                    .Open SQL, con
                'Loop through and add all items
                     For j = 2 To NxtRowItems
                        .AddNew
                        .Fields("ItemID") = (j - 3)   NxtItemRw
                        .Fields("TourID") = wsToursTtl.Cells(j, 1)
                        .Fields("ItemNum") = wsToursTtl.Cells(j, 2)
                        .Fields("Admission") = wsToursTtl.Cells(j, 3)
                        .Fields("Ticket") = wsToursTtl.Cells(j, 4)
                        .Fields("Premium/BAT") = wsToursTtl.Cells(j, 5)
                        .Fields("Tour") = wsToursTtl.Cells(j, 6)
                        .Fields("Price") = wsToursTtl.Cells(j, 7)
                        .Update
                    Next j
                'Close Recordset
                    .Close
                End With
        'Close the connection.
            con.Close
    'Release Variables
        Set rs = Nothing
        Set con = Nothing
        SQL = ""
        Tbl = ""

End Sub

Sub DBAddTours_Tours()

Dim DBFile, Tbl               As String
Dim SQL                       As String
Dim con, rs                   As Object
Dim wsToursTtl                As Worksheet

Set wsToursTtl = Worksheets("Tour Subtotal")

    'Fill Tour info Tours_Tours
        'Connect to the Database
            Set con = CreateObject("ADODB.connection")
        'Set File and tables
            DBFile = ThisWorkbook.path & "\" & "DailyInfo.accdb"
            con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile
        'Create the ADODB recordset object.
            Set rs = CreateObject("ADODB.Recordset")
        'Define Table and SQL
            Tbl = "Tours_Tours"
            SQL = "SELECT * FROM " & Tbl
            'Set the necessary recordset properties.
                With rs
                    .CursorType = 1   'adOpenKeyset on early binding
                    .LockType = 3     'adLockOptimistic on early binding
                'Open the recordset.
                    .Open SQL, con
                'Add new record to table
                    .AddNew
                    .Field("TourID") = wsToursTtl.Range("TourID_Dest").Value
                    .Field("TourDate") = wsToursTtl.Range("TourDate_Dest").Value
                    .Field("TourTime") = wsToursTtl.Range("TourTime_Dest").Value
                    .Field("Premium/Bat/Disc") = Me.PremiumBAT_Lbl.Caption
                    .Field("TourType") = wsToursTtl.Range("TourType_Dest").Value
                    .Field("GuestName") = wsToursTtl.Range("GuestName_Dest").Value
                    .Field("GuestAddress") = wsToursTtl.Range("GuestAddress_Dest").Value
                    .Field("GuestPhone") = wsToursTtl.Range("GuestPhone_Dest").Value
                    .Field("GuestEmail") = wsToursTtl.Range("GuestEmail_Dest").Value
                    .Field("GuestMember") = wsToursTtl.Range("GuestMem_Dest").Value
                    .Field("TourComments") = wsToursTtl.Range("Comments_Dest").Value
                    .Field("SaleDate") = wsToursTtl.Range("SaleDate_Dest").Value
                    .Field("TransactionDetails") = wsToursTtl.Range("Trxn_Dest").Value
                    .Field("SoldBy") = wsToursTtl.Range("SoldBy_Dest").Value
                    .Field("PaymentType") = wsToursTtl.Range("PayType_Dest").Value
                    .Field("BookedNum") = 2
                    .Field("LastModified") = Now
                    .Field("ModifiedBy") = Application.UserName
                    .Update
                'Close Recordset
                    .Close
                End With
                
        'Close the connection.
            con.Close
    'Release Variables
        Set rs = Nothing
        Set con = Nothing
        SQL = ""
        Tbl = ""

End Sub

任何幫助深表感謝！

uj5u.com熱心網友回復：

訪問欄位的正確集合是.Fields 復數。

因此DBAddTours_Items，在您的其他程式中，DBAddTours_Tours您呼叫了.Field 單數是正確的。哪個不存在。

此處找到的檔案：https ://docs.microsoft.com/en-us/sql/ado/guide/data/the-fields-collection?view=sql-server-ver15

標籤：擅长 vba 毫秒访问

上一篇：如何更改Django中默認UserCreationForm的文本顏色？

下一篇：“Null”不能分配給“Position”型別的變數