• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

Macro help

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!
0
sagarh
Asked:
sagarh
  • 3
  • 2
1 Solution
 
peter57rCommented:
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.

Pete
0
 
rockiroadsCommented:
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


0
 
rockiroadsCommented:
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
0
 
peter57rCommented:
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.

Pete
0
 
rockiroadsCommented:
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
    Next

a simple check can be made here

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


0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now