Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2002
    Posts
    5

    Unanswered: Prompt for Table Name

    I want to use the same query to create multiple tables. In order to do this, I would have to prompt the user for a table name when the query is run. How would I do that.

    Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95

    Re: Prompt for Table Name

    I can't think of a way to do it in the query builder, but it can be done with VBA, like this:

    Option Compare Database
    Option Explicit

    Sub Example()
    Dim dbs As Database, qdf As QueryDef
    Dim a As String

    Set dbs = CurrentDb
    a = InputBox("Tablename?")
    Set qdf = MakeQueryDef("qryTemp")
    qdf.SQL = "SELECT * FROM " & a
    DoCmd.OpenQuery qdf.Name

    'Housekeeping
    Set qdf = Nothing
    Set dbs = Nothing

    End Sub

    Private Function MakeQueryDef(QueryName As String) As QueryDef
    'This function creates a querydef with the name QueryName, if it doesn't already exist.
    'If it already exists, it bases the querydef on the existing query with the name QueryName.
    Dim dbs As Database

    Set dbs = CurrentDb

    On Error Resume Next

    Set MakeQueryDef = dbs.CreateQueryDef(QueryName)
    If Err.Number = 3012 _
    Then
    Set MakeQueryDef = dbs.QueryDefs(QueryName)
    End If

    End Function

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    Re: Prompt for Table Name

    you cant but you can nab the sql for how to build the table and then run it this way with a variable table name

    docmd.runSQL("SELECT <fields> INTO "& inputbox("TableName") &" FROM <tables> WHERE <conditions>;")


    Originally posted by Ad Dieleman
    I can't think of a way to do it in the query builder, but it can be done with VBA, like this:

    Option Compare Database
    Option Explicit

    Sub Example()
    Dim dbs As Database, qdf As QueryDef
    Dim a As String

    Set dbs = CurrentDb
    a = InputBox("Tablename?")
    Set qdf = MakeQueryDef("qryTemp")
    qdf.SQL = "SELECT * FROM " & a
    DoCmd.OpenQuery qdf.Name

    'Housekeeping
    Set qdf = Nothing
    Set dbs = Nothing

    End Sub

    Private Function MakeQueryDef(QueryName As String) As QueryDef
    'This function creates a querydef with the name QueryName, if it doesn't already exist.
    'If it already exists, it bases the querydef on the existing query with the name QueryName.
    Dim dbs As Database

    Set dbs = CurrentDb

    On Error Resume Next

    Set MakeQueryDef = dbs.CreateQueryDef(QueryName)
    If Err.Number = 3012 _
    Then
    Set MakeQueryDef = dbs.QueryDefs(QueryName)
    End If

    End Function
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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