Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2009
    Posts
    16

    Unanswered: table name in parameter

    Hi
    I am using code from code bank to export spread sheet. below is the brief part of that where i have problem


    Code:
    Sub ExportData_Sheet_Basic()
    Set rs = db.OpenRecordset("SELECT Date, TowerTopic, Items, Media FROM Dupl")
    End Sub
    The above code works great if Dupl is the table name but I want to use a parameter or variable to call this function for another table.

    Can some body suggest me about how to pass table name in variable or parameter with the above code

    Thank you

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Presuming the table name is in some variable:

    Set rs = db.OpenRecordset("SELECT Date, TowerTopic, Items, Media FROM " & VariableName)
    Paul

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    you can't use parameters in table names. You'd have to build a string dynamically.


    Also, if you have the exact same columns in a different table, then you can probably solve your problem with proper normalization as opposed to more identical tables.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Nov 2009
    Posts
    16
    Thank you guys,
    I tried pbaldy way but that didn't work. Actually I don't know how to set Variable for table

    Is it As String or As Object. I tried both but didn't work.

    Teddy, Yes my column are same because I am running a query to create new table that sort some record out and then export it to excel.

    I don't understand what proper normalization is. Can you explain

    Thank you

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Didn't work? This certainly would (declaring and setting the other variables of course):

    Dim strTableName As String
    strTableName = "ActualTableName"
    Set rs = db.OpenRecordset("SELECT Date, TowerTopic, Items, Media FROM " & strTableName )

    You may want to expand on what you're trying to do. Normalization:

    Fundamentals of Relational Database Design -- r937.com
    Paul

  6. #6
    Join Date
    Nov 2009
    Posts
    16
    Nope, not working. Works fine when I put Dupl after FROM

    Dim strTableName As String
    strTableName = "Dupl"
    Set rs = db.OpenRecordset("SELECT Date, TowerTopic, Items, Media, Audience FROM strTableName")

    Thank you

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You're not doing what I posted, so it's no surprise that it didn't work.

    Note the closing of the quotes and the ampersand.
    Paul

  8. #8
    Join Date
    Nov 2009
    Posts
    16
    Sorry about that it works great.

    Thank you pbaldy

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problem. If the variable is inside the quotes, Access thinks it's part of the literal string and won't evaluate it for its value.
    Paul

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You can use views/queries for this. You don't need to create a brand new table.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Nov 2009
    Posts
    16
    Yes, I have main table and then few queries and the above code will export it to spreadsheet. That is why I want to use variable in export code that export spread sheet for multiple Queries and table

    Thanks all

Posting Permissions

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