Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Unanswered: here is a hard one - how to change table name in a from statement

    Hi... I would like to do the following with an MS-ACCESS-SQL Statement

    I have the following tables
    table1
    table2
    table3

    Then I have the table table_no, which contains a field table_no.value that contains the table no.

    I am looking for an sql statement for MS Access, that selects the table no that is listed in the field table_no.value (you can assume there is only one record).

    So what I am looking for is a statement that could look like this

    select * from table & table_no.value

    Basically that displays the table no, as specified in the table_no field.

    Regards Walter

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    You basically have it already! In Vba you can open the query def, edit it's sql (qdf.sql) and update what you need to. I have posted solutions for dynamic queries on this forum this past week. I don't have the code at home.

  3. #3
    Join Date
    Oct 2003
    Posts
    5

    dynamic querry

    Thanks for your reply. Now I was looking for a solution that works in the sql-view of a querry / meaning without using vb - unless I can use vb in the sql-view of a querry?

  4. #4
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    No, I know of no way to use a variable as a table name directly in a query since the whole point of creating the query is so it will parse. If it does not know the table name, it cannot parse and will give an error. My method involves creating a parsed query then altering it in vba.

  5. #5
    Join Date
    Oct 2003
    Posts
    5
    The reason I am doing this is the following.

    I have to do some extensive calculations that consist of about 10 consequtive querries. Querry 7 can be one of 3 querries, each representing an option for the entire calculations. I would like to have parameter (in a table) control which option is being calculated. I can not calculate all Options and then filter the solution I need, because the time to calculate 1 option is about 10min - so doing them all would triple my calculation time.

    Right now all I have to do is execute the last querry, and all 10 previous querries are executed as needed. The problem is to calculate an other option, right now I have to replace Querry 7 with the querry of the option I want.

    Maybe any other ideas how to solve this...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •