Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Database cannot be opened (Access 2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database cannot be opened (Access 2003)

    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

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Database cannot be opened (Access 2003)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Database cannot be opened (Access 2003)

    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.

  5. #5
    New Lounger
    Join Date
    Jun 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database cannot be opened (Access 2003)

    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

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Database cannot be opened (Access 2003)

    Thanks Hans

Posting Permissions

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