By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 994 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Running a SELECT query from code

P: n/a
Hi All,

How do you run a SELECT query from code? I know that DoCmd.RunSQL only
works with action or DDF queries but I need to run a query from VBA and
add variables into the query as defined by entries on a form.

Many thank, Dean...

Jul 10 '06 #1
Share this Question
Share on Google+
6 Replies


P: 7
Have you used ADO? You need to set a reference to Microsoft Active Data Object Library. Then you can use code like:

dim cmd as adodb.command

cmd.activeconnection = currentproject.connection
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT * FROM myTable " & _
"WHERE primaryKey = " & comboBoxPrimaryKey
cmd.Execute


This is elementary but might get you in a good direction
Jul 10 '06 #2

P: n/a

DeanL wrote:
Hi All,

How do you run a SELECT query from code? I know that DoCmd.RunSQL only
works with action or DDF queries but I need to run a query from VBA and
add variables into the query as defined by entries on a form.

Many thank, Dean...
SELECT ....
FROM ....
WHERE [Field1] = Forms![MyOpenForm]![ControlName]
AND [Field2] Val(Forms![MyOpenForm]![NumericControlValue]

Jul 10 '06 #3

P: n/a

pietlin...@hotmail.com wrote:
DeanL wrote:
Hi All,

How do you run a SELECT query from code? I know that DoCmd.RunSQL only
works with action or DDF queries but I need to run a query from VBA and
add variables into the query as defined by entries on a form.

Many thank, Dean...

SELECT ....
FROM ....
WHERE [Field1] = Forms![MyOpenForm]![ControlName]
AND [Field2] Val(Forms![MyOpenForm]![NumericControlValue]
Thanks, I already have the SQL but need to find out HOW to execute it
from code when a button is pressed on a form.

Jul 10 '06 #4

P: n/a
"DeanL" <de*************@yahoo.comwrote in
news:11**********************@m79g2000cwm.googlegr oups.com:
>
pietlin...@hotmail.com wrote:
>DeanL wrote:
Hi All,

How do you run a SELECT query from code? I know that
DoCmd.RunSQL only works with action or DDF queries but I
need to run a query from VBA and add variables into the
query as defined by entries on a form.

Many thank, Dean...

SELECT ....
FROM ....
WHERE [Field1] = Forms![MyOpenForm]![ControlName]
AND [Field2] Val(Forms![MyOpenForm]![NumericControlValue]

Thanks, I already have the SQL but need to find out HOW to
execute it from code when a button is pressed on a form.
You attach it to a form (or report) and open that. You can make
the form look like a query grid very easily.

Alternatively, you can write the SQL to a querydef and
docmd.OpenQuery which is not reccomended in multiuser
situations.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Jul 10 '06 #5

P: n/a
Hi Dean,

What you ask is exactly the way I work with all my Access-applications.
In the way I can tune the next form exactly depending on the values of
the former forms.

In the Form_open-procedure you make your SQL-string.
Such a string looks like: cur_sql = "SELECT " & cur_fields & " FROM " &
cur_tables & cur_criteria & cur_order.

e.g.
cur_fields = "*,Concat(First_name,Middle_name,Last_name) AS Person"
cur_tables = "(Person_tbl INNER JOIN Member_tbl ON Person_tbl.Person_id
= Member_tbl.Person_tbl)"
cur_criteria = " WHERE First_name = " & As_text ("Jan")
cur_order = " ORDER BY Last_name"

Concat is an own_made string-function which adds a space and a value if
this value <Null, for each parameter.
As_text is an own_made string-function that returns the quoted
parameter (you don't have to bother about the right number of quotes).

Finally, if you are happy with your cur_sql, assign it to the forms'
Recordsource:

Me.RecordSource = cur_sql.

In fact I build a systematic, in which this cur_sql is generated
automatically, appliclable for any database.

Success, HBinc.
DeanL wrote:
Hi All,

How do you run a SELECT query from code? I know that DoCmd.RunSQL only
works with action or DDF queries but I need to run a query from VBA and
add variables into the query as defined by entries on a form.

Many thank, Dean...
Jul 11 '06 #6

P: n/a
First you will have to create a QueryDef.

'Normal Query
Dim qryDef as QueryDef
Dim txtSQL as string

txtSQL = "Select * FROM MyTable"
DBEngine.BeginTrans
Set qdf = db.CreateQueryDef("MyQuery", txtSQL)

CurrentDB.QueryDefs.Refresh
DBEngine.CommitTrans

DoCmd.OutputTo acOutputQuery, "MyQuery", , , 1
'If you want to create pass-through query

Dim qryDef as QueryDef
Dim txtSQL as string

txtSQL = "select * from MyTable"

Set qrydef = CurrentDb.CreateQueryDef("MyQuery")

qrydef.Connect = _
"ODBC;DATABASE=MyDatabase;UID=sa;PWD=;DSN=MyDS N"

txtSQL.SetFocus
qrydef.SQL = txtSQL
qrydef.ReturnsRecords = True

DoCmd.OutputTo acOutputQuery, "MyQuery", , , 1

Jul 12 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.