Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2014
    Posts
    2

    Unanswered: Variable Table Name in SQL text box

    Hi,

    Have read these forums over time to solve various problems and have been able to come up with a solution to a problem I am having doing this. But it does not work.

    This is part of a bigger solution, but I need this to work before moving onto generating all the results.

    I am trying to run a query where the table to be queried is variable and entered into a text box by the user. Eventually removing the user input and working from a recordset created which contains the table names. The database tables could be designed better but I have to work with what I have. Basically there is a table with client data, client details and a field which contains a client number. There is then 1 data table for each client named after the client number in the client list table.

    So to start off with I want to run the query based on the user input in the text box. Below is what I have so far but I am getting a syntax error I cannot fix, error 3075 missing operator in query expression '[client].Jobnumber

    Below is what I am using, will this work? Can you see anything obvious I have missed.

    Code:
    Dim db As Database
    Dim rst As DAO.Recordset
    Dim tablename As String
    Dim StrSQL As String
    Dim client As String
    
    Set db = CurrentDb
    
    StrSQL = "SELECT [client].JobNumber, [client].CompletionDate FROM [client] WHERE ((([client].completiondate ) IS NULL))"
    StrSQL = Replace(StrSQL, "[client]", Me![tablename])
    
    DoCmd.RunSQL StrSQL
    
    End Sub
    Any help will be appreciated, I want to figure this out myself, but time is becoming a factor.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. I don't see why you use [client] as a place marker for the table name, nor why you use a variable instead of a constant. I would have used:
    Code:
    Const c_SQL As String = "SELECT @T.JobNumber, @T.CompletionDate FROM @T WHERE (((@T.completiondate ) IS NULL))"
    
    Dim StrSQL As String
    
    StrSQL = Replace(c_SQL, "@T", Me![tablename])
    2. I don't see why you declare a DAO.Database object while you use a DoCmd.RunSQL command instead of using the .Execute method of the database object. I would have expected:
    Code:
    Dim dbs As DAO.Database
    ...
    Set dbs = CurrentDb
    dbs.Execute strSQL, dbFailOnError
    3. You cannot use DoCmd.RunSQL, nor dbs.Execute with a SELECT SQL statement. Both can only be used with action queries (DELETE, INSERT, UPDATE) or with DDL queries. It's either creating a Querydef object then use DoCmd.OpenQuery or using a DAO.Recordset if you use a DAO.Database object.
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sinndho's solution will work, as you'd expect
    however just to clear up a couple of matters you need to delimit table and column names with [ and ] if there are spaces in the table or column names
    in Accces JET you dont' need to fully defien the column names with a table name in a query unless there is more than one column so the tabel name qulaification indicates which table you want the column to come form

    a safer variation of Sinndho's code is
    Code:
     Const c_SQL As String = "SELECT [@].JobNumber, [@].CompletionDate FROM [@] WHERE ((([@].completiondate ) IS NULL))"
    or an alternative approach (does the same thing)
    Code:
    StrSQL = Replace(c_SQL, "@T", "[" & Me![tablename] & "]")
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You can use this to see the finished SQL:

    Debugging
    Last edited by pbaldy; 01-22-14 at 14:19. Reason: Deleted bit about action queries, didn't notice it had already been mentioned.
    Paul

  5. #5
    Join Date
    Jan 2014
    Posts
    2
    Thank you all for your input, it is working now, but I am also think I have bitten off more than I can chew. Am using this to teach myself but I don't know how I would of got to the answer provided as I have been reading for some time.

Posting Permissions

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