Unable to automatically delete a table

[adammitchell]

Hi all,

I have a database that generates a unique ID in a form (frmDataEntryGreens) by running a maketable query (to create tblTempTable) and then running some other queries on that table to generate the ID. This all works fine. however, when I need to re-generate a new ID, I need to close those queries (which I do using macros on the OnClick property of the Close Form button).
Then I need to delete tblTempTable ready for the next ID to be generated; however, if I try to do this using DoCmd.DeleteObject I receive an error telling me that the table could not be locked because it's in use by another person or process. This is not the case; all tables and queries are close and I'm the only person using the database.
If I create a function and call that function from a macro, I get the error "The object doesn't contain the automation object..."



This is driving me up the wall - it's the last hurdle to get over in a massive project and it should be so simple!

Thanks!

Adam

[ranman256]

if you have any form open that uses the table, it will not delete. (it will think its a another person)
so close all forms (that could have the table open, then delete)

or
stop using a make table query , instead keep the same temp table and just empty it , then append data. dont delete tbl.

[ranman256]

for some reason , this posted twice here.

[adammitchell]

if you have any form open that uses the table, it will not delete. (it will think its a another person)
so close all forms (that could have the table open, then delete)

or
stop using a make table query , instead keep the same temp table and just empty it , then append data. dont delete tbl.

Thanks for your reply,

There are no forms using the table (or anything related); I can just go to the Temp Table and manually delete it and I get no error.

I'd initially planned to just use the same table and use a Delete query to empty it, but I need to use an autonumber field to generate part of the ID, so I needed to create a new temp table each time for that (the ID's are incremental, but need to have a prefixed letter, depending on the subject - in this case a G for Green Turtles or an H for Hawksbill Turtles).

Cheers!

[JoeM]

stop using a make table query , instead keep the same temp table and just empty it , then append data. dont delete tbl.

I would definitely go this route!

I'd initially planned to just use the same table and use a Delete query to empty it, but I need to use an autonumber field to generate part of the ID, so I needed to create a new temp table each time for that (the ID's are incremental

Can you explain in more detail? It seems to me that if you wanted to use Autonumber incrementally, that you want to use the same table, as creating a new table would start over every time.
If that is what you want to do (start over every time), you can do this without using Autonumber. I would create a User Defined Function in VBA to create the ID field. If you tell us exactly how it needs to look, we can probably help you with that.

[adammitchell]

I would definitely go this route!


Can you explain in more detail? It seems to me that if you wanted to use Autonumber incrementally, that you want to use the same table, as creating a new table would start over every time.
If that is what you want to do (start over every time), you can do this without using Autonumber. I would create a User Defined Function in VBA to create the ID field. If you tell us exactly how it needs to look, we can probably help you with that.

So, I have two sets of data about turtles and two separate methodologies for tagging them in two separate areas. There are also two species that could be encountered (Greens and Hawksbills). Each turtle tagged needs a unique ID which also defines the species (for greens this might be G5187 or hawksbills H5489) but the ID can't be repeated across the data sets because rarely, turtles move from one area to the other. Therefore, I query the ID's from the two respective data tables and append all to the TempTable (blank, but with an autonumber field), from which I can determine the next sequential ID and inform the user what this needs to be. So, if I have 10 Greens on record and 10 hawksbills, this appends 20 records to the new table and a query tells the user that the next turtle ID will be 21 (it also puts a G or H before depending on the species).
If I were to keep the same TempTable and just delete all records in it, the next time a user tries to identify the ID of the next turtle, it would read 42, not 22 because the autonumber would continue to count incrementally despite the records having been deleted.

Does that make sense?

[JoeM]

Does that make sense?

Yes.

A different approach to take would be to simply count the number of records in the table, and add "1" to it to get the next value you need.
I would probably create a UDF to do that, using the DCOUNT function to count the number of records in the existing table.

How exactly you run this depends on exactly how you are entering new data into your tables.
Are you importing information?
Entering it manually through a Form?
Do you have another field indicating what kind of turtle it is?