use Recordset inside SQL-statement

[a.jasper@gmx.ch]

dear guys!

is it possible to use recordsets inside an sql-statement how described in folllow example. the error message: access can´t find the table or querydef.

public function useRS (RS_ext_1 as DAO.Recordset, RS_ext_2 as DAO.Recordset) as DAO.Recordset
 
  dim sql_RS_int as string
  dim RS_int as DAO.Recordset  
 
  sql_RS_int = "SELECT * FROM RS_ext_1, RS_ext_2 WHERE col1_ext1 = 1 and col1_ext2 = 5"
 
  set RS_int = CurrentDB().OpenRecordset(sql_RS_int)
   
  set useRS = RS_int.Clone
 
end function

many thanks!

 

[pr2-eugin]

Hello a.jaspher@gmx.ch, Welcome to AWF

If you could explain what is that you are trying to do in simple English, maybe we could give you a better solution, your code very little sense.

 

[CJ_London]

no - but you can call a function which interrogates a recordset and returns a value - but that value cannot be a recordset.


But why would you want to - you can just incorporate it into your query

SELECT *
FROM mytable, (SELECT * FROM RS_ext_1, RS_ext_2 WHERE col1_ext1 = 1 and col1_ext2 = 5) AS UseRS

 

[a.jasper@gmx.ch]

Thanks for fast response!

useRS is a complex function which compares any col of recordset_a to any col of recordset_b. the return value is a recordset with col_rs_a and a col to have highest similarity in col_rs_b. i use this function very often for comparing filtered tables of different databases.

many thanks!

 

[vbaInet]

You probably could have explained it better like pr2 exclaimed. Just explain without the use of cryptic techie words like col_rs_a because none of that is referenced in your code.

If you want to combine both recordsets and return a recordset you'll need to loop through one (ie the one with the least records) and build a WHERE clause or Filter and apply that to the other recordset's Filter property , then take a clone of that if you wish.

But then again your requirement isn't clear.

 

[The_Doc_Man]

A recordset is based on a table or query that provides a stream of records. A SELECT query can directly open recordsets without using the .OpenRecordset syntax. If it CAN be opened as a table-type recordset, there is NO PRACTICAL DIFFERENCE between opening a table and opening a SELECT-style query to produce a recordset. I guess I am saying that you are attempting to add complexity to a step that is already more easily handled by Access anyway.

Note that action recordsets, like action queries, can ONLY take action; they don't return anything that looks like a SELECT recordset. So opening an action query at that point is going to return no data (with one exception - the DAODatabase.Execute option can return a ".RecordsAffected" value as a property of the DAODatabase object after the query has been executed). But if you CAN open your recordset and get something from it, then you can use sub-queries rather than some bizarre function that tries to get behind the scenes when it doesn't have to. Therefore, I would advise that you are adding layers that you don't need to add.

In fact, you claim you have used this before. I don't see how you could, since the SELECT's FROM clauses aren't queries. Be that as it may, I see that your code creates a clone of a query that does what appears syntactically to be a Cartesian JOIN on two recordsets. I would suggest that this would be easier to write - and easier to debug if you did this.

Your recordsets MUST have recordsource strings of the general form "SELECT something FROM sourceRS1" (or sourceRS2 as appropriate). That is, if they return any data at all, then the previous statement MUST be true. You can generate the same answer by doing this code snippet:

StrRS1 = {source query string defining sourceRS1}
StrRS2 = {source query string defining sourceRS2}
MainRS1 = "SELECT * FROM (" & strRS1& "), (" & strRS2 &") WHERE col1_ext1 = 1 and col1_ext2 = 5"
set RS_int = CurrentDB().OpenRecordset(MainRS1)   
set useRS = RS_int.Clone

You can even modify this so that all you pass in is the string for the #1 and #2 recordsets without having to open them at all. By the way, the parenthesis are needed because I am proposing a sub-query where you had your recordsets.

 

[a.jasper@gmx.ch]

Thanks a lot for technical backround, The_Doc_Man!
It was very helpful for resolve my problem. After careful consideration the best and easiest method will be to copy datas from recordsets to temporary tables.

But I will describe the problem in simplified way. May be do you have another starting point.


I have recordset(s) generated by followed SQL-statement ( I apply successive the SQL-statement to many databases )

public sub connect_cols()

	dim DB as database
	dim SQL as string
	dim RS as dao.recordset
	dim RS_comp as dao.recordset
	dim TBL as dao.recordset

	SQL = "
		SELECT 
			G.GATTUNG_ID, 
			G.PREISTABELLE_ID, 
			switch(
				string_compare(T1.TEXT_AUSDRUCK,T2.TEXT_AUSDRUCK,"kombinatorik 4") < 0.24, T2.TEXT_AUSDRUCK+' '+T1.TEXT_AUSDRUCK,
				string_compare(T1.TEXT_AUSDRUCK,T2.TEXT_AUSDRUCK,"kombinatorik 4") >= 0.24, T2.TEXT_AUSDRUCK
			) AS Bezeichnung
		FROM 
			(SELECT
				TICKET_TEXT_ID,
				TEXT_AUSDRUCK
			FROM
				TITE 
			WHERE 
				VV_ID=72 and
				VERSION_ID=1 and
				(VP_NAME_AUSDRUCK='DV_D_GATTUNG_BEZEICHNUNG2' or VP_NAME_AUSDRUCK='DV_D_GATTUNG_BEZEICHNUNG')
			)AS T1, 
			(SELECT
				TICKET_TEXT_ID,
				TEXT_AUSDRUCK
			FROM
				TITE 
			WHERE 
				VV_ID=72 and
				VERSION_ID=1 and 
				VP_NAME_AUSDRUCK='DV_D_GATTUNG_BEZEICHNUNG'
			) AS T2, 
			(SELECT 
				GA.GATTUNG_ID,
				GA.PREISTABELLE_ID,
				count (GA.GATTUNG_ID) as Zaehler
			FROM
				TITE as T, 
				(SELECT
					GATTUNG_ID, 
					PREISTABELLE_ID
				FROM 
					GAVA
				WHERE 
					VV_ID=72 and
					VERSION_ID=1
				ORDER BY 
					GATTUNG_ID 
				)as GA
			WHERE
				T.VV_ID=72 and
				T.VERSION_ID=1 and
				(T.VP_NAME_AUSDRUCK='DV_D_GATTUNG_BEZEICHNUNG2' or T.VP_NAME_AUSDRUCK='DV_D_GATTUNG_BEZEICHNUNG' ) and 
				T.TICKET_TEXT_ID=GA.GATTUNG_ID
			GROUP BY 
				GA.GATTUNG_ID, GA.PREISTABELLE_ID
			) AS G
		WHERE 
			(	T1.TICKET_TEXT_ID =T2.TICKET_TEXT_ID and 
				G.GATTUNG_ID =T1.TICKET_TEXT_ID and 
				G.Zaehler = 1 and  T1.TEXT_AUSDRUCK = T2.TEXT_AUSDRUCK
			) or
			(	T1.TICKET_TEXT_ID =T2.TICKET_TEXT_ID and 
				G.GATTUNG_ID =T1.TICKET_TEXT_ID and 
				G.Zaehler = 2 and  
				T1.TEXT_AUSDRUCK <> T2.TEXT_AUSDRUCK
			)
		; 
	"

	set DB = DBEngine.OpenDatabase(any Database)

	set RS = DB.openrecordset(SQL)
	set TBL = currentdb().openrecordset("TITE")

	set RS_comp = compare_rs_tbl (RS, TBL )

end sub

Then the result(s) of these rocordset(s) compares to a table with related terms via the next SQL-statement. Return value should be a recordset with a column from recordset (Bezeichnung) and a column with highest similarity values from table (FAHRAUSWEIS_LANG).

function compare_rs_tbl (Recordset_ as dao.recordset,Table_ as dao.recordset) as dao.recordset
	
	dim SQL as string

	SQL = "
		SELECT 
			Switch(
				string_compare(A.FAHRAUSWEIS_LANG,B.FAHRAUSWEIS_LANG,"distanz") <> 1 And string_compare(A.FAHRAUSWEIS_LANG,B.FAHRAUSWEIS_LANG,"distanz")=A.C,B.FAHRAUSWEIS_LANG,
				True,A.FAHRAUSWEIS_LANG
			)AS col_b_, 
			B.Bezeichnung AS col_a_
		FROM 
			(SELECT  
				A1.Bezeichnung, 
				A1.C, 
				A1.FAHRAUSWEIS_LANG
			FROM     
				(SELECT 
					Bezeichnung, 
					FAHRAUSWEIS_LANG, 
					string_compare(FAHRAUSWEIS_LANG,Bezeichnung,"distanz") AS C 
				FROM  
					Table_,  
					Recordset_
				) AS A1 
				INNER JOIN 
				(SELECT   
					Bezeichnung, 
					Max(C) AS MaxC 
				FROM     
					(SELECT 
						Bezeichnung,
						FAHRAUSWEIS_LANG, 
						string_compare(FAHRAUSWEIS_LANG,Bezeichnung,"distanz") AS C 
					FROM  
						Table_,  
						Recordset_
					)
					GROUP BY 
						Bezeichnung
				) AS A2 
			ON  
				A1.Bezeichnung = A2.Bezeichnung AND A1.C = A2.MaxC 
			) AS A, 
			(SELECT   
				B1.Bezeichnung,  
				B1.C, 
				B1.FAHRAUSWEIS_LANG
			FROM     
				(SELECT 
					Bezeichnung, 
					FAHRAUSWEIS_LANG, 
					string_compare(Bezeichnung,FAHRAUSWEIS_LANG,"kombinatorik 4") AS C 
				FROM  
					Table_,  
					Recordset_ 
				) AS B1 
				INNER JOIN 
				(SELECT
					Bezeichnung, 
					Max(C) AS MaxC 
				FROM 
					(SELECT 
						Bezeichnung,
						FAHRAUSWEIS_LANG, 
						string_compare(Bezeichnung,FAHRAUSWEIS_LANG,"kombinatorik 4") AS C 
					FROM  
						Table_,
						Recordset_
					)
					GROUP BY 
						Bezeichnung
				) AS B2 
			ON  
				B1.Bezeichnung = B2.Bezeichnung AND 
				B1.C = B2.MaxC 
			) AS B
		WHERE
			A.bezeichnung=B.Bezeichnung AND 
			(	string_compare(A.FAHRAUSWEIS_LANG,B.FAHRAUSWEIS_LANG,"kombinatorik 4") = 1 Or 
				string_compare(A.FAHRAUSWEIS_LANG,B.FAHRAUSWEIS_LANG,"kombinatorik 4") > string_compare(A.FAHRAUSWEIS_LANG,B.FAHRAUSWEIS_LANG,"distanz")
			);
	"

	set compare_rs_tbl = currentdb().openrecordset(SQL)

end function