ado problems

[garethl]

i am trying to open a strored query as a recordset using the following code so that i can loop through it basing other queries on the contents

Set Cnn = CurrentProject.Connection
Set Rst = New ADODB.Recordset
Set Rst.ActiveConnection = Cnn
Call Rst.Open("QRY_AgentsToSendDupeReport", Cnn, adOpenStatic)

the last line there is highlighted by the debugger with the error

'Invalid SqL statement expected INSERT, SELECT, DELETE....'

i copyed that code straight from a thread on this board

http://www.access-programmers.co.uk/forums/showthread.php?t=55486&highlight=ado+recordset+query+open


anyone know what i'm doing wrong?

 

[boblarson]

The easiest code for this ADO is this:

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

rst.Open "QRY_AgentsToSendDupeReport", CurrentProject.Connection, adOpenDynamic, adLockOptimistic


It is ready for you then.

 

[garethl]

i get the same error as before

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

the query works if you run it manually

 

[garethl]

looking at some examples it looks to me that it wants the text of the sql statement passed as a string not the name of a stored query passed as a string though this tutorial

http://www.w3schools.com/ado/met_rs_open.asp

says you can do either

still the error suggests that it is expecting the string it sees as its first argument to be valid sql i.e. to start with INSERT, DELETE, UPDATE or whatever

could i be using the wrong options with it so it interprets this artguument wrong - this is what i assumed i was dong first time round

 

[Bogzla]

i get the same error as before

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

the query works if you run it manually

maybe try:

rst.Open "QRY_AgentsToSendDupeReport", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdStoredProc

?

 

[garethl]

ok before you posted that i tried pasting the text of the sql into a string and running that and i got an error saying too few parameters or something similar

i also get a "too few parameters: expected 1" error when i run that latest piece of code

both of these things suggest that there is something wrong with the query itself like a missing or mispelt field name BUT i can run the query fine by clicking on its icon

this is exactly the same weird behaviour i've had from another query that i posted about on this thread

http://www.access-programmers.co.uk/forums/showthread.php?t=136317

 

[garethl]

set rs = currentdb.openrecordset("QRY_AgentsToSendDupeReport")

gives the same error so it looks like its my query as however i call it i'm getting the same problem - this is the query sql

SELECT QRY_DuplicatesTopLevel.Agent
FROM QRY_DuplicatesTopLevel
GROUP BY QRY_DuplicatesTopLevel.Agent
HAVING (((Count(QRY_DuplicatesTopLevel.Agent))>0));

 

[Bogzla]

set rs = currentdb.openrecordset("QRY_AgentsToSendDupeReport")

gives the same error so it looks like its my query as however i call it i'm getting the same problem - this is the query sql

SELECT QRY_DuplicatesTopLevel.Agent
FROM QRY_DuplicatesTopLevel
GROUP BY QRY_DuplicatesTopLevel.Agent
HAVING (((Count(QRY_DuplicatesTopLevel.Agent))>0));

I'm a little confused as to why you need the HAVING line - it seems to me that this query will implicitly only return results where there is at least one entry for .Agent from QRY_DuplicatesTopLevel, perhaps this is confusing matters?

In fact I'd be tempted to try something like:
SELECT DISTINCT QRY_DuplicatesTopLevel.Agent
FROM QRY_DuplicatesTopLevel;
(I assume you're trying to get a list of [Agent]s that appear at least once in QRY_DuplicatesTopLevel)

hth,
Bogzla

 

[garethl]

tried that get the same error

the really weird thing is that when you run the query by just clicking on it it works just fine

 

[Bogzla]

tried that get the same error

the really weird thing is that when you run the query by just clicking on it it works just fine

do you have the same problem if passing the query as a text string?

strSQL = "SELECT.......;"
Rst.Open strSQL, cnn, , , adCmdText

if so can you post the code for QRY_DuplicatesTopLevel?

 

[RoyVidar]

This query seems to be based on another query. That's probably where you have a parameter that you haven't resolved. See, when firing a query from the interface, it will resolve parameters from forms, but when you wish to open them through ADO/DAO, you need to resolve the parameters.

 

[garethl]

to answer both of you

bogzla - yes i do get the error when passing the query as a text string, its sql is posted above, its this one

SELECT QRY_DuplicatesTopLevel.Agent
FROM QRY_DuplicatesTopLevel
GROUP BY QRY_DuplicatesTopLevel.Agent
HAVING (((Count(QRY_DuplicatesTopLevel.Agent))>0));

its selecting all agents which are in the result set once and once only

RoyVidar - yes the query is based on another query actually its based on a whole stack of queries and your right in saying its in one of these lower level queries which there is an unresolved parameter

i can identify which one by replacing the above sql with that of the query at the bottom of the stack and then running to test then replacing with the next one until i hit the error

the thing is when i hit the error i have the same issue - query runs through its icon but not through ado

it sounds like you have hit on the right answer here though the query does pull a number of parameters from the form using syntax such as

[Forms]![FRM_ImportSubmission]![cboAgent]

can you elaborate? what do you mean by saying i must resolve the parameters in ado? i must use some other form of syntax? or access simply can't see the data on the form at all?

 

[RoyVidar]

Well, Access can see the parameters, but neither ADO nor DAO can

For ADO, if you're sure you're going to use 2002 and later version, you could do something like this

dim cmd                as adodb.command
dim prm                as adodb.parameter
dim rs                 as adodb.recordset

set cmd = new adodb.command
with cmd
    set .activeconnection = currentproject.connection ' or relevant con
    .properties("Jet OLEDB:Stored Query")=true
    .commandtext = <name of query>
    for each prm in .parameters
        prm.value = eval(prm.name)
    next prm
    set rs = .execute
end with

' this will create a forwardonly/readonly recordset, for others
' remove the above .execute and do the following in stead

set rs = new adodb.recordset
with rs
    .cursortype = <relevant cursortype>
    .locktype = <relevant locktype>
    .cursorlocation = aduseclient
    .open cmd
end with

If it has to work with the 2000 version, too, I'd either go DAO

dim db                 as dao.database
dim qd                 as dao.querydef
dim prm                as dao.parameter

set db = currentdb
set qd = db.querydefs(<name of query>)
with qd
    for each prm in .parameters
        prm.value = eval(prm.name)
    next prm
    set rs = qd.openrecordset()
end with

or with ADO resolve the parameters one by one (see the sample in the help file on the ADO createparameter method). Resolving the parameters one by one is probably what executes fastest with ADO.

Note that the code samples are typed up, not tried and tested. Replace with name of your query (in quotes) and relevant lock/cursor types.

Note also that if you're using wildcards somewere in the queries, ADO uses % (percent) and _ (underscore) not * and ? as DAO and the interface does.

 

[garethl]

ok that all sounds a bit complicated but i'm glad someone undesrstands whats going on here! i haven't really used ado before

it was just really spinning me out that i could run the query through the icon as thats usually how i debug them

still a little unsure where my [Forms]![FRM_ImportSubmission]![cboAgent] goes if anywhere - is that in

eval(prm.name)

as in eval([Forms]![FRM_ImportSubmission]![cboAgent] )

or just literally as you have written it so its cycling through all the arguments that are unknown to it and fetching them from their descriptions in the query

anyway i have actually worked round this problem now by the somewhat ridiculous method of exporting the data to one excel file and then importing that file back in only to run a query splitting it up and exporting again to multiple files - i have it working even if it is less than elegant so i'll fix my next little problem and come back to this and have a play

thanks very much!

 

[RoyVidar]

The method I'm using (both with ADO and DAO) resolves parameters from forms dynamically. The construct

    for each prm in .parameters
        prm.value = eval(prm.name)
    next prm

loops through (or is at least supposed to loop through) each parameter in the query. Then for each of those, resolve them. Dunno how to phrase it better, but think of it as replacing the form reference in the query with the actual value it cointains through the usage of the Eval function.

So when using the method I posted, [Forms]![FRM_ImportSubmission]![cboAgent] doesn't go anywhere, it's resolved dynamically. If you wish to resolve them one by one, check out the sample in the help file I mentioned.

 

[garethl]

yeah i think i understand you

basically thats just what eval does - it looks through a query for unresolved parameters and then resolves them by going to the form and pulling out the data - THE DATA VALUE ITSELF, rather then the directions of how to find it on the form

a bit like the difference between passing an argument to a function by value rather than by reference - eval is like passing by value and [Forms]![FRM_ImportSubmission]![cboAgent] is like passing by reference, it gives directions on where to find the value rather than giving the value itself

now all i need is to be able to understand what you mean when you say that access can see the parameters and ado can't and i'm there! so what is the difference between access and ado?

 

[RoyVidar]

You said so yourself - in the interface, you could run the query by doubleclicking it -> Access can see the parameters, and resolve them.

When you open a recordset based on a parameterized query, it's not Access doing the job, you're querying a Jet database through either ADO or DAO. Neither ADO nor DAO have any understanding of Access objects like forms or reports, so when a query contains parameters from forms, you will, in some way, have to ensure the values of each parameter are supplied to ADO or DAO. That's often called to "resolve parameters".

 

[garethl]

ah yeah of course access isn't the database its the front end to the jet database

ado is another interface to that same jet database

 

[garethl]

hello again

sorry it took so long to get back but sometimes when it works you just have to leave it even if the code is horrible

right your dao method worked just fine thanks - i initially tried the ado one but parameter doesn't have the property .value since i'm only using access 2000

now though i'm running into problems using the recordset once i've opened it i can loop through it pulling out the data i need from the recordset but when i try building an sql string for a make table query and running that with CurrentProject.Connection.Execute strSQL

i get these same types of errors of unresolved paramters

my make table query is based on the same select query which I had to base QRY_AgentsToSendDupeReports from - the one which pulls parameters from the form

i am right then in thinking that you also can't resolve paramters from a form when using this method of running a query? (building the sql as a string and then running it)

this is all in a PUBLIC sub of the form called from another form so i don't know if that could cause the issue.

using your method i can make another recordset of this query fine but searching the forums has shown me that you can't make a table from a dao recordset

 

[RoyVidar]

Hm, yes, Access 2000...

Microsoft used a lot of time and money marketing ADO for Access/Jet. Perhaps if they had used a bit more time on the products it would have been more succesfull? Access 2000 wasn't ready for dynamic resolving of parameters from forms with ADO. There's a bit more info, and a sample working with Access 2000 (terribly slow, though, much slower than DAO), in chapter 6 of the developers handbook http://msdn2.microsoft.com/en-us/library/aa139932(office.10).aspx.

So your questions, which I think is alredy answered is that

Executing on CurrentProject.Connection, is to execute on an ADO connection. You cannot do so with a parameterizied query. You must use a command object. I doubt whether it is at all possible with a query string referring to a stored query, I think you'll have to have a stored query. And for the 2000 version, it's very awkward and slow to resolve them dynamicly, see the above link. You might want to resolve the parameters one by one if you really want to use ADO - and I don't think you will be able to do any of this, unless you have a stored query.

If you wish to execute something directly on the connection, it would better be an SQL string without parameters, or where parameters are resolved through concatenation of literal value into the string, i e "SELECT somefield FROM sometable WHERE notherfield = " & MyNumber

If you create a stored query out of your SQL string, you could probably execute it through DAO - air code

dim db                 as dao.database
dim qd                 as dao.querydef
dim prm                as dao.parameter

set db = currentdb
set qd = db.querydefs(<name of query>)
with qd
    for each prm in .parameters
        prm.value = eval(prm.name)
    next prm
    .execute, dbFailOnError
end with

but I'm always getting a bit suspicious with maketable stuff, I'm not sure I like that in production stuff, and often suspect there's something that could be done with the structure/design