CODEWITHSUNDEEP
ms-accessvba
mleezon
mleezon

Reputation: 11

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

Upvotes: 1

Views: 3700

Answers (1)

Fionnuala
Fionnuala

Reputation: 91356

You are missing some braces and quotes:

SELECT TBLCommissionTransactions.* 
INTO [TBLCommissionTransactions20120405-0054] IN 'T:\folder\TblBackups.mdb' 
FROM TBLCommissionTransactions;

So

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

Upvotes: 3

Related Questions