Creating a recordset based on another recordset... :-(

  • Thread starter Thread starter romantercero
  • Start date Start date
R

romantercero

hi

Long Time Reader Firs time poster...

I hope some one can help me with this as I have not been able to fin
an an answer anywhere...

Ok, so I want to manipulate data on a table using various SQL queries
So I can create some temporary table and then delete them once I hav
my way with them but I thought it would be faster usin
DAO.recordsets. Generating the first one based on a table is n
proble


Dim rs1 As DAO.Recordse
Dim rs2 As DAO.Recordse

Set rs1 = CurrentDb.OpenRecordset("SELECT palletid
clave,lote,sum(pal) as pallets FROM Disponibles "

"GROUP BY palletid, clave,lote,facturacion " &
"HAVING (((Facturacion) = " & intClient
& ") AN
((Sum(disponibles.pal))>0));"


Now, generating the seccond recordset based on the first one will no
work. I get Run-Time error 3078 The MSJet DBE cannot find the inpu
table or query 'rs2'. Here's the code for that

[code:1:0a480bf3f2
Set rs2 = CurrentDb.OpenRecordset("SELECT palletid
avg(pallets) AS PalletCount FROM rs2 " &
"GROUP BY palletid;"
[/code:1:0a480bf3f2

Als
[code:1:0a480bf3f2
CantidadPallets = DSum("PalletCount"
"[rs2]"
[/code:1:0a480bf3f2
Will give me the same error. I gather that recordsets cannot be use
interchangebly with tables or queries :-

Any thoughs will be greatly apreciated
 
romantercero said:
hi!

Long Time Reader Firs time poster....

I hope some one can help me with this as I have not been able to find
an an answer anywhere....

Ok, so I want to manipulate data on a table using various SQL queries.
So I can create some temporary table and then delete them once I have
my way with them but I thought it would be faster using
DAO.recordsets. Generating the first one based on a table is no
problem



Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset


Set rs1 = CurrentDb.OpenRecordset("SELECT palletid,
clave,lote,sum(pal) as pallets FROM Disponibles " &
_
"GROUP BY palletid, clave,lote,facturacion " & _
"HAVING (((Facturacion) = " & intCliente
& ") AND
((Sum(disponibles.pal))>0));")



Now, generating the seccond recordset based on the first one will not
work. I get Run-Time error 3078 The MSJet DBE cannot find the input
table or query 'rs2'. Here's the code for that:

[code:1:0a480bf3f2]
Set rs2 = CurrentDb.OpenRecordset("SELECT palletid,
avg(pallets) AS PalletCount FROM rs2 " & _
"GROUP BY palletid;")
[/code:1:0a480bf3f2]
Click to expand...

That would have had to be "FROM rs1" anyway, even if it would work. But
it won't -- see below.
Also
[code:1:0a480bf3f2]
CantidadPallets = DSum("PalletCount",
"[rs2]")
[/code:1:0a480bf3f2]
Will give me the same error. I gather that recordsets cannot be used
interchangebly with tables or queries :-(

Any thoughs will be greatly apreciated!
Click to expand...

No, I'm afraid that won't work. Recordsets exist in memory, but they
are not objects known to the database, so you can't use SQL or a domain
aggregate function to select from one.

If it helps, you can get the same effect by combining the SQL
statements, treating the first as a virtual table. Like this:

Set rs2 = CurrentDb.OpenRecordset( _
"SELECT T.palletid, avg(T.pallets) AS PalletCount " & _
"FROM (" & _
"SELECT palletid, clave, lote, sum(pal) as pallets " & _
"FROM Disponibles " & _
"GROUP BY palletid, clave, lote, facturacion " & _
"HAVING ((Facturacion = " & intCliente & ") AND " & _
"(Sum(disponibles.pal)>0)) " & _
") As T " & _
"GROUP BY T.palletid"

or, building on that, this:

Set rs3 = CurrentDb.OpenRecordset( _
"SELECT Sum(S.PalletCount) As CantidadPallets FROM ("
"SELECT T.palletid, avg(T.pallets) AS PalletCount " & _
"FROM (" & _
"SELECT palletid, clave, lote, sum(pal) as pallets " & _
"FROM Disponibles " & _
"GROUP BY palletid, clave, lote, facturacion " & _
"HAVING ((Facturacion = " & intCliente & ") AND " & _
"(Sum(disponibles.pal)>0)) " & _
") As T " & _
"GROUP BY T.palletid" & _
") As S"

CantidadPallets = rs3!CantidadPallets
rs3.Close

Be aware, though, that these queries are all independent of one another.
You don't need to open recordset rs1 before opening rs2, nor rs2 before
opening rs3. But neither do you get any benefit from doing so -- except
maybe you may get the benefit of some data caching on the part of the
database engine; I don't know about that.
 
Back
Top