Results 1 to 7 of 7

Thread: Deleting Table

  1. #1
    Join Date
    Nov 2003
    Posts
    55

    Unanswered: Deleting Table

    I'm having a problem deleting a table in MS Access. Here is the order of events:
    1) I create a table
    2) Then a SQL query is ran that populates the table.
    3) After that a MS Access Report is generated.
    4) ****Lastly I would like to delete the table

    Deleting the table is where I have the problem. I'm trying to delete the table in the reports close event. However, when doinig that I get: ***Run-time error '3211': The database engine could not lock table '<tablename>' because it is already in use by another person or process.***

    The only thing I can figure is that even in the close event of the report the the report is still using the table and won't let me delete the table.

    I am using the doCmd to run the sql queries. Could that be the problem?

    If any help or suggestions would be great.

    Thanks,

    Animaul.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Deleting Table

    Originally posted by Animaul
    I'm having a problem deleting a table in MS Access. Here is the order of events:
    1) I create a table
    2) Then a SQL query is ran that populates the table.
    3) After that a MS Access Report is generated.
    4) ****Lastly I would like to delete the table

    Deleting the table is where I have the problem. I'm trying to delete the table in the reports close event. However, when doinig that I get: ***Run-time error '3211': The database engine could not lock table '<tablename>' because it is already in use by another person or process.***

    The only thing I can figure is that even in the close event of the report the the report is still using the table and won't let me delete the table.

    I am using the doCmd to run the sql queries. Could that be the problem?

    If any help or suggestions would be great.

    Thanks,

    Animaul.
    The table in question is still bound to a form or control or report ... Unbind it.

    Deletion using VBA code:

    SQLString="DROP TABLE MyTableNameHere;"
    DoCmd.RunSQL SQLString

    -or-

    DoCmd.DeleteObject acTable, TableNameVariableHere

  3. #3
    Join Date
    Nov 2003
    Posts
    55
    Thats what I have been trying to do in the close event of the report. That is when I get the error.

    Thanks,

    Animaul

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by Animaul
    Thats what I have been trying to do in the close event of the report. That is when I get the error.

    Thanks,

    Animaul
    SORRY! Just read your posting more carefully - infinitely more carefully ... You can't do that. Don't try to delete the table the report is based on/bound to within the report itself... You can't. You'll have to delete it external to the report. Where are you creating the table the report runs on? In the report? External to it? By what means?

  5. #5
    Join Date
    Nov 2003
    Posts
    55
    Thanks.

    I found away around it.

    Animaul

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by Animaul
    Thanks.

    I found away around it.

    Animaul
    And that way was?????

  7. #7
    Join Date
    Nov 2003
    Posts
    55
    Sorry about that. I did not know if you were interested. First let me say that I'm maintaining a DB that someone else built.

    1)On the Switchboard Load event I create the table.
    2)Everytime the report that I generate is closed I Delete all the records in the table.
    3)Then on the Close event of the Switchboard I delete the table.

    So at least the table is only permanent while the DB is running. This is probably more efficient because I'm not creating and deleting the table on the fly everytime the report is run.

    My theory is that even though I'm in the clsoe event the report is not totally closed because it is still running through the close event of the report.

    However, I can delete all the records in the table with no problem. So maybe it has generated a clone of the table which preserves the records and also the link to the table I wanted to delete, which would allow me to delete the records but no the table.

    This is all speculation and I have yet to hear a beeter answer.

    Thanks,

    Animaul

Posting Permissions

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