Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    May 2003
    Location
    Pennsylvania, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cannot Create Table (Access 2003)

    I have a database that tracks speakers at conferences. Data for each speaker is stored in a table with a primary Autonumber key SpkrID, which is related to the conference data in another table. On the form that displays speaker data, I want to add a button to add a duplicate of the current speaker's record (with a new SpkrID of course) so that the user can change just the name and keep the rest of the data (address, etc.). (This is useful when several speakers from the same company are coming to the conference.) I added the button and used the wizard to generate the code to create the duplicate record, which works as expected:

    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70

    I also have another table which relates SpkrID with the alphabetical sequence of the names so that the user can click a "NextRecord" button and step through the speakers in alphabetical order. After adding the duplicate record, I am trying to regenerate this table (SpeakersSorted) by deleting it from the database and recreate it with a MakeTable query. (SpeakersSorted has 5 fields: LastName, FirstName, Initial, SpkrID, and AlphaSeq. SpkrId is an AutoNumber field, and AlphaSeq is a long integer. I tried setting SpkrID to a Number field, but when you run the MakeTable query manually, it resets it to AutoNumber.)

    Private Sub Form_Open(Cancel As Integer)
    'Makes new sorted speakers table and repopulates AlphaSeq field

    ' Delete old sorted table and recreate it with a MakeTable query

    DoCmd.SetWarnings False
    DoCmd.DeleteObject acTable, "SpeakersSorted"
    DoCmd.OpenQuery "Make New Sorted Speakers Table"
    DoCmd.SetWarnings True

    'Now that the names are in alpha order, go through the table and give them sequence numbers

    Dim db As DAO.Database
    Set db = CurrentDb

    Dim rs As DAO.Recordset
    Dim I As Integer
    Set rs = db.OpenRecordset("SpeakersSorted")

    I = 0
    rs.MoveFirst

    Do Until I = rs.RecordCount
    I = I + 1
    rs.Edit
    rs![AlphaSeq] = I
    rs.Update
    rs.MoveNext
    Loop

    rs.Close
    Exit Sub


    The problem I am having is that when the program tries to recreate the SpeakersSorted table, it aborts with Error 3211, "The Database Engine could not lock table 'SpeakersSorted' because it is already in use by another person or process". I am running this on a standalone PC--nobody else is using the table. And besides, the table should be gone by the time the MakeTable query is executed because the previous statement deleted it. So why is Access trying to lock a table that was deleted?

    Any help in explaining what's going on and how to get around this problem will be much appreciated.

    Thanks very much.

    Don.

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

    Re: Cannot Create Table (Access 2003)

    There is no need to create and use a SpeakersSorted table. You can create a query based on the Speakers table that sorts the records by name, and use this as record source for forms and reports, and as row source for combo boxes etc. The advantage of a query is that there is no superfluous double storage of speakers, and that the query will automatically reflect the current state of affairs.

  3. #3
    Star Lounger
    Join Date
    May 2003
    Location
    Pennsylvania, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cannot Create Table (Access 2003)

    Thanks very much Hans. This worked fine, and it greatly simplified things as well.

    Don.

Posting Permissions

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