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 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 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.