Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2009
    Posts
    47

    Unanswered: MACROs in ACCESS

    I have to run a number of queries in ACCESS and make sure the process is atuomated for future use. when new data is uploaded in the Data base I want these Queries to execute one by one and update some tables. S o how can I do that will macros help...how do you write Macros in ACCESS is it similar to Excel.

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    You can run queries you have created in Macros and add the number and sequence as desired. Click new macro and under Action select OpenQuery under query name click the drop down and all you queries will be listed. Select the one you want. Then repeat the process to add as many queries as you like.

    You will need to assign the macro to a button (or some other object or code) to start the macro.

    The code for this would be
    DoCmd.RunMacro "MacroName"

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    If you are append or using the Maketable

    you can turn the warning off but rememder to turn it back on again.

    have attach a screen shot that should point you down the right track
    Attached Thumbnails Attached Thumbnails Macros.bmp  
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Feb 2009
    Posts
    47

    New Dimensions forthe Macro

    I havent tried what you said but it seems like it will work. what I forgot was that every month the user will a import new data set. So I need the macro to Import the new data which is a txt file or Excel spraedsheet located on a Network Drive and put it in place of the main table with the same name, since all the queries are looking at that Table to do their calculations.
    For Example
    Right now the Table name is "CALLDATA" so I want the macro to delete that Table and save the new import data as "CALLDATA" before running all the queires.

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    you can create a query that can delete all records ie

    DELETE Hour.Hourid FROM [Hour] WHERE (((Hour.Hourid)>1));


    there a macro function call "TransferText" you can import that

    or an other way to do it which I have done heaps of times

    is link the Calldata text file then run a mk table which would convert the link file to a msaccess table them run the rest of the querys

    don't try to run query over text link file it can be done but the computer works over time giving you the answer but it has to convert each record to MS format

    The good thing about doing it this way you are always looking at the Right DATA (or Wrong data)
    Last edited by myle; 03-18-09 at 20:47.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by UNCC-EE
    how do you write Macros in ACCESS is it similar to Excel.
    Quote Originally Posted by UNCC-EE
    I havent tried
    What I really don't understand is this attitude of "not trying first". Why bring an issue like this to a forum before trying??

    Try it first, then you have a more complete understanding and can pose your questions much more clearly.

    If you had, you probably would have found your answers for yourself.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Feb 2009
    Posts
    47
    well if you read the rest of the post you would see that i understood what the guy had said, but wanted to import new data before implementing the Macro. So If you can't help DONT reply at all..kangroo jack

  8. #8
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Quote Originally Posted by UNCC-EE
    well if you read the rest of the post you would see that i understood what the guy had said, but wanted to import new data before implementing the Macro. So If you can't help DONT reply at all..kangroo jack
    hay just go for it
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  9. #9
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by UNCC-EE
    well if you read the rest of the post you would see that i understood what the guy had said, but wanted to import new data before implementing the Macro. So If you can't help DONT reply at all..kangroo jack
    Don't forget StarTrekker; Give a man a fish, and he'll eat for a day. Teach a man to fish, and he'll eat for a lifetime. Point out that he forgot his fishing pole, and he'll bite your head off.
    Me.Geek = True

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    lol Nick ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Feb 2009
    Posts
    47
    More than one a #@ h#@%s present i guess.

  12. #12
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by UNCC-EE
    More than one a #@ h#@%s present i guess.
    You will usually get much better results on forums such as this if you post a specific problem, which will usually surface after you have searched around and tried a few things. It can be very hard to write a description covering each step to make something.

    As to your question Macros in Access are completely different to Excel or Word. In Excel or Word macros are really containers for VBA and the counterpart in Access would be Modules. Macros in Access are like pre packaged VBA procedures.

    To make the Access macro go to that section and click New. A screen will open showing two columns. The first is for macro actions and the other is for description. That is optional and is only for documentation. If you go to View on the toolbar you will see Conditions. Click it and another column will appear on the left. Entries in this column determine if the macro action is to be done.

    The action line has a drop down list which will appear when you click in it. The one you are after is OpenQuery. When selected the bottom of the macro design page will allow you to select the query.

    On the action line before you do the queries select SetWarnings and No. At the end of the macro do SetWarnings again but this time for Yes.

  13. #13
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by UNCC-EE
    More than one a #@ h#@%s present i guess.
    First off, I wasn't trying to be mean, I was trying to lighten the situation with humor. You probably won't get very far in life if you don't learn to laugh at yourself once and a while (or at least you won't be very happy doing it).

    Secondly, if you don't like what StarTrekker wrote, that's fine, you're welcome to your opinion. But I would advise you not to retaliate when someone offers advice here, as this is a voluntary forum and no one is required to help you out (not to mention you usually get more and better help by being nice). StarTrekker (and he can correct me if I'm wrong) was just pointing out that trying stuff first will often allow you to solve your own problems faster and without depending on others, with the added benefit that it usually gives you a good learning experience and makes you a better programmer too.

    Thirdly, please watch your language and try to maintain at least a hint of professionalism, please.
    Last edited by nckdryr; 03-25-09 at 02:30.
    Me.Geek = True

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Well I obviously can't help; I am just a kangaroo named Jack.

    Create lots of macros. Macros are good.

    [/sarcasm].
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  15. #15
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by nckdryr
    StarTrekker (and he can correct me if I'm wrong) was just pointing out that trying stuff first will often allow you to solve your own problems faster and without depending on others, with the added benefit that it usually gives you a good learning experience and makes you a better programmer too.
    I liken it to not having the insect spray to kill the spider under the house, so you have to crawl around to find the spider and then hit it with a hammer But while you are crawling around looking for the spider you discover two water leaks in the pipes etc. and also discover that the wiring for the TV is under the bathroom.

    When you ask the plumber about the water leaks and he says.....where are they are etc.....you have the answer. So the plumber only has to give advice on how to fix the leak.

    I don't know about others but for me there is no way to do the data base stuff unless you see the sun rise (many times) while being on the keyboard or mouse.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •