Delete a Table via Macro

  • Thread starter Thread starter nc
  • Start date Start date
N

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?
 
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)
 
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?
 
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---
 
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.

ryguy7272 said:
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''.


nc said:
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?
Click to expand...
Click to expand...
 
Back
Top