Archival

G

Guest

I have an "Archival" button which is suppose to archive records from one
table to another (actually move the data). I got the code from:
http://allenbrowne.com/ser-37.html. When I adapted the code for my situation
I received "Syntax error in INSERT INTO statement" (Error 3134). The idea is
[Archive] is the date which a record should be moved. If [Archive] is <=
Date
then it is ready to be moved and should be moved. It doesn't appear to like
the insert statement. I posted this question before but the help dropped.

Any thoughts? Here is my adapted code:

Private Sub Archival_Click()
'Sub DoArchive()
On Error GoTo Err_DoArchive
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 MyArchiveTable ( MyField, AnotherField, Field3 ) "
& _
' "IN ""C:\My Documents\MyArchive.mdb"" " & _
' "SELECT SomeField, Field2, Field3 FROM MyTable WHERE (MyYesNoField =
True);"

strSql = "INSERT INTO tblCandidatesArchive * IN
C:\MyCandidateArchiveTable.mdb SELECT * FROM tblCandidatesTST WHERE (Archive
< = Date);"
'******************

db.Execute strSql, dbFailOnError

'Step 3: Execute the delete.
strSql = "DELETE FROM tblCandidatesTST WHERE (Archive < = Date);"
db.Execute strSql, dbFailOnError

'Step 4: Get user confirmation to commit the change.
strMsg = "Archive " & db.RecordsAffected & " record(s)?"
If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then
ws.CommitTrans
bInTrans = False
End If

Exit_DoArchive:
'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_DoArchive:
MsgBox Err.Description, vbExclamation, "Archiving failed: Error " &
Err.NUMBER
Resume Exit_DoArchive
End Sub
 
G

Guest

Rod,

I haven't inserted records into an external database, but I did notice two
things. The external database reference in the INSERT INTO clause should be
enclosed in quotes. In Allen Browne's example, he used double double-quotes
(I tend to use single quotes)

And you can't use "(Archive < = Date);".
If you add a line after the "strSql = " that looks like

Msgbox strSql

, you will see that the WHERE clause is

....."WHERE (Archive < = Date);

when you really want

....."WHERE (Archive < = #10/1/2006#);"

So you should change the WHERE clause to

....."WHERE (Archive < = #" & Date() & "#);"


Try this line:

strSql = "INSERT INTO tblCandidatesArchive *"
strSql = strSql & " IN 'C:\MyCandidateArchiveTable.mdb' "
strSql = strSql & " SELECT * FROM tblCandidatesTST"
strSql = strSql & " WHERE (Archive < = #" & Date() & "#);"


Also, is "MyCandidateArchiveTable.mdb" in C:\ or in MyDocuments or a
different location? Make sure the path is correct.

The delete line should be like:

strSql = "DELETE FROM tblCandidatesTST"
strSql = strSql & " WHERE (Archive < = #" & Date() & "#);"


Is the field [Archive] a Date/Time field with only the Date and not the
Time? It will make a difference on which records are copied and deleted.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Rod said:
I have an "Archival" button which is suppose to archive records from one
table to another (actually move the data). I got the code from:
http://allenbrowne.com/ser-37.html. When I adapted the code for my situation
I received "Syntax error in INSERT INTO statement" (Error 3134). The idea is
[Archive] is the date which a record should be moved. If [Archive] is <=
Date
then it is ready to be moved and should be moved. It doesn't appear to like
the insert statement. I posted this question before but the help dropped.

Any thoughts? Here is my adapted code:

Private Sub Archival_Click()
'Sub DoArchive()
On Error GoTo Err_DoArchive
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 MyArchiveTable ( MyField, AnotherField, Field3 ) "
& _
' "IN ""C:\My Documents\MyArchive.mdb"" " & _
' "SELECT SomeField, Field2, Field3 FROM MyTable WHERE (MyYesNoField =
True);"

strSql = "INSERT INTO tblCandidatesArchive * IN
C:\MyCandidateArchiveTable.mdb SELECT * FROM tblCandidatesTST WHERE (Archive
< = Date);"
'******************

db.Execute strSql, dbFailOnError

'Step 3: Execute the delete.
strSql = "DELETE FROM tblCandidatesTST WHERE (Archive < = Date);"
db.Execute strSql, dbFailOnError

'Step 4: Get user confirmation to commit the change.
strMsg = "Archive " & db.RecordsAffected & " record(s)?"
If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then
ws.CommitTrans
bInTrans = False
End If

Exit_DoArchive:
'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_DoArchive:
MsgBox Err.Description, vbExclamation, "Archiving failed: Error " &
Err.NUMBER
Resume Exit_DoArchive
End Sub
Click to expand...
 
G

Guest

Perhaps we are getting a little deeper.

I received the same error complaining about the INSERT statement. The
MsgBox shows the statement ending with "(Archive <= #11/1/2006#);". Are the
"#" suppose to be there?
I press OK and it comes back wit the "Archiving Failed: Error 3134" "Syntac
error in INSERT INTO statement."

I made all of the suggested changed. [Archive] is if Date/Time format short
- no timestamp. The idea is if the date I want to archive this record has
come then archive it.

Thanks for helping me on this. The weight of all of the data is really
hurting the db performance, so I really need this help.

Here is a complete copy of the updated code in case you wanted to see it:
*****************************************************
Private Sub Archival_Click()
'Sub DoArchive()
On Error GoTo Err_DoArchive
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 MyArchiveTable ( MyField, AnotherField, Field3 ) "
& _
' "IN ""C:\My Documents\MyArchive.mdb"" " & _
' "SELECT SomeField, Field2, Field3 FROM MyTable WHERE (MyYesNoField =
True);"

strSql = "INSERT INTO tblCandidatesArchive *"
strSql = strSql & " IN 'C:\Documents and Settings\Administrator\My
Documents\PFS\LIBERATORS\Recruiting\MyCandidateArchiveTable.mdb' "
strSql = strSql & " SELECT * FROM tblCandidatesTST"
strSql = strSql & " WHERE (Archive < = #" & Date & "#);"

MsgBox strSql
'******************

db.Execute strSql, dbFailOnError

'Step 3: Execute the delete.
strSql = "DELETE FROM tblCandidatesTST"
strSql = strSql & " WHERE (Archive < = #" & Date & "#);"
db.Execute strSql, dbFailOnError

'Step 4: Get user confirmation to commit the change.
strMsg = "Archive " & db.RecordsAffected & " record(s)?"
If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then
ws.CommitTrans
bInTrans = False
End If

Exit_DoArchive:
'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_DoArchive:
MsgBox Err.Description, vbExclamation, "Archiving failed: Error " &
Err.NUMBER
Resume Exit_DoArchive
End Sub
*****************************************************

SteveS said:
Rod,

I haven't inserted records into an external database, but I did notice two
things. The external database reference in the INSERT INTO clause should be
enclosed in quotes. In Allen Browne's example, he used double double-quotes
(I tend to use single quotes)

And you can't use "(Archive < = Date);".
If you add a line after the "strSql = " that looks like

Msgbox strSql

, you will see that the WHERE clause is

...."WHERE (Archive < = Date);

when you really want

...."WHERE (Archive < = #10/1/2006#);"

So you should change the WHERE clause to

...."WHERE (Archive < = #" & Date() & "#);"


Try this line:

strSql = "INSERT INTO tblCandidatesArchive *"
strSql = strSql & " IN 'C:\MyCandidateArchiveTable.mdb' "
strSql = strSql & " SELECT * FROM tblCandidatesTST"
strSql = strSql & " WHERE (Archive < = #" & Date() & "#);"


Also, is "MyCandidateArchiveTable.mdb" in C:\ or in MyDocuments or a
different location? Make sure the path is correct.

The delete line should be like:

strSql = "DELETE FROM tblCandidatesTST"
strSql = strSql & " WHERE (Archive < = #" & Date() & "#);"


Is the field [Archive] a Date/Time field with only the Date and not the
Time? It will make a difference on which records are copied and deleted.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Rod said:
I have an "Archival" button which is suppose to archive records from one
table to another (actually move the data). I got the code from:
http://allenbrowne.com/ser-37.html. When I adapted the code for my situation
I received "Syntax error in INSERT INTO statement" (Error 3134). The idea is
[Archive] is the date which a record should be moved. If [Archive] is <=
Date
then it is ready to be moved and should be moved. It doesn't appear to like
the insert statement. I posted this question before but the help dropped.

Any thoughts? Here is my adapted code:

Private Sub Archival_Click()
'Sub DoArchive()
On Error GoTo Err_DoArchive
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 MyArchiveTable ( MyField, AnotherField, Field3 ) "
& _
' "IN ""C:\My Documents\MyArchive.mdb"" " & _
' "SELECT SomeField, Field2, Field3 FROM MyTable WHERE (MyYesNoField =
True);"

strSql = "INSERT INTO tblCandidatesArchive * IN
C:\MyCandidateArchiveTable.mdb SELECT * FROM tblCandidatesTST WHERE (Archive
< = Date);"
'******************

db.Execute strSql, dbFailOnError

'Step 3: Execute the delete.
strSql = "DELETE FROM tblCandidatesTST WHERE (Archive < = Date);"
db.Execute strSql, dbFailOnError

'Step 4: Get user confirmation to commit the change.
strMsg = "Archive " & db.RecordsAffected & " record(s)?"
If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then
ws.CommitTrans
bInTrans = False
End If

Exit_DoArchive:
'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_DoArchive:
MsgBox Err.Description, vbExclamation, "Archiving failed: Error " &
Err.NUMBER
Resume Exit_DoArchive
End Sub
Click to expand...
Click to expand...
 
G

Guest

Rod,
MsgBox shows the statement ending with "(Archive <= #11/1/2006#);". Are the
"#" suppose to be there?
Click to expand...

Yes, dates must be delimied with "#s"

I made a couple of test mdb's and the following code *seems* to work.
(Watch for line wrap)

'******** begin code ***************
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 MyArchiveTable ( MyField, AnotherField, Field3 )
" & _
' "IN ""C:\My Documents\MyArchive.mdb"" " & _
' "SELECT SomeField, Field2, Field3 FROM MyTable WHERE (MyYesNoField
=True);"

strSql = "INSERT INTO tblCandidatesArchive"
strSql = strSql & " IN 'C:\Documents and Settings\Administrator\My
Documents\PFS\LIBERATORS\Recruiting\MyCandidateArchiveTable.mdb' "
strSql = strSql & " SELECT * FROM tblCandidatesTST"
strSql = strSql & " WHERE Archive <= #" & Date & "#;"

'MsgBox strSql
'******************


db.Execute strSql ', dbFailOnError

'Step 3: Execute the delete.
strSql = "DELETE FROM tblCandidatesTST"
strSql = strSql & " WHERE Archive <= #" & Date & "#;"
db.Execute strSql ', dbFailOnError

'Step 4: Get user confirmation to commit the change.
strMsg = "Archive " & db.RecordsAffected & " record(s)?"
If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then
ws.CommitTrans
bInTrans = False
End If

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: Error # " & Err.Number & " Desc: " &
Err.Description, vbExclamation
ws.Rollback
Resume Exit_Archival

End Sub
'**********end code*****************
 
G

Guest

EXCELLENT! YOU GOT IT!

You figured it out when others gave up!

Thank You SOOOO MUCH!
 

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

Archive 3
What's wrong w/ this SQL stmt? 1
Modify sql statement 2
Moving Info 6
Syntax (Missing Operator) in Query Expr .... 2
SQL Statements does not Work 2
Transactions failing in Loop 2
Filtered Records as source for appending records to a table 1

Top