安装 Office 2007 后编译 VBA 应用程序时出错

Question

自从我们安装了 Office 2007 以来，我们的 VBA 应用程序的代码就停止工作了。当我调试时，出现编译错误，并且 .Edit 突出显示。我将 .Edit 替换为 .Update，并且没有收到任何调试错误，但当我运行代码时，收到类型不匹配错误代码。我做错了什么吗？

这是代码：

Private Sub Command290_Click()
On Error GoTo Err_Command290_Click
'This routine imports the latest Changepoint CSV file into the ChangepointCSV table.
'SR: valid routine

    Dim FullFileName As String      'full file path & name
    Dim myDB As Database
    Dim rstAsOfDate As Recordset
    Dim rstCumulativeResources As Recordset
    Dim strOldDate As String
    Dim tableExists As Integer
    Dim strExistingCSVTable As String
    Dim transferSuccessful As String
    Dim deleteBackup As Boolean

'set default values
    Set myDB = CurrentDb
    strExistingCSVTable = "ChangepointCSV"

'form maintenance to restrict user options
    DoCmd.Close acForm, "frmMain", acSaveNo
    DoCmd.OpenForm "frmImportingCPData"

'get name of the existing CSV file
    'MsgBox ("before RS set")
    Set rstAsOfDate = myDB.OpenRecordset("tblChangepointFileName")
    'MsgBox ("after RS set")

    With rstAsOfDate
        'MsgBox ("inWITH")
       .Edit
        'Store original data.
        strOldDate = !CurrentFileName
    End With

    rstAsOfDate.Close

'get name of file to be imported
    FullFileName = GetFile()
    'MsgBox ("DEBUG  FullFileName = " + FullFileName)

    'FullFileName = "C:Documents and Settings ext.xlsx"

'compare existing to latest
    If strOldDate = FullFileName Then
        MsgBox "The RI currently contains the latest Changepoint data extract."
        deleteBackup = False
        GoTo RestoreForms
    End If

'if Changepoint CSV table exists then back it up in case of an error
    tableExists = ObjectExists_20%("Tables", strExistingCSVTable)

    If tableExists = -1 Then
        DoCmd.CopyObject , "ChangepointCSV-backup", acTable, strExistingCSVTable
        DoCmd.DeleteObject acTable, strExistingCSVTable
    End If

'transfer the latest CSV file
    transferSuccessful = TransferSpreadsheetFile(strExistingCSVTable, FullFileName)
    'MsgBox ("DEBUG:  Transfer Successful:  " + transferSuccessful)

'if the lastest CSV file was NOT imported, restore the backup CSV and exit,
'else continue processesing.

    If transferSuccessful = 0 Then
        DoCmd.CopyObject , strExistingCSVTable, acTable, "ChangepointCSV-backup"
        MsgBox "The Changepoint data could not be refreshed at this time.  Please try again later."
        deleteBackup = True
        GoTo RestoreForms:
    Else
        'MsgBox ("before RS set")
        Set rstAsOfDate = myDB.OpenRecordset("tblChangepointFileName")
        'MsgBox ("after RS set")

        'Update Filename
            With rstAsOfDate
                'MsgBox ("inWITH")
                .Edit
                'Store original data
                !CurrentFileName = FullFileName
                .Update
            End With

        rstAsOfDate.Close
        'MsgBox ("RS closed")

        Set rstCumulativeResources = myDB.OpenRecordset("tbl_CumulativeResources")
        Do While Not rstCumulativeResources.EOF
            rstCumulativeResources.Delete
            rstCumulativeResources.MoveNext
        Loop

        rstCumulativeResources.Close

        DoCmd.RunMacro "mcrFTEAnalysis"
        deleteBackup = True
        GoTo RestoreForms

    End If

'restores main form and cleans up backup file
RestoreForms:
    If deleteBackup = True Then
        DoCmd.DeleteObject acTable, "ChangepointCSV-backup"
    End If

    DoCmd.Close acForm, "frmImportingCPData", acSaveNo
    'MsgBox ("DEBUG:  import form closed")
    DoCmd.OpenForm "frmMain", acNormal

Exit_Command290_Click:
    Exit Sub

Err_Command290_Click:
    MsgBox Err.Description
    Resume Exit_Command290_Click
End Sub

Answer 1

我的第一个猜测是参考资料有问题。您使用 DAO 或 ADO 进行数据访问吗？检查引用后，尝试将 DIM 行更改为 DAO.Database 等（如果使用 DAO）和 ADODB.Database（如果使用 ADO）。

因为 DAO 和 ADO 都包含一个 .Database 对象，VBA 有时会混淆您指的是哪一个，以及内存中不同版本的默认更改