Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2004
    Posts
    37

    Unanswered: Writing Macros In Access

    Hello,
    I am trying to write a query that will execute 3 make table queries.

    I set warnings off
    I identify the 1st make table query via OpenQuery Command
    I identify the 2nd make table query via OpenQuery Command
    I identify the 3rd make table query via OpenQuery Command
    I set warnings back On

    Now, I did this and it deleted my 1st query.

    Any way around this? I have a macro in the DB that I did not create that does the exact same thing. I seemed to have set it up identical. Something is not right.

    Any help will be beneficial!!!

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Create the querys
    create the macro

    then hide the querys

    what the user can't see they can't run / delete
    Attached Thumbnails Attached Thumbnails this.gif  
    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.

  3. #3
    Join Date
    Sep 2004
    Posts
    37
    Thanks for the response!

    OK I have figured out how to hide the queries. But I need to be modified slightly each time I will run the macro. How do I unhide queries in Access?

    TIA
    Last edited by gissa; 12-16-04 at 17:12.

  4. #4
    Join Date
    Sep 2004
    Posts
    37
    any ideas?

  5. #5
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    To view your hidden queries, go to Tools|Options|View and check the Hidden Objects box. That will allow you to see the queries that you set to hidden. However, the advice that myle gave to you is misleading. A user can still delete a query even if it is hidden if they run a Macro that opens a Make Table query that has the Make New Table - Table Name set to the same name as another query. I think that is what has occured here. That is why it is a good idea to run your action queries with the warnings set to ON initially to make sure they aren't doing something unexpected. Then if everything runs as expected, add the SetWarnings Off statement to your Macro.

    To track down your problem, open your Make Table queries in design view and check that the Make New Table - Table Name is not the same as any of your query names.

    TD

  6. #6
    Join Date
    Sep 2004
    Posts
    37
    Thank you for the response!
    I will try this tomorrow!

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Why not Create a form Where you can change the The value the put a buttom on it to exe the macro just get the querys to point to the form

    One Tick I do

    Create a New msdatabase and Use the The Autoexec macro
    run at startup an use the Make table to Pass a A New Table into a other database

    Code:
    SELECT [ALL FREE DAYS].* INTO [TB LTI] IN 'C:\Event\LTI.MDB'
    FROM [ALL FREE DAYS];
    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.

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi gissa,
    Just for info sake....try getting away from Macros. They are ok but VBA is a whole lot better and easier to maintain. One way to learn as I did was once I created and saved a Macro and saw that it did what I wanted it to do, I then opened it in DesignView, then did SaveAs....and then selected Module. It will automatically convert your Macro to a VBA module for you to study. Really isn't all that hard and just a step in the higher direction. Elevate your knowledge and you will be happier. Just some info from a friendly face.

    have a nice one,
    BUD

  9. #9
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Hi Bud,

    I've seen a lot of your posts on the forum and you have provided a lot of useful assistance and have done it in a polite way. That is why I am reluctant to post this reply to your suggestion.

    I don't agree with your adivce to get away from Macros. There is nothing wrong with writing Macros in Access. If there was, they wouldn't give us such a tool. Oh sure, writing VBA code is a "higher" level and I will do that when necessary, but Macros have been serving me well in Access application development for close to 10 years. I know that many people will also say to write your Access queries in SQL rather than using the Query tools in Access, but I think there is a place for those tools also and I will alternate as necessary.

    The tools in Access are there for a reason. I haven't used a wizard for Query, Form, or Report design for years, but I highly recommend those tools for less experienced users as a very good starting point. When they need to do something more, they, just like all of us, will make use of the underlying tools and code. And when they need help on it, they will come to dBforums and post a question.

    TD
    Last edited by buckeye_td; 12-18-04 at 14:06. Reason: mispelling and change "is wrong" to "don't agree"

  10. #10
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi TD and thanks,

    I always try my best to offer solutions / opinions in a polite manner, just a manner of my upbringing. However, if you always notice, I never TELL anyone that they should. It is just a suggestion, as I did give the user a starting point as to how to learn to use VBA. This was something I was taught from some very brainy people and was given very good reasons for their recommendations. Sure, Macros are very good still, and they keep them there for users who learned them in the past and feel comfortable with them. But was told and even got this as an inside MS tip that VBA is more flexible and easier when transporting portions of your database as many people don't fully Document their program to easily know what Macro goes with what actual Form or Report etc.
    Anyway I don't think my suggestion is Right OR Wrong. It is just that, my suggestion or point of view. And will always try to explain WHY as well as take time to make an example for those having a bit of a snag on something. It's like a gentleman told me once when I used to paint signs for businesses.
    "Sure, you can use a stencil, but to get the true feeling and appreciation for your work, lose the stencil and go freehand, and in time you will make it so it looks as if you did use one."
    Funny, in time people didn't know I freehanded it when looking, and I did the work in much less time....(stencil cutting, applying....blah blah....) Oh, btw, I'm an artist too....
    This is a good Forum and this is how it should be. Hmmmm, maybe I'll make it my second home... ahhhhhhh, kinda nice and cozy right.....over.....zzzzzzzzzzzzzzzz

    niterz all....
    have a nice one,
    BUD

    geez...where are my manners...forgot to mention that I notice you do good work here as well TD and Glad to meet ya.....(tipping of the hat)
    Last edited by Bud; 12-18-04 at 05:42. Reason: omitted a bracket

Posting Permissions

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