Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011

    Unanswered: Error 3045 when opening two recordsets


    I am sure someone more expert will spot what I am doing wrong quickly but I have bashed my head on it for a while and read many threads without seeing what the problem is.

    I am opening two recordsets from two linked tables in the same backend database. On trying to open the second I get error 3045 "could not use dbname; file already in use. Here is code up to the point where it fails.

    I am using MS Access 2007. I am the only user.

    All help appreciated.

    Public Sub StatusAndMigration()

    Dim sql As String
    Dim dbs As DAO.Database
    Set dbs = CurrentDb()
    Dim rst As DAO.Recordset
    Dim rst1 As DAO.Recordset
    Dim BaseYear As Integer
    Dim MyYearEnding
    Dim MyTable As String
    Dim MyParentNode As Integer
    Dim MyModel As String

    'Turn off confirmation of changes to the tables
    SetOption "Confirm Action Queries", False

    'set the base year from which the forecast is generated
    BaseYear = 2010

    'set MyTable to the correct table
    MyTable = "Status_All_Years"

    'Set which model to use
    MyModel = "RegionStatusModel"

    'For each year of the forecast
    For MyYearEnding = BaseYear To BaseYear + 19

    'Status and migration forecast for the next year

    'Open a recordest of the base year's fleet
    sql = "SELECT * FROM " & MyTable & " WHERE StatusYear = " & MyYearEnding & ";"
    Set rst = dbs.OpenRecordset(sql, dbOpenDynaset)

    sql = "SELECT * FROM " & MyModel & ";"
    Set rst1 = dbs.OpenRecordset(sql, dbOpenSnapshot)

    Thanks, Steplewie
    Last edited by steplewie; 06-17-11 at 10:55.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    I don't see anything wrong with your code. I tested here and it works. Can you simultaneously open both tables from the database window ?
    Have a nice day!

  3. #3
    Join Date
    Jun 2011

    Thanks - Solved!

    Thanks to all that looked and to Sinndho for the post. I believe that I have found the problem. Although the code was fine, I had not used good practice and include an error handler that closed the connections. I suspect that on testing the code an error occurred that stopped the code without closing the connection. The next time that I ran code, I received the error.

    Now that I have included an error handler that closes deassigns everything, it works fine.

    A lesson learned.

  4. #4
    Join Date
    Mar 2009
    Provided Answers: 15
    Thanks for the feedback!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts