rs.open for query by name, not SQL.
Phil Smith
...
...
...
rs.Open QueryToRun, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
will open a record set, assuming that the variable QueryToRun contains
an SQL statement.
What if QueryToRun contains "Buffalo Jump", which is the name of a
stored query?
I want to be able to pass my function the name of a stored query, and
have it do it's thing.
Thanx
Phil
Jeanette Cunningham
needs it.
Look at help on query defs.
Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
"Phil Smith" <ph...@nhs-inc.com> wrote in message
news:ucOlSuja...@TK2MSFTNGP05.phx.gbl...
Dirk Goldgar
> ...
> ...
> ...
> rs.Open QueryToRun, CurrentProject.Connection, adOpenDynamic,
> adLockOptimistic
>
> will open a record set, assuming that the variable QueryToRun contains an
> SQL statement.
>
> What if QueryToRun contains "Buffalo Jump", which is the name of a stored
> query?
That should work. Have you tried it?
--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html
(please reply to the newsgroup)
Douglas J. Steele
rs.Open "[" & QueryToRun & "]", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Phil Smith" <ph...@nhs-inc.com> wrote in message
news:ucOlSuja...@TK2MSFTNGP05.phx.gbl...
Sylvain Lafontaine
rs.Open "EXEC [" & QueryToRun & "]", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic
And adding the owner of the stored procedure (SP) could also help; for
example, if this is dbo :
rs.Open "EXEC dbo.[" & QueryToRun & "]", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic
Finally, in the case of of multi-statement SP, the command SET NOCOUNT ON
should be set at its very beginning; before any other statement is executed.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message
news:%23nfktNq...@TK2MSFTNGP04.phx.gbl...
David W. Fenton
> I want to be able to pass my function the name of a stored query,
> and have it do it's thing.
Why are you using ADO?
Second, you can't have a multipurpose function that will run both
SELECT and DML statements (i.e., what Access calls "action
queries").
In any event, Access already provides two commands for this,
DoCmd.OpenQuery for SELECT statements and DoCmd.RunSQL for DML. Both
accept SQL or the name of a saved QueryDef.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Dirk Goldgar
news:Xns9CCAB3E854633f9...@74.209.136.93...
>
> In any event, Access already provides two commands for this,
> DoCmd.OpenQuery for SELECT statements and DoCmd.RunSQL for DML. Both
> accept SQL or the name of a saved QueryDef.
I don't believe DoCmd.OpenQuery will accept a SQL statement. So far as I
know, it requires the name of a stored query. I there some trick to it that
I haven't heard of?
Phil Smith
Douglas J. Steele
What's the SQL of your query?
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Phil Smith" <ph...@nhs-inc.com> wrote in message
news:OHsKlaGb...@TK2MSFTNGP06.phx.gbl...
Dirk Goldgar
> Yep. I get "Syntax Error (missing Operator) in query expression ...
If you've verified that the query itself is okay and can be opened normally
from the user interface, see if enclosing the query name in square brackets
makes a difference. That is, instead of calling your function like this:
Call DoitToit("Buffalo Jump")
... call it like this:
Call DoitToit("[Buffalo Jump]")
That might be required if your query name contains spaces, other
punctuation, or reserved words..
Phil Smith
"DoCmd.OpenQuery"
Executes a query. It does not return the records of that query in a
set. My goal is to get each record from the passed query, which returns
the text of an SL statement, and then individually process that SQl
coomand. Some of them need to be tweaked first.
DoCmd.RunSQL however, would be of use to me in this project once I get
the above figured out, which I think I have thankx to have.
Phil Smith
David W. Fenton
news:FDBD2289-C0C2-4733...@microsoft.com:
> "David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
> news:Xns9CCAB3E854633f9...@74.209.136.93...
>>
>> In any event, Access already provides two commands for this,
>> DoCmd.OpenQuery for SELECT statements and DoCmd.RunSQL for DML.
>> Both accept SQL or the name of a saved QueryDef.
>
> I don't believe DoCmd.OpenQuery will accept a SQL statement. So
> far as I know, it requires the name of a stored query. I there
> some trick to it that I haven't heard of?
No, you're right -- OpenQuery requires a saved QueryDef name.
David W. Fenton
> Why not use ADO? Any major reason not to?
Because it's completely obsolete? Because it's slower than DAO?
Because it's a non-native database interface in between Access and
Jet, instead of the native one, DAO?
Last of all, because MS has deprecated ADO for use in Access apps in
favor of DAO.
> "DoCmd.OpenQuery"
> Executes a query. It does not return the records of that query in
> a set.
It wasn't clear from your post that you wanted to open a recordset,
as you didn't say what you're doing with it.
> My goal is to get each record from the passed query, which returns
> the text of an SL statement, and then individually process that
> SQl coomand. Some of them need to be tweaked first.
I hardly ever open a recordset and walk through it making changes --
that's more likely properly done with a SQL UPDATE statement.
> DoCmd.RunSQL however, would be of use to me in this project once I
> get the above figured out, which I think I have thankx to have.
DoCmd.RunSQL is flawed in many ways. You might want to Google my
name and "SQLRun", which is my replacement for DoCmd.RunSQL that is
much safer to use.