query on query

  • Thread starter Thread starter susan
  • Start date Start date
S

susan

Hello,

Please see code below:

strSQL returns a set of records. I want this set to be ordered random by
using the strSQL2.
Doing so (better said: trying so) I get error 3078 "Jet-engine can't find
query strSQL."

The errorpointer however points to this line:
Set rs2 = dbsHuidig1.OpenRecordset(strSQL2)

Tooltiptext on rs2 is "rs2 = Nothing"

So the rrormessage points to strSQL and the errorpointer mwnrions strSQL2.



I tested strSQL and that works properly.


Can anyone help?
Using Access 2004,

Thanks,


Susan



Private Sub Knop123_Click()

AANT = Me.t1

' ### -----------
strSQL = "SELECT TOP " & AANT & " " & _
" QVarHitsSel.hit, QVarHitsSel.titelschoon, QVarHitsSel.lokkaal, " & _
"QVarHitsSel.lokatie, QVarHitsSel.titel, QVarHitsSel.volg, " & _
"QVarHitsSel.mijnchceck, VarTrackSpecs.toegevoegd " & _
"FROM QVarHitsSel INNER JOIN VarTrackSpecs " & _
"ON QVarHitsSel.titel=VarTrackSpecs.titel " & _
"ORDER BY VarTrackSpecs.toegevoegd DESC;"
' ### -----------

MsgBox strSQL

' ### -----------
strSQL2 = "SELECT strSQL.titelschoon, strSQL.titel, strSQL.lokatie, " & _
"Rnd(Len([titelschoon])) AS expr1 " & _
"FROM strSQL " & _
"ORDER BY Rnd(Len([titelschoon]));"
' ### -----------

MsgBox strSQL2

' ### -----------
Dim dbsHuidig1 As Database
Dim rs1 As Recordset
Dim rs2 As Recordset

Set dbsHuidig1 = CurrentDb()
Set rs1 = dbsHuidig1.OpenRecordset(strSQL)
Set rs2 = dbsHuidig1.OpenRecordset(strSQL2)

' rs2.MoveFirst
Do Until rs2.EOF
MsgBox rs2!lokatie
rs2.MoveNext
Loop
rs2.Close
' ### -----------

End Sub
 
susan said:
Hello,

Please see code below:

strSQL returns a set of records.
Click to expand...

No!! strSQL is a variable in memory that contains a string: a set of
characters. In this case, the string spells out a sql statement that, when
executed, returns a set of records. Let us be clear about what happens when
you execute the statement contained in that variable. The database object's
OpenRecordset method accepts a string argument. That string can contain the
name of a table or saved query, or it can contain a sql statement. In either
case, behind the scenes, a temporary querydef object is created in memory,
and the string passed is assigned to the querydef's SQL property. If it
simply contains the name of a table or saved query, the words "select *
from" are prepended. And then, it calls the querydef's OpenRecordset method
which returns the set of records as long as the sql statement is
syntactically correct. I will continue inline below.
I want this set to be ordered random by using the strSQL2.
Doing so (better said: trying so) I get error 3078 "Jet-engine can't
find query strSQL."
Click to expand...

No surprise there. Again, strSQL is not a query.
The errorpointer however points to this line:
Set rs2 = dbsHuidig1.OpenRecordset(strSQL2)

I tested strSQL and that works properly.
Click to expand...

Good - very proper technique. I assume you mean you used the Query Builder
to test the sql contained in strSQL.
' ### -----------
strSQL2 = "SELECT strSQL.titelschoon, strSQL.titel, strSQL.lokatie, "
& _ "Rnd(Len([titelschoon])) AS expr1 " & _
"FROM strSQL " & _
"ORDER BY Rnd(Len([titelschoon]));"
Click to expand...

A string variable contained in memory cannot be used as the object in the
FROM clause of another query. Only a table or a saved query or a table
expression can be used there. You have two choices:

1. Create a saved query (Call it TopNResults) that uses the sql statement in
strSQL and use the name of that saved query in strSQL2. At runtime, assign
the string in strSQL to TopNResults. Like this (assuming you've manually
created the saved query ahead of time - the saved query can be created in
code but there's no point that I can see):
Dim qdf as QueryDef
set qdf=dbsHuidig1.Querydefs("TopNResults")
qdf.SQL = strSQL

' aside from testing, there is no point to opening a recordset on strSQL

strSQL2 = "SELECT strSQL.titelschoon, strSQL.titel, strSQL.lokatie, " & _
"Rnd(Len([titelschoon])) AS expr1 " & _
"FROM TopNResults " & _
"ORDER BY Rnd(Len([titelschoon]));"
Set rs2 = dbsHuidig1.OpenRecordset(strSQL2)

2. Use the sql statement contained in strSQL as a subquery (table
expression) in strSQL. Like this:

' Again, there is no point in opening a recordset on strSQL
strSQL2 = "SELECT strSQL.titelschoon, strSQL.titel, strSQL.lokatie, " & _
"Rnd(Len([titelschoon])) AS expr1 " & _
"FROM (" & _
strSQL & ") As q " & _
"ORDER BY Rnd(Len([titelschoon]));"
debug.print strSQL2 ' testing - comment this line out when finished
Set rs2 = dbsHuidig1.OpenRecordset(strSQL2)

PS. Unless you are planning to display the result of
Rnd(Len([titelschoon])), there is no point in including it in the query
results. You can change the statement to this:

strSQL2 = "SELECT strSQL.titelschoon, strSQL.titel, strSQL.lokatie, " & _
"FROM (" & _
strSQL & ") As q " & _
"ORDER BY Rnd(Len([titelschoon]));"
 
Back
Top