New Info on Stupid Error I'm not Seeing

  • Thread starter Thread starter TKD Karen
  • Start date Start date
T

TKD Karen

I was having trouble with an ADO function that was creating a recordset
based on a query. I now understand that it was really the query that was
the problem. The query is supposed to be rewritten using ADO in VBA
(Access2000 soon to be upgraded to 2003) but it doesn't recognize the
wildcard '*'. I learned to use '%' instead with the word 'ALike' instead of
'Like' but when I tried to run it, I got booted out of A2k. The DAO code
works for this type of query:
SELECT * FROM tblWhatever WHERE CurrentStatus Like 'Admin*'

Any suggestions how I would save this sqlString to an existing query named
qrySomeQuery?
Thanks!
--
Karen Stern
A Little Help Computer Services, LLC
www.alittlehelp.net
(e-mail address removed)
518-885-4549
Everyone Needs A Little Help!
 
Queries are written in SQL, not ADO or VBA.

To change the SQL of a stored query using DAO:

(untested)

dim db as database, qd as querydef
set db = currentdb()
set qd = db.querydefs![My Stored Query]
qd.sql = ...
set qd = nothing
set db = nothing

Doubtless there is something similar in ADO.

HTH,
TC
 
You didn't answer my question. I want to use ADO not DAO. I appreciate the
effort, but please read what I am looking to do. ADO does not recognize
querydef. I want to do away with DAO altogether because in Access 2003, it
must be manually referenced and some of the databases I use require DAO2.5
(openquerydef) which is no longer available so I want to be consistent in my
programming. What I meant when I referred to VBA was that the SQL statement
was written in my code as opposed to an Access Query Design.
Karen

TC said:
Queries are written in SQL, not ADO or VBA.

To change the SQL of a stored query using DAO:

(untested)

dim db as database, qd as querydef
set db = currentdb()
set qd = db.querydefs![My Stored Query]
qd.sql = ...
set qd = nothing
set db = nothing

Doubtless there is something similar in ADO.

HTH,
TC


TKD Karen said:
I was having trouble with an ADO function that was creating a recordset
based on a query. I now understand that it was really the query that was
the problem. The query is supposed to be rewritten using ADO in VBA
(Access2000 soon to be upgraded to 2003) but it doesn't recognize the
wildcard '*'. I learned to use '%' instead with the word 'ALike'
Click to expand...
instead
of
'Like' but when I tried to run it, I got booted out of A2k. The DAO code
works for this type of query:
SELECT * FROM tblWhatever WHERE CurrentStatus Like 'Admin*'

Any suggestions how I would save this sqlString to an existing query named
qrySomeQuery?
Thanks!
--
Karen Stern
A Little Help Computer Services, LLC
www.alittlehelp.net
(e-mail address removed)
518-885-4549
Everyone Needs A Little Help!
Click to expand...
Click to expand...
 
Have I understood your requirement correctly, you want to
create an Access Query Object using ADO not DAO.
If that is correct, then I do not think it can be done.
If I have misunderstood your requirement, could you please
have another go at restating it.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
You didn't answer my question. I want to use ADO not DAO. I appreciate the
effort, but please read what I am looking to do. ADO does not recognize
querydef. I want to do away with DAO altogether because in Access 2003, it
must be manually referenced and some of the databases I use require DAO2.5
(openquerydef) which is no longer available so I want to be consistent in my
programming. What I meant when I referred to VBA was that the SQL statement
was written in my code as opposed to an Access Query Design.
Karen

Queries are written in SQL, not ADO or VBA.

To change the SQL of a stored query using DAO:

(untested)

dim db as database, qd as querydef
set db = currentdb()
set qd = db.querydefs![My Stored Query]
qd.sql = ...
set qd = nothing
set db = nothing

Doubtless there is something similar in ADO.

HTH,
TC


TKD Karen said:
I was having trouble with an ADO function that was creating a recordset
based on a query. I now understand that it was really
Click to expand...
Click to expand...
the query that
was word 'ALike'
instead
of
Click to expand...
A2k. The DAO
code existing query
named

.
Click to expand...
 
I want to do away with DAO altogether because in Access 2003, it
must be manually referenced
Click to expand...

Access 2003 *does* include a default reference set to DAO.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


TKD Karen said:
You didn't answer my question. I want to use ADO not DAO. I appreciate the
effort, but please read what I am looking to do. ADO does not recognize
querydef. I want to do away with DAO altogether because in Access 2003, it
must be manually referenced and some of the databases I use require DAO2.5
(openquerydef) which is no longer available so I want to be consistent in my
programming. What I meant when I referred to VBA was that the SQL statement
was written in my code as opposed to an Access Query Design.
Karen

TC said:
Queries are written in SQL, not ADO or VBA.

To change the SQL of a stored query using DAO:

(untested)

dim db as database, qd as querydef
set db = currentdb()
set qd = db.querydefs![My Stored Query]
qd.sql = ...
set qd = nothing
set db = nothing

Doubtless there is something similar in ADO.

HTH,
TC


TKD Karen said:
I was having trouble with an ADO function that was creating a recordset
based on a query. I now understand that it was really the query that was
the problem. The query is supposed to be rewritten using ADO in VBA
(Access2000 soon to be upgraded to 2003) but it doesn't recognize the
wildcard '*'. I learned to use '%' instead with the word 'ALike'
Click to expand...
instead
of
'Like' but when I tried to run it, I got booted out of A2k. The DAO code
works for this type of query:
SELECT * FROM tblWhatever WHERE CurrentStatus Like 'Admin*'

Any suggestions how I would save this sqlString to an existing query named
qrySomeQuery?
Thanks!
--
Karen Stern
A Little Help Computer Services, LLC
www.alittlehelp.net
(e-mail address removed)
518-885-4549
Everyone Needs A Little Help!
Click to expand...
Click to expand...
Click to expand...
 
I >did< read what you are trying to do. But no-one here is obliged to give
full answers to each question.

Do you really plan to rewrite all your DAO code in ADO, just because of a
trivial reference problem? If so, might it not be necessary for you to learn
the ADO object model, first? Your original question would then "come out in
the wash", no?

TC


TKD Karen said:
You didn't answer my question. I want to use ADO not DAO. I appreciate the
effort, but please read what I am looking to do. ADO does not recognize
querydef. I want to do away with DAO altogether because in Access 2003, it
must be manually referenced and some of the databases I use require DAO2.5
(openquerydef) which is no longer available so I want to be consistent in my
programming. What I meant when I referred to VBA was that the SQL statement
was written in my code as opposed to an Access Query Design.
Karen

TC said:
Queries are written in SQL, not ADO or VBA.

To change the SQL of a stored query using DAO:

(untested)

dim db as database, qd as querydef
set db = currentdb()
set qd = db.querydefs![My Stored Query]
qd.sql = ...
set qd = nothing
set db = nothing

Doubtless there is something similar in ADO.

HTH,
TC


TKD Karen said:
I was having trouble with an ADO function that was creating a recordset
based on a query. I now understand that it was really the query that was
the problem. The query is supposed to be rewritten using ADO in VBA
(Access2000 soon to be upgraded to 2003) but it doesn't recognize the
wildcard '*'. I learned to use '%' instead with the word 'ALike'
Click to expand...
instead
of
'Like' but when I tried to run it, I got booted out of A2k. The DAO code
works for this type of query:
SELECT * FROM tblWhatever WHERE CurrentStatus Like 'Admin*'

Any suggestions how I would save this sqlString to an existing query named
qrySomeQuery?
Thanks!
--
Karen Stern
A Little Help Computer Services, LLC
www.alittlehelp.net
(e-mail address removed)
518-885-4549
Everyone Needs A Little Help!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top