Solved

how to run an existing query instead of SQL string using VBA code ?

Posted on 2007-12-06
7
804 Views
Last Modified: 2013-11-27
Hi, here is a simple question. But i just can't figure out.

I made a query in Access Query designer. I want to run the query in VB code for a button in a form. The SQL string is really long so I want to just run the existing query. I was thinking to use DoCmd or db.Execute .....But it didn't work. Please advice....Thank you very much!  Kate
e.g.
db.Execute ("Q_AppendTesting")      
DoCmd.RunSQL (Q_AppendTesting)
0
Comment
Question by:kate_y
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 150 total points
ID: 20420657
DoCmd.OpenQuery "NameOfQueryGoesHere"
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 150 total points
ID: 20420667
kate_y,

And to suppress the warning message associated with action queries:


Regards,

Patrick
With DoCmd
    .SetWarnings False
    .OpenQuery "QueryName"
    .SetWarnings True
End With

Open in new window

0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
ID: 20420670
CurrentDb.Execute "Q_AppendTesting"

or

Dim db as Database
Set db = CurrentDb()

db.Execute "Q_AppendTesting"

or even better (uses ADO)

Dim cn as adodb.connection
Set cn = CurrentProject.Connection

Dim lRecordsAffected as Long

cn.Execute "Q_AppendTesting", lRecordsAffected

msgbox "You just updated " & lRecordsAffected  & " records.  Woohoo"
0
Technology Partners: 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!

 
LVL 26

Expert Comment

by:jerryb30
ID: 20420843
If this is a Select query, your methods of .execute or .runsql would not work.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 20420850
^ Above just an explanation. matthew had it right in first comment.
0
 
LVL 3

Expert Comment

by:incrediblejohn
ID: 20424783

I agree, Matthew has it right.
0
 

Author Comment

by:kate_y
ID: 20586321
Sorry for the late reply. I traveled outside the country for the past few weeks. Thank you all for the answers. I really appreciate it.

k
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question