Delete a Table via Macro

[nc]

In my database (created pre-2007) I used RunCommand Delete in a Macro to
delete a temporary table which had been created earlier in the Macro. Now
that I have upgraded to Access 2007 I am getting an error message. It says
that Delete is not available. What can I use instead?

 

[Jack Leach]

Write a function in VBA do to it.

DoCmd.Delete

see the help for argument details.

If you are intent on running from a macro use RunCode with your function
name as the macro argument.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[nc]

Thanks Jack for the suggestion. Sorry for the delay in my response but my
internet connection just went AWOL.

I'm afraid I'm a complete novice regarding Visual Basic. Are you able to
guide me through what I need to do to make your suggestion work?

 

[ryguy7272]

This is the easiest way I can think of doing it. Hit Alt+F11, Crate a new
Module and paste the below cod into the window that opens:

Option Compare Database

Function DeleteTableMacro()
DoCmd.SetWarnings False
DoCmd.RunSQL ("DELETE * FROM [tblYourTable];")
DoCmd.SetWarnings True
End Function

Replace [tblYourTable] with your actual table name.

Create a new Form. Create a Button control. Cancel. Right-click > Build
Event > Code Builder > Ok

Paste the code below into the window that opens:
Private Sub Command1_Click()
Call DeleteTableMacro
End Sub

Click the Red X. Save > Ok > Ok.

Open the Form and you’re in business.

HTH,
Ryan---

 

[nc]

Thanks Ryan for the guidance which I got this morning.
As I wanted the procedure to work automatically within the macro, I didn't
want to have to physically click a button. But I decided that if I created a
dummy form and put the code in 'On open' then all I needed the macro to do
was open and then close the dummy form.
I also tripped over another piece of code in the Microsoft Office
Development Centre. This uses DROP TABLE. So I have adapted the following
code (and it works).
Sub DropX2()

Dim dbs As Database

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")

' Delete the Employees table.
dbs.Execute "DROP TABLE Employees;"

dbs.Close

End Sub
Thanks again for your help.

This is the easiest way I can think of doing it. Hit Alt+F11, Crate a new
Module and paste the below cod into the window that opens:

Option Compare Database

Function DeleteTableMacro()
DoCmd.SetWarnings False
DoCmd.RunSQL ("DELETE * FROM [tblYourTable];")
DoCmd.SetWarnings True
End Function

Replace [tblYourTable] with your actual table name.

Create a new Form. Create a Button control. Cancel. Right-click > Build
Event > Code Builder > Ok

Paste the code below into the window that opens:
Private Sub Command1_Click()
Call DeleteTableMacro
End Sub

Click the Red X. Save > Ok > Ok.

Open the Form and you’re in business.

HTH,
Ryan---



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Thanks Jack for the suggestion. Sorry for the delay in my response but my
internet connection just went AWOL.

I'm afraid I'm a complete novice regarding Visual Basic. Are you able to
guide me through what I need to do to make your suggestion work?