What's wrong w/ this SQL stmt?

  • Thread starter Thread starter Rod
  • Start date Start date
R

Rod

I have looked at this append sql and can't figure out why I get error:
"Archiving failed:#3000 Desc: Reserved error (-1524); there is no message
for this error."

Private Sub Archival_Click()
On Error GoTo Err_Archival

Dim ws As DAO.Workspace 'Current workspace (for transaction).
Dim db As DAO.Database 'Inside the transaction.
Dim bInTrans As Boolean 'Flag that transaction is active.
Dim strSql As String 'Action query statements.
Dim strMsg As String 'MsgBox message.

'Step 1: Initialize database object inside a transaction.
Set ws = DBEngine(0)
ws.BeginTrans
bInTrans = True
Set db = ws(0)

'Step 2: Execute the append.
strSql = "INSERT INTO tblCandidatesArchive "
strSql = strSql & " IN 'G:\Backup\My
Documents\PFS\Recruiting\MyCandidateArchiveTable.accdb' "
strSql = strSql & " SELECT * FROM tblCandidates"
strSql = strSql & " WHERE Archive <= #" & Date & "#;"

'The next statement is where it jumps to Err_Archiving:
db.Execute strSql ', dbFailOnError

Exit_Archival:
'Step 5: Clean up
On Error Resume Next
Set db = Nothing
If bInTrans Then 'Rollback if the transaction is active.
ws.Rollback
End If
Set ws = Nothing
Exit Sub

Err_Archival:
MsgBox "Archiving failed: # " & Err.NUMBER & " Desc: " & Err.Description,
vbExclamation
ws.Rollback
Resume Exit_Archival

End Sub
 
Don't know whether this is the issue, but even if you're positive that the
two tables are identical, it's always best to explicitly list the fields in
an INSERT INTO statement.

strSql = "INSERT INTO tblCandidatesArchive " & _
" IN 'G:\Backup\My Documents\PFS\Recruiting\MyCandidateArchiveTable.accdb'
" & _
"(Field1, Field2, Field3, ...) " & _
" SELECT Field1, Field2, Field3 FROM tblCandidates" & _
" WHERE Archive <= " & Format(Date, "\#yyyy\-mm\-dd\#")

I'm assuming that Date is supposed to be the built-in VBA Date function. (If
it's anything else, rename it!) The reason for using the Format function
around the date is because you should never assume anything about the Short
Date choice for your users.

I assume you've validated that you have the necessary permissions on
G:\Backup\My Documents\PFS\Recruiting\ (and that it's mapped!)
 
Back
Top