devxlogo

Creating Dynamic SQL

Creating Dynamic SQL

Question:
I’m trying to develop a dynamic query handler within VB that will prompt users for parameters as set up in the parameter list in MS Access (v1.1). I wish to dynamically build the param list with .listparameters (at run time) and then use this list to assign values to these same parameters.My problem is that VB/Access will not allow assignment of values to parameters without explicitly referring to actual parameter names in code (ie. a using a string/variable as returned by .listparameters to refer to such parameters is apparently not possible).

Answer:
The way I’ve done this in the past is to dynamically generate the actual SQL needed to do the query. It’s a lot more reliable and clear than trying to deal with parameters, which Access/VB doesn’t do very well. As the users select pieces of data to query on, you build up the SQL statement to retrieve the appropriate data.

devx-admin

Share the Post: