OpenRecordset fails after saving to Access 2007-2016 format RRS feed

  • Question

  • I have an Access DB made in 2002-2003-format.I used to run it on a Windows7 PC with Access 2010.

    Now I am running it on a Windows10 PC with Access from Office 365.

    Now it performs very poorly - about 25 times slower.

    I thought maybe saving it to the latest format would help - but after saving it to the default new format (2007-2016 format) some of the basic VBA statements fail.

    The following fails in the last statement:

        Dim dbs As Database
        Dim rst As Recordset
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("RTxt")

    "Runtime error '13': Type Mismatch"

    I have tried creating a completely ned DB in the new format (.accdb) and here the code works...

    So why does it not work in the old database when saved as .accdb?

    So I cant see if the new format would restore performance....

    Friday, November 30, 2018 3:30 PM

All replies

  • Hi Jake,

    Not sure about the rest of the issues you're having but to answer the "Type Mismatch" problem, try declaring your recordset variable like this:

    Dim rst As DAO.Recordset

    Hope it helps...

    Friday, November 30, 2018 3:51 PM
  • Adding to DBGuy's answer...Both objects need DAO specified as in

        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset

    The reason for this is that Access from 2000 going forward defaults to the ADO library. If I remember right, DAO wasn't even checked in the references in 2000 so you had to do it manually for each database project. If DAO is used you have to specify it for most of the objects.

    Bill Mosca

    Friday, November 30, 2018 4:44 PM
  • Thanks - the DAO. prefix solves that issue.

    Strange though that in a new database I dont need to do that and it works with

        Dim dbs As Database
        Dim rst As Recordset
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("Table1")

    But I guess the .accdb format adds this information by itself when its created in that format.

    And for the performance - it works, but is slower than in the original format.

    Maybe some of the code has "behind the scenes" logic given by the Access-version when it was originally written - and if the same code was written in 2016-Access it may use the "new" logic (like the DAO. prefix is not needed if written in the .accdb-format from scratch - and i.e. it must somehow be defined as default...)?

    So can some of the original code be stuck in some old and non-optimal logic?

    In particular there seems to be very slow performance with the following:

    Private Sub TruncateFunctionsLines_Click()
        Const ForReading = 1, ForWriting = 2, ForAppending = 8
        Dim FS, f
        Dim str As String
        Dim i As Long
        Set FS = CreateObject("Scripting.FileSystemObject")
        Set f = FS.OpenTextFile(CurDBDir & "\Functions1.txt", ForReading, 0)
        Call dump_to_file("")
        i = 1
        While Not f.AtEndOfStream 'true
            Call append_to_file(Left(f.readline, 254))
            i = i + 1
            If i Mod 200 = 0 Then
                Me.status = i
                Call pause(1)
            End If
        Me.status = "Done : " & i
        FileCopy CurDBDir & "\dbug.txt", CurDBDir & "\Functions.txt"
    End Sub

    This is a routine that reads one txt-file and writes it to another after having truncated all lines to max 254 chars.

    I'm sure this can be done in a better way - but as the program is a "Utility" automating a series of steps in converting a program from one language to another it has not been subject to refining as long as it performed well.

    The routines "dump_to_file" and "append_to_file" writes text to the file dbug.txt:

    Public Sub dump_to_file(text As String)
        Open Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name))) & "\dbug.txt" For Output As #1
        Print #1, text
        Close #1
    End Sub

    Public Sub append_to_file(text As String)
        Open Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name))) & "\dbug.txt" For Append As #1
        Print #1, text
        Close #1
    End Sub

    These routines are some long-ago-written utility-functions I keep using for all kinds of tracing purposes.

    So - does any of that stand out as being bad for Acces-365 and Win10?

    Monday, December 3, 2018 1:04 PM
  • I recently switched from Vista/Access 2010 to Win10/Access 2016, and I can't say that I've noticed any slower performance.  The code you've posted, while very inefficient, *should* not run noticeably slower between versions of Access, because it's clearly I/O bound, not processor-bound.  If it's running slowly, it seems to me that it must be due to something going on external to Access.  You might try unplugging from the internet, turning off your antivirus, and seeing if it still runs slowly under those conditions.  Of course, turn on your antivirus before connecting to the internet again.

    Aside from that, the only thing I can think of is to instrument your code with calls to a high-performance timer to see just how much time is being spent on different statements or blocks of code.

    Dirk Goldgar, MS Access MVP
    Access tips:

    Monday, December 3, 2018 8:14 PM
  • Thanks.

    Before I add timers I shall benchmark the "new" environment against the "old" environment - then I can see what else hase become slower.

    Then I can look at the individual steps to see what statements takes the most time. Normally anything involving opening/closing of files takes times - and if Virus-scan is different now, then that may be the reason. 

    Tuesday, December 4, 2018 3:06 PM