Community discussion forum

A Memorable query!

• • Reply

  8 months ago

  by prabha_friend

  Karan Prabha

  Chennai, India

  Joined 9 months ago
  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.
  Post was edited on 22/03/2009 23:01:47 Report abuse