Choose tbl defined by Paremeter

Hi Experts,

I have a form in VB6 which contains my grid, I have 2 tables in my database with different results.

I want to know of it's possible to have the table name as a parameter, which I can specify from VB6. I know how to specify the parameter and how to make it work.

But I don't know If the parameter can be the table name.

For example, I call my qry_XYZ, which takes tbl_A by as coded in the query. Can I have tbl_A as a parameter, which I can define from my VB6 combo box, so if the user selects tbl_B, then it should call tbl_b in qry_XYZ
Student_101Asked:
Who is Participating?
 
Kyle AbrahamsConnect With a Mentor Senior .Net DeveloperCommented:
With regards to your code:
If cbo_OPerationParam.Text = "Daily Mode" Then
 ' DO SOMETHING ELSE
 sSql = "select * from tbl_operation"
ElseIf cbo_OperationParam.Text = "Daily Mean" Then
sSql = "SELECT tbl_Operation.Date, Avg(tbl_Operation.Value) AS Average, Count(tbl_Operation.Value) AS n, StDev(tbl_Operation.Value) AS St_Dev
FROM tbl_Operation
GROUP BY tbl_Operation.Date
ORDER BY tbl_Operation.Date"
end if

'execute the appropriate SQL. which is in sSql
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
You can.

Dim a string as your query to hold your sql.

If param 1 selected:
sql = "select * from table 1"
else
sql = "select * from table 2"

then execute the SQL.
0
 
Student_101Author Commented:
Hi Ged,

I know how to do it like that,

But I call my query in the SQL part, my query is as follows, that's why I just call the query.

Do you think I will get the same result if I write the query in VB, like you have above.
''' How I call it from VB 
ElseIf cbo_OperationParam.Text = "Daily Mean" Then
sSql = "Select * from qry_Daily_averages"
 
''' The actual Query
 
SELECT tbl_Operation.Date, Avg(tbl_Operation.Value) AS Average, Count(tbl_Operation.Value) AS n, StDev(tbl_Operation.Value) AS St_Dev
FROM tbl_Operation
GROUP BY tbl_Operation.Date
ORDER BY tbl_Operation.Date;

Open in new window

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
It's a general programming practice to let the Application do the work.  The database and queries just give you the data, let the program massage it.

You will get the correct results if you choose it from VB and only execute the appropriate SQL.  Let the SQL handle the appropriate DB logic (eg: triggers, rules for the data itself, etc.).  Let your program handle the manipulation or business logic.
0
 
Student_101Author Commented:
Perfect !

I tried doing what you said first, but I was getting an error that the argument includes a reserved word, so I thought I couldn't call things such as AVG() Count()..etc from VB6

But what my mistake was that, I forgot to leave one space at the end of the line, for example

sSql = "Select * from tbl"& _
"Where...."

So the tbl and Where were one word, that's why the error occurred.

Works great now.
Thanks !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.