run SQL SELECT query in vba

[Guest]

Hi y'all,

I have a "SearchForm" where the user
selects criteria to query my tables by.
But the combination of criterias
the user may pickscould be too complex for
my pre-saved query (built using the Query builder).
So I was thinking about writing vba code
within the searchform that would create the
SELECT statement based on the users criteria
then run it and display the results.

But, would it be correct for me to assume that one
can not construct & run simple SELECT SQL statements
from within vba UNLESS i go into Toos>References> and
checkmark the "Microsoft DAO 3.6 Object Library"?

Cheers,
WebDude Out.
(ms access 2000)

 

[Baz]

Hi y'all,

I have a "SearchForm" where the user
selects criteria to query my tables by.
But the combination of criterias
the user may pickscould be too complex for
my pre-saved query (built using the Query builder).
So I was thinking about writing vba code
within the searchform that would create the
SELECT statement based on the users criteria
then run it and display the results.

But, would it be correct for me to assume that one
can not construct & run simple SELECT SQL statements
from within vba UNLESS i go into Toos>References> and
checkmark the "Microsoft DAO 3.6 Object Library"?

Cheers,
WebDude Out.
(ms access 2000)

No. I normally do this sort of thing by constructing the query string, and
then assigning it to the RecordSource property of a form which is to show
the results. You don't need DAO to do that.

However, the first thing I always do when starting a new Access mdb
application is to go to References, uncheck ADO, and check DAO. You really
can't get far in a moderately sophisticated application without it.

 

[Guest]

Hi.

But, would it be correct for me to assume that one
can not construct & run simple SELECT SQL statements
from within vba UNLESS i go into Toos>References> and
checkmark the "Microsoft DAO 3.6 Object Library"?

No. One may use the ADOX library to manipulate existing queries. Set a
Reference to the Microsoft ADO Ext. 2.x for DDL and Security library, then
try:

Public Sub setView()

On Error GoTo ErrHandler

Dim cat As New ADOX.Catalog
Dim vw As ADOX.View

Set cat.ActiveConnection = CurrentProject.Connection
Set vw = cat.Views("qrySearch")
vw.Command = "SELECT * FROM tblLocations WHERE State = 'Texas';"
DoCmd.OpenQuery vw.Name

CleanUp:

Set vw = Nothing
Set cat = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in setView( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub

.. . . where qrySearch is the name of an existing query and the Command is
any valid SQL for your database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

You can try this

Create a query, and use the code to change the sql within this query, and
then run it.

Application.CurrentDb.QueryDefs("Queryname").SQL = "Select * From TableName
Where FieldName = " & Param
Docmd.OpenQuery "Queryname"

 

[Guest]

You can try this
Create a query, and use the code to change the sql within this query, and
then run it.
Application.CurrentDb.QueryDefs("Queryname").SQL = "Select * From TableName
Where FieldName = " & Param
Docmd.OpenQuery "Queryname"

I created a little sample form with your code, and
SUCCESS! That was brilliant, Ofer!

Now i dont have to worry about setting the proper
vba references on other peoples PC if they want to
run my database.. i just use the built in
Microsoft ActiveX Data Objects 2.x library.

:-D

Cheers!
WebDude aaawwwaaaaaaaaaaaaaay

ps: Im gonna add you to my xmass list

 

[Guest]

Hi Camaro!

One may use the ADOX library to manipulate existing queries. Set a
Reference to the Microsoft ADO Ext. 2.x for DDL and Security library.....

That reference seems to have come preloaded with my MS Access 2000.
So, i assume other people who wish to run my database on their pc wont have
to manually load the ADO reference either. Which is a good thing : -)
Im going to go with it

.....then try:

Public Sub setView()

On Error GoTo ErrHandler

Dim cat As New ADOX.Catalog
Dim vw As ADOX.View

Set cat.ActiveConnection = CurrentProject.Connection
Set vw = cat.Views("qrySearch")
vw.Command = "SELECT * FROM tblLocations WHERE State = 'Texas';"
DoCmd.OpenQuery vw.Name

CleanUp:

Set vw = Nothing
Set cat = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in setView( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub

. . . where qrySearch is the name of an existing query and the Command is
any valid SQL for your database.

Im gonna use a variation of the code which is at the heart of your example.
Which will look something like this;

Application.CurrentDb.QueryDefs("Queryname").SQL = "Select * From TableName
Where FieldName = " & Param
Docmd.OpenQuery "Queryname"

In the years ahead, after ive forgotten about access, my boss may want me to
go back and edit the database code, which is why i opt for simple code.

Me vba skills are seriously lacking.


Thank you!!
Cheers
Webdude


im gonna add ye to me xmass list too

 

[Guest]

No. I normally do this sort of thing by constructing the query string, and
then assigning it to the RecordSource property of a form which is to show
the results. You don't need DAO to do that.

Yes, ive done that but.. i want a way to check the validity of the sql query
BEFORE opening up a form and using that statement as the forms recordsource.

However, the first thing I always do when starting a new Access mdb
application is to go to References, uncheck ADO, and check DAO. You really
can't get far in a moderately sophisticated application without it.

Yes but, id like to give my database to a few people who dont program at
all.. so they wont know how to check the DAO reference in vba.



In the end, im gonna go with this handy bit of code:


Application.CurrentDb.QueryDefs("Queryname").SQL = "Select * From TableName
Where FieldName = " & Param
Docmd.OpenQuery "Queryname"


Which will also let me check to see if the sql string returns any results,

If DCount("*", "QueryName") >1 then
' display results
else
' do nothing
end if

Cheers,
WebDude

Ill send you something special, just for replying to me post

 

[Guest]

Thank you for that response, Its one of the best one I got.

 

[Guest]

Thank you for that response, Its one of the best one I got.

You deserve it dude! I mean, the insight you provided (with
the code you gave) will enable me to reduce the overall size
of my database and allow me to offer the user custom queries
built to their own specifications....And thats exactly what i was
looking for!

WebDude
"Ofer for MVP!"