Macro help

Posted on 2006-05-30
Last Modified: 2012-08-14
I dont know access that well and would like to write a macro which basically tests to see if table exists and drops it and then runs a query which recreates the table and populates it with new data - I need to run this twice within the same macro - ie. drop table 1 if it exists and table 2 and then run two queries which populates two new tables... I can do this in MS SQL via stored procedures but dont know how in Access and have a time deadline to meet.

Thanks for any help!
Question by:sagarh
    LVL 77

    Expert Comment

    Hi sagarh,

    To do this in a macro use the DeleteObject macro action to delet the table, and the OpenQuery macro action to run your maketable query.
    You can use the Setwarnings action if you want to get rid of the prompts when running the macro.

    LVL 65

    Expert Comment

    first create a procedure like this

    'this procedure checks if table exists then deletes it if it does
    'it then runs the specified query - which recreates that table
    public sub RecreateTables (byval sTable as String, byval sQuery as String)

        if IsObject(sTable) = True then         'if table1 exists as an object
            DoCmd.DeleteObject acTable, sTable
        end if

        'Now run your query
        DoCmd.RunSQL sQuery
    end sub

    now u can call it like this

    public function DoYourStuff
        RecreateTables "table1", "query1"
        RecreateTables "table2", "query2"
    end function

    LVL 65

    Expert Comment

    Note, Peter's explanation is good, especially for novices
    I know u can do RunCode and then IsObject but I wouldnt know the if conditions. I dont use macros as I go straight into VBA - Thats why my explanation was in VBA. (Macro is a like a GUI to VBA).

    Pete would u know how to make use of IsObject? If so, that might be better for sagarh

    Now the func DoYourStuff is defined as a public function even though it does not return a value. One of the reasons is so u can use it in a Macro if u want. U select RunCode, then database name, then your module name (the name where u pasted the code) then select DoYourStuff

    Ideally though, it should be RecreateTables that is defined as a function, then u simply specify this in the Macros/RunCode
    LVL 77

    Accepted Solution

    Sorry missed the Exists bit.

    Set a condition for the DeleteObject action which says:

    DCount("[Name]","msysobjects","[Type]=1 AND [Name] = 'tablenamehere'")>0

    Rocki - are you sure IsObject works like that? I thought it was only applicable to object variables, not objects themselves.
    You could use the same test that I have shown here instead.

    LVL 65

    Expert Comment

    Yes, your right Pete, I dont know what I was thinking there. Hang up from another question
    Silly Me, many apologies sagarh

    IsObject is used to test if a variable is of type object, same as IsDate, IsNumeric etc

    this is the way I last used to document my tables

        For i = 0 To CurrentDb.TableDefs.Count - 1
            Debug.Print "Table: " & CurrentDb.TableDefs(i).name

    a simple check can be made here

    Sagarh, I'd go for Pete's macro approach, easier for you


    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now