Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2013

    Unanswered: using a variable instead of table name in SQL query


    would it be possible to use a variable in the FROM statement of an access query? I thought of something like

    SELECT A.*
    FROM GetTable() AS A;
    where GetTable() is a public function of a module, returning a string, but it does not work.

    Thanks in Advance,

  2. #2
    Join Date
    Apr 2004
    outside the rim
    Where are you trying to do this?

    If you are in code, it would be something like:
    Dim sSQL As String
    sSQL = "SELECT A.* FROM " & GetTable() & " AS A;"
    You can then create an object variable for a query, open the query through code, assign your SQL statement as the source SQL, save the query and then run it.

    If you want to simply use this as the actual source SQL for the query with a variable (function) as the sources, you may be out of luck in Access.
    have fun!

    Todd S.
    click to email

    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET

    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  3. #3
    Join Date
    Apr 2013
    Thanks for your immediate reply,

    I tried the following and it worked for my purposes.

    I first created an arbitary query named "qry_test" and then execute the code

    Public Function mod_test_func()
        Dim db As Database
        Dim qdf As QueryDef
        Dim strSQL As String
        Dim name_tab As String
        Set db = CurrentDb
        Set qdf = db.QueryDefs("qry_test")
        name_tab = "tab_test"
        strSQL = "SELECT * FROM " & name_tab & ";"
        qdf.SQL = strSQL
    End Function
    Is that what you ment?

    Thanks again,

Posting Permissions

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