How to execute a query in ms-access in VBA code?

Question

How can I execute a query to return records in an ms-access database using VBA code?

yes it will return paths to pictures stored locally in db and id of each — Oghenero E.
– Oghenero E., Commented Dec 31, 2010 at 3:46

user794459 · Accepted Answer · 2011-06-12 05:10:31Z

18

How about something like this...

Dim rs As RecordSet
Set rs = Currentdb.OpenRecordSet("SELECT PictureLocation, ID FROM MyAccessTable;")

Do While Not rs.EOF
   Debug.Print rs("PictureLocation") & " - " & rs("ID")
   rs.MoveNext
Loop

answered Jun 12, 2011 at 5:10

user794459

Sign up to request clarification or add additional context in comments.

Comments

zsalzbank · Accepted Answer · 2010-12-31 01:13:55Z

14

Take a look at this tutorial for how to use SQL inside VBA:

http://www.ehow.com/how_7148832_access-vba-query-results.html

For a query that won't return results, use (reference here):

DoCmd.RunSQL

For one that will, use (reference here):

Dim dBase As Database
dBase.OpenRecordset

answered Dec 31, 2010 at 1:13

zsalzbank

9,8611 gold badge28 silver badges39 bronze badges

4 Comments

Oghenero E. Over a year ago

@Remou please more info on DoCmd.RunQuery, i don't want to use SQL, I have the Query already done in ms-access

Fionnuala Over a year ago

oops Openquery : msdn.microsoft.com/en-us/library/bb238028(v=office.12).aspx . With Open query you will get warning messages, but they can be turned off and on again if they become a problem.

Oghenero E. Over a year ago

@Remou thanks, i have a table in ms-access that contains paths to pictures locally and want to print that, pls help me chek this link stackoverflow.com/questions/4567204/…

David-W-Fenton Over a year ago

It's simply never a good idea to turn off SetWarnings. For one, you will then not know if something went wrong. That is, DoCmd.SetWarnings False doesn't just turn off the confirmation prompt, but also turns off the reporting of any errors. And in the case of DoCmd.RunSQL, you can't know that your entire update succeeded, because the SQL will be executed and as many UPDATEs/INSERTs as possible completed. I do not know if DoCmd.OpenQuery has the same deficiency, but it likely does. For a replacement that implements @Remou's first suggestion, search SO for my SQLRun() function.

Collectives™ on Stack Overflow

How to execute a query in ms-access in VBA code?

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related