Table Won't Delete

[catbeasy]

I have some code that exports a table to a users temp drive as an excel file (using transferspreasheet) then, I want the table that was used to be destroyed..
However, after the transferspreadsheet command is run, I get an error saying that the db engine couldn't lock table tblname* because its already in use by another person or process..
It seems almost as if there is some hold on the table created by the transfer spreadsheet code. Any ideas on how to release the hold or otherwise make this work?
Thanks!
*tbl name is variable based on the user input at beginning of form..

If fcn_confirm(str_msg_send_email) = True Then
Call sub_save_req_to_drive 'save to drive and eventually send to email
DoCmd.DeleteObject acTable, str_obj_name_tbl
End If
here is the sub_save_req_to_drive code:
Public Sub sub_save_req_to_drive()
Dim fso, f
Dim str_folder_mod As String 'for creating/deleting folder
Dim str_folder_exp As String 'for export per the transferspreadsheet command, delete won't work with the slash ("/")
Dim frm As Form
Set frm = Forms!frm_pr_proc_sel_req

str_folder_mod = "c:\temp\maaps"
str_folder_exp = "c:\temp\maaps\"


Set fso = CreateObject("Scripting.FileSystemObject")
If fso.folderexists(str_folder_mod) Then
fso.deleteFolder (str_folder_mod)
End If

fso.CreateFolder (str_folder_mod)

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, frm.str_obj_name_tbl, str_folder_exp & "pr_" & frm.str_obj_name_tbl, True

 

[DCrake]

Why are you wanting to delete the table? At some point you must have created a new one. Why not simply delete the contents of the host table and append data to it. Creating and deleting tables is much more agressive than clearing and appending to an existing table.


The only reason I can see for doing this is that the structure of the table will change everytime.

David

 

[catbeasy]

Why are you wanting to delete the table? At some point you must have created a new one. Why not simply delete the contents of the host table and append data to it. Creating and deleting tables is much more agressive than clearing and appending to an existing table.


The only reason I can see for doing this is that the structure of the table will change everytime.

David

I have to delete the table, it is a temp table. Allowing it to exist would unecessarily clutter the database. I cannot change this.

Do you have any ideas why I cannot delete the table?

 

[MSAccessRookie]

I have to delete the table, it is a temp table. Allowing it to exist would unecessarily clutter the database. I cannot change this.

Do you have any ideas why I cannot delete the table?

You appear to see the Table as a temporary resource, to be disposed of when you are through with it. I see it as a recyclable storage area to be reset and used over and over again. One advantage to doing it this way, is that you will no longer get the error that you are referring to. Another advantage is that you do not need to recreate the Table each time that you want to use it. One or two Tables does not usually amount to much clutter.

I believe you will find that David's view is similar to mine.

 

[catbeasy]

You appear to see the Table as a temporary resource, to be disposed of when you are through with it. I see it as a recyclable storage area to be reset and used over and over again. One advantage to doing it this way, is that you will no longer get the error that you are referring to. Another advantage is that you do not need to recreate the Table each time that you want to use it. One or two Tables does not usually amount to much clutter.

I believe you will find that David's view is similar to mine.

I understand what you are saying, however, I cannot change the way I have set the database up..

In that light Are you saying that you do not know why I am getting the error? If you don't know/cannot help me, then please let me know..I fully understand that you do not approve of the methodology I'm using, but again, it cannot be changed at this point..

 

[speakers_86]

I dont know that it will work, but you can certainly try this little fix.

If fcn_confirm(str_msg_send_email) = True Then
Call sub_save_req_to_drive 'save to drive and eventually send to email
docmd.Close acTable,"your table name here",acsaveno
DoCmd.DeleteObject acTable, str_obj_name_tbl
End If

Or maybe the module being called isnt finished working. If thats that case, then replace the close command with a code that waits for x seconds.

 

[HiTechCoach]

You may also try adding a DoEvents and/or refresh the database window before you try to delete the table.

I actually use a temp database to hold my temp tables. Then I just delete the database file. This avoids all the database bloat and additional maintenance requirements..
See: The TempTables.MDB illustrates how to use a temporary MDB in your Microsoft Access app.

 

[catbeasy]

Thanks for that Speakers 86..

Actually, I figured it out. In my code, I do a count to see how many records there are in the temp table. In doing so, I create a recordset where I set the object variable "r" to the temp table.

I have to release that object variable from the table since it is "occupying" the table.

This is an interesting discovery as prior to this, I had seen code that used recordsets always end where they set the recordset variable to Nothing and wondered why. Now I know. Perhaps in the future, i'll gain a similar insight as to why its a bad idea to use temp tables?

 

[MSAccessRookie]

Thanks for that Speakers 86..

Actually, I figured it out. In my code, I do a count to see how many records there are in the temp table. In doing so, I create a recordset where I set the object variable "r" to the temp table.

I have to release that object variable from the table since it is "occupying" the table.

This is an interesting discovery as prior to this, I had seen code that used recordsets always end where they set the recordset variable to Nothing and wondered why. Now I know. Perhaps in the future, i'll gain a similar insight as to why its a bad idea to use temp tables?

I am glad to hear that you found the root of your problem. I also wanted to say that my point was not to say that it is a bad idea to use temp tables, but that creating and deleting them over and over again is not always necessary.

 

[catbeasy]

I am glad to hear that you found the root of your problem. I also wanted to say that my point was not to say that it is a bad idea to use temp tables, but that creating and deleting them over and over again is not always necessary.

Yeah, I can appreciate reasons for that. Actually, this project is an overhaul of a previous database I created that didn't use temp tables. It did work, but I didn't like what I had to do relative to some of the processes in the database. So, I thought of giving each user their own table and then processing everything based on that.

I like to experiment with many different ways of doing things and then compare results. There is a fine line between not wanting to recreate the wheel (read: good to use the tried and trusted methods) and getting your experience by trying things in an alternative manner.

Regarding trying things in a different manner, the worst that could happen is that it won't work and you'll be back to the drawing board. However, my guess is that you'll come out of that a much better programmer than if you'd just used the standard template. Sometimes we frown on those that don't take the advice of someone whose "been there", but really, the experience gained by, er, direct experience has many benefits that seem to get clouded by the failure that was predicted by those "who knew".

Sometimes ya gotta let the kid touch the hot stove..peace..