Make Table Query

  • Thread starter Thread starter kateri4482
  • Start date Start date
K

kateri4482

I have the following query that I run every few months to backup one of my
tables. But instead of "Summer08", I want it to automatically put today's
date so that I don't have to manually change the "Summer08" to something else
before running it. How do I do that?

SELECT TblEnhancedCodes.* INTO TblBackupEnhancedCodesSummer08
FROM TblEnhancedCodes;

Thanks!
 
You could do it in code using something like the following;

Dim strTableName As String, strSQL As String

strTableName = "tblBackupEnhancedCodes" & Format(Date, "ddmmmmyy")

strSQL = "Select tblEnhancedCodes.* Into " & strTableName _
& " From tblEnhancedCodes;"

CurrentDb.Execute strSQL, dbFailOnError

This would give you a backup table name like;

tblBackupEnhancedCodes12December08

You could run it from a command button, etc.
 
I have the following query that I run every few months to backup one of my
tables. But instead of "Summer08", I want it to automatically put today's
date so that I don't have to manually change the "Summer08" to something else
before running it. How do I do that?

SELECT TblEnhancedCodes.* INTO TblBackupEnhancedCodesSummer08
FROM TblEnhancedCodes;

Thanks!
Click to expand...

Access Tables very rarely become corrupt. Access Databases *can* become
corrupt! Backing up a table in your database into another table in the same
database is probably a Bad Idea; if your database gets corrupted you will lose
*both*.

Instead, use Windows Explorer or your system backup software to make a backup
of the entire .mdb file.

If you're just backing it up in case of misguided user edits, you will need to
run the query from VBA code, and construct the SQL of the query in code:

Private Sub BackupCodes()
Dim strSQL As String
strSQL = "SELECT tblEnhancedCodes.* INTO TblBackupEnhancedCodes" _
& Format(Date, "q\Qyyyy") & " FROM TblEnhancedCodes;")
Currentdb.Execute strSQL, dbFailOnError
End Sub

This will save it as (e.g.) TblBackupEnhancedCodes4Q2008 - I don't know of any
way to easily get "Summer" or "Winter" though I'm sure you could with a bit of
extra code.
 
Thanks. That makes sense.

Beetle said:
You could do it in code using something like the following;

Dim strTableName As String, strSQL As String

strTableName = "tblBackupEnhancedCodes" & Format(Date, "ddmmmmyy")

strSQL = "Select tblEnhancedCodes.* Into " & strTableName _
& " From tblEnhancedCodes;"

CurrentDb.Execute strSQL, dbFailOnError

This would give you a backup table name like;

tblBackupEnhancedCodes12December08

You could run it from a command button, etc.
Click to expand...
 
Thanks John. This is a small table that I backup before I delete the data in
two fields. I also backup the entire database before I run the make table
query.
 
Back
Top