Re: Running SQL Statements from VBA
Ok, What you want do to is use the .sql method from VBA,
How I did this was to create a baseQuery that I knew I would
be using for the SearchResults Form, and I manipulate the Where Clause in vb.
It does not really matter, once you have the QDef established its really providing a object for Access to keep tract of and use, you can really manipulate the SQL under the hood.
I did this in a DAO enviroment. You would need to reference the DAO 3.6 Library. I turned off ADO for this project, if you use it then referance DAO and ADO specifically in your objects. Ie DAO.Recordset, so VB knows what to do. -- Lost Much hair on that one. --
This code was cut from my Seach Module, but its incomplete if you just cut and paste it wont work. It does provide a working example of the critical piece you need however.
' REM makes the Querys SQL statement your string.
' Note although Im only interested in changing the Where Clause I have
' to pass the complete SQL string I want for the Query now.
Let dbs.QueryDefs!Search.SQL = StrBldSQL
'You will need to open a connection to your db and close it,
'I just happened to close mine here.
' REm Opens the form I linked to the Query, you can just open the
query as well.
stDocName = "frm_SearchResults"
DoCmd.OpenForm stDocName, , , stLinkCriteria
I hope this helps. BB
Originally posted by secutanudu
I am new to Access programming. I have a sql statement in a string variable (a select statement) and I want to run it from VBA, and have the results pop up in an editable grid window, just as ifI were running a normal access query. How do I do that?
I am using access 2000.
Last edited by blackbird_61; 05-17-03 at 13:41.
K did you Flashy thing me, K??