Results 1 to 4 of 4
  1. #1
    mlee is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2012
    Posts
    3

    Question VBA Code "Query input must contain at least one table or query"

    Sorry, not really well versed in VBA. I have the following code which I modified from search online. When I run it I get the message "Query input must contain at least one table or query" which I am suspecting is failing on strTableName but don't really know for sure. I have played around with it and can't get it to work. What am I doing wrong? I want to take all the fields in TBLCommissionTransactions in current DB and make a new table in T:\folder\TblBackups.mdb and append today’s date and time to the table name. This is purely for archival purposes.


    Private Sub BackupTblCommissions_Click()
    Dim strTableName As String
    Dim strFilename As String
    Dim strSQL As String
    strFilename = "T:\folder\TblBackups.mdb"
    strTableName = "TBLCommissionTransactions" & Format(Now(), "yyyymmdd-hhmm")
    strSQL = "SELECT TBLCommissionTransactions.* INTO " & strTableName & " IN " & _


    strFilename & " FROM TBLCommissionTransactions;"

    CurrentDb.Execute strSQL
    End Sub

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    So - your SQL statement is actually something like this:
    Code:
    SELECT TBLCommissionTransactions.* INTO TBLCommissionTransactions20120403-1200 IN T:\folder\TblBackups.mdb FROM TBLCommissionTransactions;
    ?
    I have never seen that [in red] in a SQL statement.

    I got the same error when I just tried a maketable query like the one you have with the 'IN T:\folder\TblBackups.mdb' in it.
    When I removed that - the query ran and it created a new table with all the data from the original table.

    Is your objective to copy data from one table into a new table in the same database? Or are you trying to create a table in a totally different database?

    Hope this helps!

  3. #3
    mlee is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2012
    Posts
    3
    Yes right from Action query SQL is:
    SELECT TBLCommissionTransactions.* INTO TBLCommissionTransactions20120404-1100 IN 'T:\folder\TblBackups.mdb'
    FROM TBLCommissionTransactions;

    And I can get it to run with the action query, just can get the date appended to the table name that way.


    Quote Originally Posted by Robeen View Post
    So - your SQL statement is actually something like this:
    Code:
    SELECT TBLCommissionTransactions.* INTO TBLCommissionTransactions20120403-1200 IN T:\folder\TblBackups.mdb FROM TBLCommissionTransactions;
    ?
    I have never seen that [in red] in a SQL statement.

    I got the same error when I just tried a maketable query like the one you have with the 'IN T:\folder\TblBackups.mdb' in it.
    When I removed that - the query ran and it created a new table with all the data from the original table.

    Is your objective to copy data from one table into a new table in the same database? Or are you trying to create a table in a totally different database?

    Hope this helps!

  4. #4
    mlee is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2012
    Posts
    3

    Thumbs up Here is the code that worked

    Thanks to Remou


    Code:
    strFilename = "'T:\folder\TblBackups.mdb'"
    strTableName = "[TBLCommissionTransactions" & Format(Now(), "yyyymmdd-hhmm" & "]")
    strSQL = "SELECT TBLCommissionTransactions.* INTO " & strTableName & " IN " & _
    strFilename & " FROM TBLCommissionTransactions;"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 01-11-2012, 12:34 PM
  2. Replies: 1
    Last Post: 08-02-2011, 07:21 AM
  3. Replies: 3
    Last Post: 07-23-2011, 09:12 AM
  4. Query Table for "*" and then Count all instances
    By Steven.Allman in forum Queries
    Replies: 14
    Last Post: 09-10-2010, 07:45 PM
  5. Replies: 0
    Last Post: 09-17-2009, 12:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums