INSERT INTO and VALUES

[Vulcan1500]

I'm trying to write a SQL statement to fill a table with the backup files as stored in the backup folder. I managed to create the table tblBackup with the fields strBackupName and dtmBackupDateTime. The names of the backup file are: Backup with the date and time of the backup added. I'm able to read the backup files and store the name of the file in strBackupName and to derive the date and time from the name and store this in dtmBackupDateTime.

Example:
strBackupName='Backup 31-12-2007 08:30:05'
dtmBackupDateTime=#12/31/2007 08:30:05#

My problem now is to insert strBackupName and dtmBackupDateTime in tblBackup. To do this I'm using the following statement:

DoCmd.RunSQL "INSERT INTO tblBackup ([strBackupName], [dtmBackupDateTime]) VALUES (strBackupName, dtmBackupDateTime);"

When this statement is executed ACCESS is asking via a messagebox to input strBackupName and dtmBackupDateTime. It does not accept the variables! Pls help me with this problem.

 

[tehNellie]

DoCmd.RunSQL "INSERT INTO tblBackup ([strBackupName], [dtmBackupDateTime]) VALUES (strBackupName, dtmBackupDateTime);"

You need to break the string up, Access is seeing strbackupname as a literal part of the SQL string rather than "use the value of this variable"

DoCmd.RunSQL "INSERT INTO tblBackup ([strBackupName], [dtmBackupDateTime]) 
VALUES (" & chr(34) & strBackupName & Chr(34) & "," & dtmBackupDateTime & ");"

You can use multiple double quotes, but I personally find this gets confusing so I wrap the variable in the ascii code for " (Chr(34)).

On a general basis, if you're having trouble with a SQL string, output it using debug.print or msgbox first so you can see exactly how the final string looks.

Off the top of my head I can't remember if you need to wrap a date in quotes so you might need to wrap dtrmbackupdatetime in quotes as well.

 

[Vulcan1500]

Thanks for your help tehNellie and it works. Now I would like to order the table on dtmBackupDateTime descending. Again I have a problem with the SQL statement. Maybe you can give me directions again.

DoCmd.RunSQL "SELECT [tblBackup].[pkBackupID], [tblBackup].[strBackupName], [tblBackup].[dtmBackupDateTime]
FROM tblBackup
ORDER BY [tblBackup].[dtmBackupDateTime] DESC;"

The RunSQL does not accept the string as an instruction.

 

[tehNellie]

you can't use SELECT with docmd.runSQL only "action queries" if memory serves me correct.

Depending on the context, you can set the form/control/query recordsource to the strSQL or just create a static query with the SELECT statement and then open the form or use the query to populate a recordset.

 

[Vulcan1500]

What I try to achieve is to sort the data of tblBackup on dtmBackupDateTime in descending order, so that the latest backup is on top of the list. After being able to do that I want to keep the required number of backups-1 and then backup the data.

 

[tehNellie]

You can't sort the data in your table like that and basically you don't need to.

The SELECT statement does just that but doesn't alter the underlying data in the table.

You can read the data into a recordset using

Dim db as database
dim rs as dao.recordset
dim strSQL as string

set db = currentdb

strSQL = "SELECT [tblBackup].[pkBackupID], [tblBackup].[strBackupName], " & _
"[tblBackup].[dtmBackupDateTime] " & _
"FROM tblBackup " & _
"ORDER BY [tblBackup].[dtmBackupDateTime] DESC;"

set rs = db.openrecordset(strSQL)

If you're only interested in the last record you can use SELECT TOP 1 or SELECT MAX(dtmBackupDateTime) to only pull the last record that you're interested in rather than the whole table.

 

[Vulcan1500]

Thanks for your help tehNellie. It's now working . By the way, if the recordset is completed and ordered can I then get the number of records in the set?