prabhafriend
03-22-2009, 03:03 PM
Hello Friends. I am Prabha Karan from Thanjavur (South India). Recently it happened to me to write a very long query (as SQL code) i.e. above 1024 characters in length to store in a recordset object. I hope you all know that all strings whether it is SQL code or a variable is strictly restricted up to 1024 characters only. But what happens if a table contains about 150 columns and we have put conditions in each and every column through SQL code. Definitely it will fill a whole notebook all by itself. After analyzing so many ways, I figured out a way but I don’t know why it doesn’t works well. The way I figured is to store the complete SQL code into a memo field of a table. Then we just have to store the table containing the code in a recordset and finally have to reference the fieldname of the recordset containing the SQL code in necessary places. Let’s assume...
Structure of the codetable:
Id = auto number
Code = memo
Data of the codetable:
Id = 1
Code = Select * from sometable where id = 1;
Sub Sample ( )
Dim rset (3) as recordset
‘Get data from the table containing code as memo
Set rset (0) = currentdb.openrecordset (“codetable”)
‘Have to get the result recordset indirectly putting codes
Set rset (1) = currentdb.openrecordset (rset (0) (“code”))
Surprisingly this method works for a scenario. You see I haven’t put any variables there. It works for this scenario. But I don’t know why this is not working if we reference a variable indirectly through the memo value. Let’s change the code like this…
‘Indirectly getting the data from the textbox in the form
Code = “Select * from sometable where id = “&me.txt1.value&”;”
I suspect that I am not following some syntax correctly to get the result through this way. I hope there are experts here to explain me that why this is not happening in this way and also helps me to achieve this. Thank You.
Structure of the codetable:
Id = auto number
Code = memo
Data of the codetable:
Id = 1
Code = Select * from sometable where id = 1;
Sub Sample ( )
Dim rset (3) as recordset
‘Get data from the table containing code as memo
Set rset (0) = currentdb.openrecordset (“codetable”)
‘Have to get the result recordset indirectly putting codes
Set rset (1) = currentdb.openrecordset (rset (0) (“code”))
Surprisingly this method works for a scenario. You see I haven’t put any variables there. It works for this scenario. But I don’t know why this is not working if we reference a variable indirectly through the memo value. Let’s change the code like this…
‘Indirectly getting the data from the textbox in the form
Code = “Select * from sometable where id = “&me.txt1.value&”;”
I suspect that I am not following some syntax correctly to get the result through this way. I hope there are experts here to explain me that why this is not happening in this way and also helps me to achieve this. Thank You.