Choose tbl defined by Paremeter

Posted on 2009-02-24
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
Question by:Student_101
    LVL 39

    Expert Comment

    by:Kyle Abrahams
    You can.

    Dim a string as your query to hold your sql.

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

    then execute the SQL.

    Author Comment

    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

    LVL 39

    Expert Comment

    by:Kyle Abrahams
    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.
    LVL 39

    Accepted Solution

    With regards to your code:
    If cbo_OPerationParam.Text = "Daily Mode" Then
     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

    Author Comment

    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"& _

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

    Works great now.
    Thanks !

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now