[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Choose tbl defined by Paremeter

Posted on 2009-02-24
5
Medium Priority
?
262 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:Student_101
  • 3
  • 2
5 Comments
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 23721932
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
 

Author Comment

by:Student_101
ID: 23721964
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
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 23722054
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
 
LVL 41

Accepted Solution

by:
Kyle Abrahams earned 1000 total points
ID: 23722088
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
 

Author Comment

by:Student_101
ID: 23722265
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question