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).


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.

“The trouble with programmers is that you can never tell what a programmer is doing until it's too late.” - Seymour Cray