Problem with VBA app after office 2007 was installed

Question

Ever since we installed Office 2007, our VBA apps code stopped working. When I debug, I get a compile error and .edit is highlighted. I replaced the .edit with .update and i don't get any debug errors, but when i run the code i get a type mismatch error code. Is the something im doing wrong? Here is the codez:

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

My first guess would be a problem with references. Are you using DAO or ADO for your data access? After checking your references try changing the DIM lines to DAO.Database etc if using DAO and ADODB.Database if using ADO.

Because both DAO and ADO contain a .Database object VBA can sometimes get confused as to which one you mean and the default changes from version to version from memory