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

[mleezon]

I want to take all the fields in TBLCommissionTr ansactions in current DB and make a new table in T:\folder\TblBa ckups.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

[NeoPa]

mleezon:
What am I doing wrong?

Everything I would say. The whole concept is ill-advised, starting with multiple tables with the same layout. Please read Database Normalisation and Table Structures as it's a very important concept that you really need to appreciate before designing anything at all in a database.

I can't imagine this particular question will be any use to you once you have a basic understanding of Normalisation under your belt, but you might want to post something else (in a new thread) at that point.

[mleezon]

I think your reply does not apply to the original question.

[devu21]

mleezon,

You have to create the table first(Using VBA in the same function) and then append the data to it.

Code to create table:

   Dim dbsdb1 As Database
   Dim tdfNew As TableDef
   Dim prpLoop As Property
   TblName = "TBLCommissionTransactions" & Format(Now(), "yyyymmdd-hhmm") 

   Set dbsdb1 = OpenDatabase("T:\folder\TblBackups.mdb")

   ' Create a new TableDef object.
   Set tdfNew = dbsdb1.CreateTableDef(TblName)

   With tdfNew
      .Fields.Append .CreateField("F1", dbText)
      .Fields.Append .CreateField("F2", dbText)
      .Fields.Append .CreateField("F3", dbText)
      .Fields.Append .CreateField("F4", dbText)
      .Fields.Append .CreateField("F5", dbText)

      dbsdb1.TableDefs.Append tdfNew

   End With

*****
Here you use the append/Insert SQL command.
****

   dbsdb1.Close

[NeoPa]

mleezon:
I think your reply does not apply to the original question.

I can't imagine what would lead you to think that.

Your choice of course, but it's unfortunate that you can't see what would be obvious to anyone with database experience.

[mleezon]

I must have disturbed the database gods.

As I never discussed or asked a question about or even showed anything regarding the structure of my data tables I do not think that the page you referred me to "Database Normalization and Table Structures" applies. I am simply looking for a way to back up certain tables within a database from VBA. And yes, I am self taught with databases and am always willing to learn when someone is willing to teach, but smug responses to make someone feel superior do not teach anything.

[mleezon]

Here is corrected code that works:

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

Thanks to Remou

[NeoPa]

mleezon:
As I never discussed or asked a question about or even showed anything regarding the structure of my data tables I do not think that the page you referred me to "Database Normalization and Table Structures" applies. I am simply looking for a way to back up certain tables within a database from VBA.

I'm sorry you feel that way. Actually, the very fact that you are considering making multiple copies of a table (ignoring that they might hold different data as that is irrelevant to database design) is indication enough to most people (at least with some database design experience) that you don't have a good understanding of the concepts of Normalisation. There's nothing magic or superior about it. I'm sure you would be able to pick up on the same sort of stuff in subjects that you know something about.

mleezon:
I am self taught with databases and am always willing to learn when someone is willing to teach, but smug responses to make someone feel superior do not teach anything.

I would suggest this is not actually true. I would certainly hope that you could learn something from this, but your attitude seems to be so much on the defensive, that you can't even consider that I may be right. If you can get over that, you might find you'll save yourself some hassle. On the other hand, if you continue with your intended approach, and also continue to work in databases, you will surely reach a point where you will appreciate what I tried to save you from. If you think that anyone could feel superior or smug from stating one of the very basics of the subject though, then I would suggest that merely reflects your own confusion.

Ultimately, how you proceed is down to you of course. I'm comfortable at least that I did what I could to try to warn you.

[mleezon]

How about the possibility that before an import procedure that appeneds data into the table in question I would like to make a backup of a table? I don't think that making multiple copies of tables for backup purposes would apply to your normalization document. It is simply that, a back up in case I should need it if the import goes wrong. Most likley I would not, but when I do it is there backed up.

[NeoPa]

mleezon:
How about the possibility that ...

Making a backup wouldn't indicate that, but naming a table with the date would. Think about it.

PS. We don't encourage OPs to select their own posts as Best Answer. In very rare circumstances we can allow this, but your answer is simply putting some code to the concept provided for you by devu21. It explains nothing and is from the OP (The Original Poster - which is whichever member it is who asks the original question. IE. yourself in this case). As such, it doesn't qualify as a Best Answer.