How to delete Table via macro? Example code attached

[EagleOne]

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

 

[Daniel Pineault]

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

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

 

[chezhaol]

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

 

[EagleOne]

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

EagleOne

 

[Daniel Pineault]

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

 

[EagleOne]

Excellent followup. Thanks!!!

 

[Steve Schapel]

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.

 

[EagleOne]

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

 

[fredg]

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

 

[Steve Schapel]

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?

 

[EagleOne@microsoftdiscussiongroups]

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

 

[Douglas J. Steele]

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

 

[Nicholas Meyer]

hmm idk
"EagleOne@microsoftdiscussiongroups"

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