A Memorable query!

• • Reply

  11 years ago

  by prabha_friend

  Karan Prabha

  Mumbai, India

  Joined 12 years 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.
  Report abuse