Delete Object Macro

Discussion in 'Microsoft Access Macros' started by sg, May 28, 2010.

  1. sg

    sg Guest

    I have many tables in my database as a result of a make-table query. The
    tables are named Data, Data1, Data2, etc. If there any way to use the Delete
    Object action in a macro to delete all of the tables that start with "data"
    regardless of the number behind them? I tried using the * (Wildcard) but it
    didn't allow that.

    Thanks in advance.
     
    sg, May 28, 2010
    #1
    1. Advertisements

  2. Hello "sg".

    "sg" wrote:
    > I have many tables in my database as a result of a make-table query.
    > The tables are named Data, Data1, Data2, etc. If there any way to
    > use the Delete Object action in a macro to delete all of the tables
    > that start with "data" regardless of the number behind them?
    > I tried using the * (Wildcard) but it didn't allow that.


    This will not work with a macro, you will have to use VBA. Add a
    function like the following to a module:

    Public Function DeleteTables(Pattern As String) As Boolean

    Dim tbl As Object
    For Each tbl In CurrentDb.TableDefs
    If tbl.Name Like Pattern Then DoCmd.DeleteObject acTable, tbl.Name
    Next

    End Function

    You can call the function from within a macro using the RunCode action,
    using the argument DeleteTables("Data*")

    --
    Regards,
    Wolfgang
     
    Wolfgang Kais, Jun 1, 2010
    #2
    1. Advertisements

  3. sg

    sg Guest

    Thank you! This was so helpful!

    "Wolfgang Kais" wrote:

    > Hello "sg".
    >
    > "sg" wrote:
    > > I have many tables in my database as a result of a make-table query.
    > > The tables are named Data, Data1, Data2, etc. If there any way to
    > > use the Delete Object action in a macro to delete all of the tables
    > > that start with "data" regardless of the number behind them?
    > > I tried using the * (Wildcard) but it didn't allow that.

    >
    > This will not work with a macro, you will have to use VBA. Add a
    > function like the following to a module:
    >
    > Public Function DeleteTables(Pattern As String) As Boolean
    >
    > Dim tbl As Object
    > For Each tbl In CurrentDb.TableDefs
    > If tbl.Name Like Pattern Then DoCmd.DeleteObject acTable, tbl.Name
    > Next
    >
    > End Function
    >
    > You can call the function from within a macro using the RunCode action,
    > using the argument DeleteTables("Data*")
    >
    > --
    > Regards,
    > Wolfgang
    >
    >
    > .
    >
     
    sg, Jun 2, 2010
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. jon
    Replies:
    0
    Views:
    1,686
  2. Guest

    Access 2000 error: "object is not an OLE object"

    Guest, Jun 19, 2006, in forum: Microsoft Access Macros
    Replies:
    1
    Views:
    410
    Steve Schapel
    Jun 19, 2006
  3. Guest

    object referred to in VBA not an OLE object

    Guest, Sep 9, 2006, in forum: Microsoft Access Macros
    Replies:
    0
    Views:
    175
    Guest
    Sep 9, 2006
  4. Guest

    Message - Object does not contain automation object

    Guest, Nov 13, 2006, in forum: Microsoft Access Macros
    Replies:
    0
    Views:
    510
    Guest
    Nov 13, 2006
  5. vandy

    object does not contain automation object form- error

    vandy, May 27, 2008, in forum: Microsoft Access Macros
    Replies:
    5
    Views:
    3,612
    Steve Schapel
    Jun 4, 2008
Loading...

Share This Page