How to delete Table via macro? Example code attached

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

EagleOne@discussions.microsoft.com - 24 May 2008 19:24 GMT

2003

Using the following VBA Macro in Access:

Function Delete_Tables()
On Error GoTo Delete_Tables_Err

DoCmd.DeleteObject(acTable,"myTable")

Delete_Tables_Exit:
Exit Function

Delete_Tables_Err:
MsgBox Error$
Resume Delete_Tables_Exit

End Function

************************************************************************

PROBLEM: I get compile error "expected: =" error

HELP!!

TIA EagleOne

Reply to this Message

Chris O'C - 24 May 2008 19:40 GMT

In Access it's not called a VBA macro. You have a VBA procedure. VBA code
and macros are two different things.

Delete the parentheses around the acTable and "myTable" arguments.

Chris
Microsoft MVP

>2003
>
[quoted text clipped - 21 lines]
>
>TIA EagleOne

Reply to this Message

EagleOne@discussions.microsoft.com - 24 May 2008 19:50 GMT

Thanks for the ring Chris. I was experimenting ... but not enough for the gold ring.

EagleOne

>In Access it's not called a VBA macro. You have a VBA procedure. VBA code
>and macros are two different things.
[quoted text clipped - 29 lines]
>>
>>TIA EagleOne

Reply to this Message

Daniel Pineault - 24 May 2008 19:45 GMT

You don't need the brackets. You only use them when you are assigning the
value of the function back to a variable. Try the following (it worked for
me):

Function Delete_Tables()
On Error GoTo Delete_Tables_Err

DoCmd.DeleteObject acTable, "TestTable"

Delete_Tables_Exit:
Exit Function

Delete_Tables_Err:
MsgBox Error$
Resume Delete_Tables_Exit

End Function

Signature

Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.com/index.php
Please rate this post using the vote buttons if it was helpful.

> 2003
>
[quoted text clipped - 21 lines]
>
> TIA EagleOne

Reply to this Message

Daniel Pineault - 24 May 2008 19:56 GMT

Here a fast function I created based on your original one.

'---------------------------------------------------------------------------------------
' Procedure : DelTbl
' Purpose : Delete the specified table
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' strTable Name of the table to be deleted
'
Function DelTbl(strTable As String) As Boolean
On Error GoTo Error_Handler

DoCmd.SetWarnings False 'Disable prompts to confirm deletion
DoCmd.DeleteObject acTable, strTable
DoCmd.SetWarnings True 'Reenable prompts

If Err.Number = 0 Then
DelTbl = True
Exit Function
End If

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: DelTbl" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
DelTbl = False
Exit Function
End Function

You simply call it as follows:
DelTbl("TheTableNameToBeDeleted")

Signature

Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.com/index.php
Please rate this post using the vote buttons if it was helpful.

> You don't need the brackets. You only use them when you are assigning the
> value of the function back to a variable. Try the following (it worked for
[quoted text clipped - 39 lines]
> >
> > TIA EagleOne

Reply to this Message

EagleOne@discussions.microsoft.com - 24 May 2008 21:41 GMT

Excellent followup. Thanks!!!

>Here a fast function I created based on your original one.
>
[quoted text clipped - 30 lines]
>You simply call it as follows:
>DelTbl("TheTableNameToBeDeleted")

Reply to this Message

chezhaol - 24 May 2008 19:48 GMT

> 2003
>
[quoted text clipped - 21 lines]
>
> TIA EagleOne

Reply to this Message

Steve Schapel - 24 May 2008 22:25 GMT

EagleOne,

Just a hint for the future... If you really feel it is important for
your post to appear in more than one newsgroup (in practice this is
seldom necessary), it is preferable to cross-post (i.e. address the same
message simultaneously to both groups) rather than multi-post (i.e. send
a separate copy of the message to each group). Thanks.

Signature

Steve Schapel, Microsoft Access MVP

> 2003
>
[quoted text clipped - 21 lines]
>
> TIA EagleOne

Reply to this Message

EagleOne@discussions.microsoft.com - 24 May 2008 22:55 GMT

Thanks for your thoughts. That said, even thought the .macro group is the prime source for my
questions, there is very little response in that group. So I post both to: 1st get any response,
and 2nd, to see if I can wake up the .macro group.

In comparison, the Excel "monitors" are much more active. Bottom line, my intent is positive.

EagleOne

>EagleOne,
>
[quoted text clipped - 3 lines]
>message simultaneously to both groups) rather than multi-post (i.e. send
>a separate copy of the message to each group). Thanks.

Reply to this Message

Steve Schapel - 25 May 2008 03:54 GMT

Thanks, EagleOne. However, I feel you missed the point. Post to both
if you must - just do it by addressing the same message simultaneously
to both. That way, responses can be seen by participants in both
groups. It is irritating to spend time answering a question, only to
find that the original question was multi-posted, and the help has
already been adequately provided elsewhere. You see?

Signature

Steve Schapel, Microsoft Access MVP

> Thanks for your thoughts. That said, even thought the .macro group is the prime source for my
> questions, there is very little response in that group. So I post both to: 1st get any response,
> and 2nd, to see if I can wake up the .macro group.
>
> In comparison, the Excel "monitors" are much more active. Bottom line, my intent is positive.

Reply to this Message

fredg - 24 May 2008 23:01 GMT

> DoCmd.DeleteObject(acTable,"myTable")

Ditch the parenthesis.
This syntax works for me:

DoCmd.DeleteObject acTable, "myTable"

However, why are you using a Function?
A function returns a value. There is no value being returned here.
Use a Sub Procedure instead.

Public Sub DeleteTables()
On Error GoTo Delete_Tables_Err

DoCmd.DeleteObject acTable, "MyTable"

Delete_Tables_Exit:
Exit Sub

Delete_Tables_Err:
MsgBox Error$
Resume Delete_Tables_Exit

End Sub

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

EagleOne@microsoftdiscussiongroups - 27 May 2008 13:04 GMT

Good question!

While getting my feet wet re: Access VBA etc, I used Access Convert macro
wizard. It scripted the VBA as a function. I have no idea why and I also
had a similar question but not the courage to challenge it!

EagleOne

> > DoCmd.DeleteObject(acTable,"myTable")
>
[quoted text clipped - 20 lines]
>
> End Sub

Reply to this Message

Nicholas Meyer - 27 May 2008 23:20 GMT

hmm idk

> Good question!
>
[quoted text clipped - 28 lines]
>>
>> End Sub

Reply to this Message

Douglas J. Steele - 27 May 2008 13:32 GMT

There are legitimate reasons for casting code as a Function, rather than a
Sub, even if the function doesn't return any value.

You can set the property of an Event to a function, whereas you can't set it
to a sub. In other words, if you used

Public Function DeleteTables()
On Error GoTo Delete_Tables_Err

DoCmd.DeleteObject acTable, "MyTable"

Delete_Tables_Exit:
Exit Function

Delete_Tables_Err:
MsgBox Error$
Resume Delete_Tables_Exit

End Function

then you could set the OnClick property for the button to "=DeleteTables()",
rather than having to set it to "[Event Procedure]" then having to code

Private Sub MyButton_Click()

Call DeleteTables

End Sub

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> However, why are you using a Function?
> A function returns a value. There is no value being returned here.
[quoted text clipped - 13 lines]
>
> End Sub

Reply to this Message