Archive

G

Guest

I have an "Archival" button which is suppose to archive records from one
tablt 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.

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 ( NUMBER, FOLLOWUP_NUMBER,
CANDIDATE, CALLED_ON, NUm_of_Calls, Prior_Call, CALL_RESULTS, PRIORITY,
CONFIRMATION_DATE, DATE_CONFIRMED, CORP_OVERVIEW, Booked, RETURNED_CALL,
NO_SHOW_FOLLOW-UP, COMMENTS, CO RESULTS, SOURCE, Follow-up_Needed, Date of
1st No Show Msg, MANAGER, First_Call, Final_Call, Archive ) IN
C:\MyCandidateArchiveTable.mdb SELECT NUMBER, FOLLOWUP_NUMBER, CANDIDATE,
CALLED_ON, NUm_of_Calls, Prior_Call, CALL_RESULTS, PRIORITY,
CONFIRMATION_DATE, DATE_CONFIRMED, CORP_OVERVIEW, Booked, RETURNED_CALL,
NO_SHOW_FOLLOW-UP, COMMENTS, CO RESULTS, SOURCE, Follow-up_Needed, Date of
1st No Show Msg, MANAGER, First_Call, Final_Call, Archive 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
 
J

J. Goddard

Hi -

Any field with spaces in the name must be enclosed in square brackets,
i.e. [Date of 1st No Show Msg].

If you are copying all the fields from one table to the archive table,
and the structures are the same (field names the same and in the same
order), you don't need the long field lists:

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

should work

HTH

John

I have an "Archival" button which is suppose to archive records from one
tablt 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.

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 ( NUMBER, FOLLOWUP_NUMBER,
CANDIDATE, CALLED_ON, NUm_of_Calls, Prior_Call, CALL_RESULTS, PRIORITY,
CONFIRMATION_DATE, DATE_CONFIRMED, CORP_OVERVIEW, Booked, RETURNED_CALL,
NO_SHOW_FOLLOW-UP, COMMENTS, CO RESULTS, SOURCE, Follow-up_Needed, Date of
1st No Show Msg, MANAGER, First_Call, Final_Call, Archive ) IN
C:\MyCandidateArchiveTable.mdb SELECT NUMBER, FOLLOWUP_NUMBER, CANDIDATE,
CALLED_ON, NUm_of_Calls, Prior_Call, CALL_RESULTS, PRIORITY,
CONFIRMATION_DATE, DATE_CONFIRMED, CORP_OVERVIEW, Booked, RETURNED_CALL,
NO_SHOW_FOLLOW-UP, COMMENTS, CO RESULTS, SOURCE, Follow-up_Needed, Date of
1st No Show Msg, MANAGER, First_Call, Final_Call, Archive 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

Same error. Here is the updated code segment:
strSql = "INSERT INTO tblCandidatesArchive IN
C:\MyCandidateArchiveTable.mdb SELECT * FROM tblCandidatesTST WHERE (Archive
< = Date);"



J. Goddard said:
Hi -

Any field with spaces in the name must be enclosed in square brackets,
i.e. [Date of 1st No Show Msg].

If you are copying all the fields from one table to the archive table,
and the structures are the same (field names the same and in the same
order), you don't need the long field lists:

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

should work

HTH

John

I have an "Archival" button which is suppose to archive records from one
tablt 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.

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 ( NUMBER, FOLLOWUP_NUMBER,
CANDIDATE, CALLED_ON, NUm_of_Calls, Prior_Call, CALL_RESULTS, PRIORITY,
CONFIRMATION_DATE, DATE_CONFIRMED, CORP_OVERVIEW, Booked, RETURNED_CALL,
NO_SHOW_FOLLOW-UP, COMMENTS, CO RESULTS, SOURCE, Follow-up_Needed, Date of
1st No Show Msg, MANAGER, First_Call, Final_Call, Archive ) IN
C:\MyCandidateArchiveTable.mdb SELECT NUMBER, FOLLOWUP_NUMBER, CANDIDATE,
CALLED_ON, NUm_of_Calls, Prior_Call, CALL_RESULTS, PRIORITY,
CONFIRMATION_DATE, DATE_CONFIRMED, CORP_OVERVIEW, Booked, RETURNED_CALL,
NO_SHOW_FOLLOW-UP, COMMENTS, CO RESULTS, SOURCE, Follow-up_Needed, Date of
1st No Show Msg, MANAGER, First_Call, Final_Call, Archive 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

Same error. Here is the updated code segment:
strSql = "INSERT INTO tblCandidatesArchive IN
C:\MyCandidateArchiveTable.mdb SELECT * FROM tblCandidatesTST WHERE (Archive
< = Date);"


J. Goddard said:
Hi -

Any field with spaces in the name must be enclosed in square brackets,
i.e. [Date of 1st No Show Msg].

If you are copying all the fields from one table to the archive table,
and the structures are the same (field names the same and in the same
order), you don't need the long field lists:

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

should work

HTH

John

I have an "Archival" button which is suppose to archive records from one
tablt 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.

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 ( NUMBER, FOLLOWUP_NUMBER,
CANDIDATE, CALLED_ON, NUm_of_Calls, Prior_Call, CALL_RESULTS, PRIORITY,
CONFIRMATION_DATE, DATE_CONFIRMED, CORP_OVERVIEW, Booked, RETURNED_CALL,
NO_SHOW_FOLLOW-UP, COMMENTS, CO RESULTS, SOURCE, Follow-up_Needed, Date of
1st No Show Msg, MANAGER, First_Call, Final_Call, Archive ) IN
C:\MyCandidateArchiveTable.mdb SELECT NUMBER, FOLLOWUP_NUMBER, CANDIDATE,
CALLED_ON, NUm_of_Calls, Prior_Call, CALL_RESULTS, PRIORITY,
CONFIRMATION_DATE, DATE_CONFIRMED, CORP_OVERVIEW, Booked, RETURNED_CALL,
NO_SHOW_FOLLOW-UP, COMMENTS, CO RESULTS, SOURCE, Follow-up_Needed, Date of
1st No Show Msg, MANAGER, First_Call, Final_Call, Archive 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...
 

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

Archival 5
What's wrong w/ this SQL stmt? 1
Modify sql statement 2
Can You Figure Out What's Wrong W/ This Query 4
SQL Statements does not Work 2

Top