Results 1 to 9 of 9
  1. #1
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451

    new user, can i do it?


    hello all, I've been using macros to do a lot of my actions in access and recently have been trying to learn and start using VBA. i have a update query and an append query. how hard is it to do this same action in VBA? can a novice do this or should i stay with the sql for now?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Very simple example of VBA running SQL action:

    DoCmd.Execute "UPDATE table SET fieldname=something"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    vicsaccess, this is one of those things that can be done different ways, and sometimes, that's what gives learning vba thoroughly a long curve. Some will tell you to do or not do things certain ways, while others will say the opposite, so sometimes you need to take advice with a grain or two. For example, you can DoCmd.OpenQuery or DoCmd.RunSql (only for action queries, if I recall correctly). Which is best may depend on circumstances such as whether or not parameters have to be passed if you are constructing a sql statement in code. Then there is the Currentdb.Execute method, which provides capabilities not available in the other methods. However, DoCmd.Execute is one that I'm not familiar with and could not find in the Microsoft Access 2013 Developer Reference for the DoCmd Object.

    Maybe I'm old fashioned, but I'd recommend a book on Access that contains code because I think you'd find it easier to learn in chunks. It's easier to read, re-read, bookmark, peruse the glossary or index, etc. Maybe your local library? Anyway, I found that approach helped.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Sorry, my error, mixing up commands. The correct syntax is:

    CurrentDb.Execute ...
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    as usual, thank you all. I've been learning as i go. the last DB, i created for my co-workers to track production. it primarily used macros and queries, worked well but the other day they ask me to create a new one to handle our maintenance. this one i was thinking of advancing my knowledge and try and use all VBA. yes, Micron, i have my Access 2013 Bible book, the internet and all of you friends that have led me this far. so far i have been able to do basic commands in VBA but would love to be able to tackle the action queries in VBA also. just not sure how advanced of a programmer that requires me to be, is it something that a beginner can do or is it more of an advanced move that i should look at for a future project? either way i'll continue to explore my capabilities and tap the knowledge of this group.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by vicsaccess View Post
    ... but the other day they ask me to create a new one to handle our maintenance...
    I think the above quote answers your question. Eventually, you will need to implement VBA in order to provide a solution. The only way to learn how to code is to get your hands dirty. You will need cheat sheets that provide information regarding data types, code snippets, glossary of terms, etc. For cheat sheets I use everything from Text files to Web Page bookmarks to actual Access files. The trick is to place these files in a folder and provide descriptive names. Sometimes a file name is not descriptive enough and I will place that cheat sheet inside another folder, using the name of the folder to help with the description.

    Be aware of your Macros, especially embedded Macros. You might be frustrated if you execute a Macro and also execute some VBA.

  7. #7
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    thanks ItsMe, any chance of sharing your cheat sheets? just kidding. i have started some sort of a file and am adding to it pretty regularly. keep your eyes open, i'm sure i'll need all your help.

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I do not know if you are aware of them, but I created some tutorials that explain some VBA techniques. I know you have your book and that is good. It may be worthwhile to check out the VBA videos I created, too. I tried to present them in a way that they cover more than what the title says. For instance, the videos regarding the MsgBox do more than explain how to implement the MsgBox. They are also a good introduction to functions.
    https://www.accessforums.net/tutoria...ers-52741.html

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    is it something that a beginner can do or is it more of an advanced move that i should look at for a future project?
    I'd say it depends on your deadline. It will take you longer than if you used macros, but you will not regret advancing your skill, I'm sure. You would also gain the ability to handle errors more efficiently, since macro errors result in termination of a process, which may leave a chunk of your data in an altered state while the rest is unaffected.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 04-08-2014, 10:39 AM
  2. Replies: 13
    Last Post: 11-18-2013, 02:20 PM
  3. Replies: 1
    Last Post: 07-20-2012, 05:35 PM
  4. Replies: 3
    Last Post: 09-22-2011, 03:35 PM
  5. Replies: 8
    Last Post: 06-30-2010, 10:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums