Solved: Do you hate the prompt "The existing table will be deleted before you run query" ?

[Tom]

I used to HATE that prompt! So this is a little info for beginning coders.

Let's say you're going to create a table called "work", so you write this line:


DoCmd.RunSQL "SELECT data.DEPARTMENT_NAME, data.FIRST_NAME, _
data.MIDDLE_INIT INTO [Work] FROM data ORDER BY data.DEPARTMENT_NAME;"

The next time you run it you get the "The existing table will be deleted before you run query" prompt.

OF COURSE YOU KNOW YOU?RE GOING TO DELETE THE TABLE!!

So you add this line above the other:


DoCmd.DeleteObject acTable, "Work"

DoCmd.RunSQL "SELECT data.DEPARTMENT_NAME, data.FIRST_NAME, _
data.MIDDLE_INIT INTO

[Work] FROM data ORDER BY data.DEPARTMENT_NAME;"

Now this works great unless your code stalls out and you have to rerun the script.

What happens?

You get ?Microsoft Office can?t find the object ?work? because the table?s already been deleted.

What do you do? You add this neat little sub routine that tells the system, ?If you find the object ?work? delete it, if not keep on going! And it looks something like this:


Sub CREATE_TABLE

'Put the sub routine call here before the create table SQL

D_WORK

DoCmd.RunSQL "SELECT data.DEPARTMENT_NAME, data.FIRST_NAME, _
data.MIDDLE_INIT INTO [Work] FROM data ORDER BY data.DEPARTMENT_NAME;"

End Sub

And the sub routine runs before the create table script


Sub D_WORK()
Dim DBS
Dim TDF
Dim T
Dim TD
Set DBS = CurrentDb
Set TDF = DBS.TableDefs
For Each TDF In CurrentDb.TableDefs
If TDF.Name = "WORK" Then
DoCmd.DeleteObject acTable, "WORK"
Exit For
End If
Next
End Sub

Anyhooooooo, when I started writing code I had to figure this one out on my own. I hope I can save a novice code writer some heartburn :)

If I've confused anyone, please let me know and I'll try to explain things a blt better.

[mdmackillop]

Hi Tom
When you're posting code,please highlight the code sections and click the VBA button to format the code as shown. It makes it easier to read. You may need to add some linebreaks as well, for those of us without huge screens.
Regards
MD

[mdmackillop]

Rather than making D_Work so specific, you can pass the name of the object to be tested, which allows the same code to be used by other procedures

Sub test()
D_WORK "Work"
End Sub

Sub D_WORK(objName as String)
Dim TDF
For Each TDF In CurrentDb.TableDefs
If TDF.Name = objName Then
DoCmd.DeleteObject acTable, objName
Exit For
End If
Next
End Sub

[Tom]

Dude,
Thanks for the code modification. As this is only my second entry, I'm still learning the ropes. I tried [color = blue] but it didn't work at my end and I had no idea about the single code screen entry. Additionally, I did not think my screen was bigger than yours (does that sound like "screen envy")) ? I guess the best thing for me is (when) I try to add a little help I'll just do it as a zip file to show my basic code layout.

[Ken Puls]

Hey Tom,

Malcolm's not trying to be hard on you. The screen width is always an issue. :)

Here, I found our tags that give us instructions on how to add the VBA tags:

a

HTH,

[mdmackillop]

Hey Tom,

Malcolm's not trying to be hard on you. The screen width is always an issue. :)

Here, I found our tags that give us instructions on how to add the VBA tags:

a

HTH,
Hi Tom,
Just as Ken said. As the linebreak issue doesn't appear until you use the VBA tags, I just thought I would mention it now.
Regards
MD

[Tom]

Ken and Macolm,
No offense taken at all (really)! I'll read up on how to "code" my code and any assistance would be GREAT! And Macolm, your ititial post gave be a kool idea, how about I take your suggestion one step further....and use a PUBLIC statement to carry the name of the table to be deleted so it can be changed and used in any sub in the script?
Thanks,
Tom


Option Compare Database
Public WW As String
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
WW = "A_TABLE"
DD
DoCmd.RunSQL "SELECT One.EMPLOYEE, One.LAST_NAME, One.FIRST_NAME, One.EMP_STATUS INTO A_TABLE FROM One;"
WW = "B_TABLE"
DD
DoCmd.RunSQL "SELECT One.EMPLOYEE, One.LAST_NAME, One.FIRST_NAME, One.EMP_STATUS, One.FICA_NBR INTO B_TABLE FROM One;"
Exit_Command0_Click:
Exit Sub
Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub
Sub DD()
Dim Dbs
Dim TDF
Dim T
Dim TD
Set Dbs = CurrentDb
Set TDF = Dbs.tabledefs
For Each TDF In CurrentDb.tabledefs
If TDF.NAME = "" & WW & "" Then
DoCmd.DeleteObject acTable, "" & WW & ""
Exit For
End If
Next
End Sub

[mdmackillop]

Hi Tom,
Public declaration seems fine. You are declaring a few items not used in the DD sub, in fact only TDF is used, and I'm not sure that the Set TDF statement is required either.

[Tom]

Macolm,
You're right. I removed all the Dim statements and the sub still worked fine! I don't know if that would be the case if I was using Access 2000 instead of 2003. If I remember right, 2000 was REAL pickey about DIMs. I owe you a cup of coffee :)


Sub DD()
Set Dbs = CurrentDb
Set TDF = Dbs.tabledefs
For Each TDF In CurrentDb.tabledefs
If TDF.NAME = "" & WW & "" Then
DoCmd.DeleteObject acTable, "" & WW & ""
Exit For
End If
Next
End Sub

[mdmackillop]

Hi Tom,
You can loop if you want

Option Compare Database
Public WW As String
Private Sub Command0_Click()
WW = "Table1"
DD
End Sub

Sub DD()
Dim TDF As TableDef
For Each TDF In CurrentDb.TableDefs
If TDF.Name = WW Then
DoCmd.DeleteObject acTable, WW
Exit For
End If
Next
End Sub
otherwise you could simply use error handling to step through if the table doesn't exist

Sub DD()
On Error Resume Next
DoCmd.DeleteObject acTable, WW
End Sub


BTW, Variables should not be contained within quotes.

[geekgirlau]

Of course, we could always use the simple method:


DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT data.DEPARTMENT_NAME, data.FIRST_NAME, _
data.MIDDLE_INIT INTO [Work] FROM data ORDER BY data.DEPARTMENT_NAME;"
DoCmd.SetWarnings True

[Ken Puls]

I removed all the Dim statements and the sub still worked fine! I don't know if that would be the case if I was using Access 2000 instead of 2003. If I remember right, 2000 was REAL pickey about DIMs. I owe you a cup of coffee :)

Try putting Option Explicit as the first line of your module and see if it runs... I'm betting you'll have issues.

You should use Option Explicit to force variable declaration. I think what Malcolm was getting at was that you had variables declared that weren't being used. Those aren't necessary.

FYI, you also had all of your variables declared as Variants. You'll get more stable code and better performance if you actually specify the types.. (See how Malcolm Dim's TDF vs the way you did.) :)

[Tom]

Hey, Everybody, l really appreciate the input. And, Macolm, thanks for the idea of reducing the Dims and using a Public.
Tom