Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2012
    Posts
    2

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

    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 new table name. This is purely for archival purposes.


    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?

    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

    Here is SQL and I can get it to run with the action query, just can autmatically get the date appended to the table name that way.

    SELECT TBLCommissionTransactions.* INTO TBLCommissionTransactions20120404-1100 IN 'T:\folder\TblBackups.mdb'
    FROM TBLCommissionTransactions;

  2. #2
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    Looks like you're mssing some quotes - try this:

    strSQL = "SELECT TBLCommissionTransactions.* INTO " & strTableName & '" IN " & strFilename & "' FROM TBLCommissionTransactions;"
    Regards
    JD

    Software-Matters

  3. #3
    Join Date
    Apr 2012
    Posts
    2

    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;"

Posting Permissions

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