What's wrong w/ this SQL stmt?

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
 
D

Douglas J. Steele

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!)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Modify sql statement 2
Archival 5
Archive 3
Moving Info 6
Can anyone tell me the problem with this code 3
Rolling back transactions 2
Append query works, but doesn't... 2
Pls help, my VBA does not work 3

Top