Database cannot be opened (Access 2003)

Topic

New Reply

I want to fill an array with the content of a table and am using the following code which I’ve hacked out of the Access Help file
Unfortunately whilst it appears to do what I want, I find I am unable to add new forms etc because of an error ‘the database has been placed in a satate by user ‘Admin’ on machine ‘Tower’ that prevents it from being opend or locked’ everytime I try to start the form wizard

I am sure that the problem is within this code because if I disable it the form wizard runs as normal

Can anyone see why this routine leaves the database locked up please?

Public Sub Array_fill_meals()
‘Fill the meal array with meal code information
‘meal array = avarMealCode(15,4)

Dim dbsRegistration As Database
Dim rstMeals As Recordset
Dim avarMeals As Variant
Dim intRows As Integer
Dim intCol As Integer

‘Where is the database?
strRegistrationPath = Application.CurrentProject.Path & “” & “Registration-Data.mdb”

‘ MsgBox “Path to the current database file = ” & strRegistrationPath

‘Set ‘containers’
Set dbsRegistration = OpenDatabase(strRegistrationPath)
Set rstMeals = dbsRegistration.OpenRecordset(“tblMealCodes”, dbOpenSnapshot)

‘Fill the temporary array with table items (15 items in MealCode table)
avarMeals = rstMeals.GetRows(15)
‘ MsgBox ” ubound columns= ” & UBound(avarMeals, 1)
‘ MsgBox ” ubound rows= ” & UBound(avarMeals, 2)

‘Copy the data from the temporary array to my array
For intRows = 0 To UBound(avarMeals, 2)
avarMealCode(intRows + 1, 1) = avarMeals(0, intRows)
avarMealCode(intRows + 1, 2) = avarMeals(1, intRows)
avarMealCode(intRows + 1, 3) = avarMeals(2, intRows)
avarMealCode(intRows + 1, 4) = avarMeals(3, intRows)
Next intRows

‘Close the containers to tidy-up
rstMeals.Close
dbsRegistration.Close

End Sub

Reply | Quote

Replies

Is Registration-Data.mdb the current database? If so, why not use CurrentDb?

Also, you should set both object variables to Nothing at the end:

rstMeals.Close
dbsRegistration.Close
Set rstMeals = Nothing
Set dbsRegistration = Nothing

Reply | Quote

I notice that the objects are closed then set to nothing.

I have always set the objects to nothing, I don’t close them first, does it matter?

Maybe if DAO was used explicitly it may make a difference.

Reply | Quote

You should always close recordsets when you don’t need them any more. For example, if you have opened a recordset on a linked table, the connection to the backend database will remain until the recordset is closed.

Reply | Quote

Thanks Hans

Reply | Quote

Hans – thankyou – CurrentDB appears to have done it – going through the code I can see that with the daabase already open, that piece of code was trying to re-open it

I note your Close comments too- thankyou

Reply | Quote