Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Unanswered: Updating Temporary Table

    Greetings,
    I have a list box that I populate through a temporary table (t_tblTeacher). This is done from the following command in a module:

    Public Sub CopyTeachers()

    Dim strTableName As Variant
    Dim strTemp As String
    strTemp = "t_tblTeacher"
    strTableName = "tblTeacher"
    DoCmd.DeleteObject acTable, strTemp
    DoCmd.CopyObject , strTemp, acTable, strTableName

    End Sub

    I use this to create the temporary table prior to opening the form containing the list box. No problem. However, once the form is open, this command, if run, notes that the temporary table is locked, so cannot be deleted and thus ###error###. Understand the error (it makes sense), but I would still like to be able to reload the temporary table from the original using a simple "clear" button and a requery.

    Any easy solution? Should I try to disconnect and reconnect the list box record source?

    Regards

    John S
    Nanning China (at the moment)

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    That or you can delete everything from the table then fill it again with an INSERT query. What I don't understand, though, is why you need a temporary table as data source for the list. Can't you simply use a SELECT query on the original table?
    Have a nice day!

  3. #3
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1
    I am using temporary tables because in fact I have three list boxes. One list box contains teachers, and one courses. As a course is selected for a teacher, they are put in the third list box, and deleted from the teachers/course boxes. The ultimate in error checking: it is impossible to put two teachers with one course. The application is for choosing teachers for invigilation of exams. The list boxes function through the database and queries, not listbox commands (additem does not work in Access 2003).


    I suppose I could to a delete, insert combination for courses and teachers. Thanks for the ideas.

    Regards

    John S

  4. #4
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1
    Sorry, should read "requeries" not "queries"

  5. #5
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Resetting Temporary Tables Solutions

    There is another solution: go through a dummy form, close the original form, recopy the temporary tables (as objects), and then reopen the form and close the dummy. This can be done on the form open event (in the dummy form), and doesn't seem to be problematic (seems to work fine for me). Probably vastly faster than any SQL operation to delete and refill temporary tables. Anyone know of any potential pitfalls?

    BTW, it would be nice when people come to the forum seeking solutions to problems if they inform the forum of any solutions they come upon. Its all about sharing...

    Regards

    John S
    Currently Nanning, China

Posting Permissions

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