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?

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.

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

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

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

?

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

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));

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

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

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?

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.

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?

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 thisdim 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 withIf it has to work with the 2000 version, too, I'd either go DAOdim 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 withor 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.

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!

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 prmloops 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.

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?

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".

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

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

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 codedim 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 withbut 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

thanks for that - at least now i understand what is going on

parameters resolved through literal string concatenation is the way i have pretty much gone up until now and it is now that i have a fairly complex set of stacked queries that this option becomes not available to me, queries lower down in the stack have to pull information from the form and so i can't just store the data in a variable or take straight from the form and use literal string concatenation

i think from now on i will avoid ado / dao whenever possible, i am also having a look at my design to see how it can be improved

unfortunatly this particular bit where i loop through a recordset building other queries cannot really be avoided as far as i can see, these are the make table queries

possibly they don't need to be make table queries, the end result is to export excel files (and this definitly definitly needs to happen it is one of the main purposes of the db) but there are other ways of making excel files - whatever happens though i'd still need to build some sort of query and its still going to be based on a stack of other queries

where i may well be able to improve the design however is to fundementally change the way in which the data is imported so that (hopefully) i can do all this processing in a single sql statement removing the need for the stacked queries, then my problem is solved.. back to literal string concatenation

i will have a look at the db and post my proposed change see what you think, i know what i intend to do pretty much but just to keep the length of this post down as much as anything else ;)

ok so what i propose is this, quick description of whats happening first

we recieve excel files where each line represents a job done by a contractor and the work gets loaded into one big table of jobs done but first it is loaded to a temporary table

while the data is in the temporary table various string preprocessing operations are performed, then a duplicate check is carried out, a report is produced on the data (which displays counts of different types of jobs, the sum of the monetary value to check against the money they have claimed... stuff like that) and the user is prompted by another form to decide if they want to accept, reject or ammend the data

i realise duplicate checking is not the most complicated operation but it is complicated slightly in our case by the need for the duplicate check to be a check only of duplicates that relate to the current list of jobs being considered for accepting into the database - that is one half of a pair of duplpicates must exist in that list for it to be a valid duplicate

also we are not checking whole lines of data, we build a 'DupeCode' from three of the fields and search for duplication of that code, so i have the following queries (this is the stack i'm talking about)

- DupeCodes for the data already in database (the big table we append to)
- DupeCodes for the new data being considered (the temp table)
- A union all which links these first too together
- A query which searches the list produced by the union all to give those codes which occur more than once (i.e. the duplicated codes)


this next bit is probably really bad design!

- A query which pulls all the fields in for all the records in both tables (including the DupeCode)
- Finally a query which uses a left join from the query of duplicated codes to this last one to just show whole lines of data for duplicated jobs

i realise now that i didn't need the first two the second to last one there would do

anyway the change i propose making is that there is no need to leave the data in the temp table to perform the duplicate check - what i could do is do all my string preprocessing etc. in the temp table, append straight away but mark the records which are still 'pending' with an extra yes/no field in that table making them easy to remove should they be rejected and then i can just have one simple query searching for duplicates in that that table

the only thing is while it would be easy to just use access's find duplicates wizard to create a bog standard duplicate query i'm not sure how i would work implement the constraint that one half of each duplicate must be in the new submission - perhaps just produce the duplicates and then run another query on those results? to avoid stacking queries again i could use a temporary table or just make sure there is nothing being pulled from the form and then stack them anyway