A Memorable query!

msoffice , access , vba India
  • 11 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.

Post a reply

No one has replied yet! Why not be the first?

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Any fool can write code that a computer can understand. Good programmers write code that humans can understand.” - Martin Fowler