Running a SELECT query from code

[DeanL]

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...

[AccessAl]

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.activeconne ction = currentproject. connection
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT * FROM myTable " & _
"WHERE primaryKey = " & comboBoxPrimary Key
cmd.Execute


This is elementary but might get you in a good direction

[pietlinden]

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]![NumericControlV alue]

[DeanL]

pietlin...@hotm ail.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]![NumericControlV alue]

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.

[Bob Quintal]

"DeanL" <de************ *@yahoo.comwrot e in
news:11******** **************@ m79g2000cwm.goo glegroups.com:

>
pietlin...@hotm ail.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]![NumericControlV alue]

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

[hbinc]

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_na me,Last_name) AS Person"
cur_tables = "(Person_tb l INNER JOIN Member_tbl ON Person_tbl.Pers on_id
= Member_tbl.Pers on_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...

[Anand]

First you will have to create a QueryDef.

'Normal Query
Dim qryDef as QueryDef
Dim txtSQL as string

txtSQL = "Select * FROM MyTable"
DBEngine.BeginT rans
Set qdf = db.CreateQueryD ef("MyQuery", txtSQL)

CurrentDB.Query Defs.Refresh
DBEngine.Commit Trans

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.Creat eQueryDef("MyQu ery")

qrydef.Connect = _
"ODBC;DATABASE= MyDatabase;UID= sa;PWD=;DSN=MyD SN"

txtSQL.SetFocus
qrydef.SQL = txtSQL
qrydef.ReturnsR ecords = True

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