SQL Statements does not Work

[jeanhurtado]

Hi, I;m Jean and well I have the following code to make a backup. The
problem that I have is the the "strSql" are not executing. I have run
each statement one at a time but no work and no errors. Can you help
me determine why the SQL statements does not work on my database in
Access. Thanks you so much for effort to help me. Have anice day.

********************************************************************************************************************

Private Sub cmdbackup_Click()

On Error GoTo Err_cmdbackup

Dim db As DAO.Database 'Inside the transaction.
Dim strSql As String 'Action query statements.
Dim strMsg As String 'MsgBox message.
Dim confirmation As String 'MsgBox shows records transferred and
date.

'Step 1: Initialize database object inside a transaction.
Set db = CurrentDb

'Step 2: Execute the delete.
strSql = "DELETE * FROM BACKUPCOSTTABLE"
db.Execute strSql, dbFailOnError

'Step 3: Execute the append.
strSql = "INSERT INTO BACKUPCOSTTABLE ([Part Number], LAB1000,
MAT1000) SELECT * FROM COSTTABLE"
db.Execute strSql, dbFailOnError

'Step 4: Execute the delete.
strSql = "DELETE FROM COSTTABLE"
db.Execute strSql, dbFailOnError


'Step 5: Execute Spreadsheet Transfer
'Code to be run if the transfer in Excel gets corrupted or if there
is an error roll back to its original state
BACKUPERROR

'Step 6: Get user confirmation to commit the change.
strMsg = "Archive " & db.RecordsAffected & " record(s)?" & Date
confirmation = MsgBox(strMsg, vbOKOnly + vbInformation, "Successful
Transfer")


Exit_cmdbackup:
'Step 7: Clean up
On Error Resume Next
Exit Sub

Err_cmdbackup:
MsgBox Err.Description, vbExclamation, "Archiving failed: Error " &
Err.Number
Resume Exit_cmdbackup



End Sub
*******************************************************************************************************************************

 

[Smartin]

Hi, I;m Jean and well I have the following code to make a backup. The
problem that I have is the the "strSql" are not executing. I have run
each statement one at a time but no work and no errors. Can you help
me determine why the SQL statements does not work on my database in
Access. Thanks you so much for effort to help me. Have anice day.

********************************************************************************************************************

Private Sub cmdbackup_Click()

On Error GoTo Err_cmdbackup

Dim db As DAO.Database 'Inside the transaction.
Dim strSql As String 'Action query statements.
Dim strMsg As String 'MsgBox message.
Dim confirmation As String 'MsgBox shows records transferred and
date.

'Step 1: Initialize database object inside a transaction.
Set db = CurrentDb

'Step 2: Execute the delete.
strSql = "DELETE * FROM BACKUPCOSTTABLE"
db.Execute strSql, dbFailOnError

'Step 3: Execute the append.
strSql = "INSERT INTO BACKUPCOSTTABLE ([Part Number], LAB1000,
MAT1000) SELECT * FROM COSTTABLE"
db.Execute strSql, dbFailOnError

'Step 4: Execute the delete.
strSql = "DELETE FROM COSTTABLE"
db.Execute strSql, dbFailOnError


'Step 5: Execute Spreadsheet Transfer
'Code to be run if the transfer in Excel gets corrupted or if there
is an error roll back to its original state
BACKUPERROR

'Step 6: Get user confirmation to commit the change.
strMsg = "Archive " & db.RecordsAffected & " record(s)?" & Date
confirmation = MsgBox(strMsg, vbOKOnly + vbInformation, "Successful
Transfer")


Exit_cmdbackup:
'Step 7: Clean up
On Error Resume Next
Exit Sub

Err_cmdbackup:
MsgBox Err.Description, vbExclamation, "Archiving failed: Error " &
Err.Number
Resume Exit_cmdbackup



End Sub
*******************************************************************************************************************************

Hallo,

When you execute one statement at a time, check to see if the results
are as you expect at each step. Where does your code break down?

 

[jeanhurtado]

Hi, I;m Jean and well I have the following code to make a backup. The
problem that I have is the the "strSql" are not executing. I have run
each statement one at a time but no work and no errors. Can you help
me determine why the SQL statements does not work on my database in
Access. Thanks you so much for effort to help me. Have anice day.

Private Sub cmdbackup_Click()

On Error GoTo Err_cmdbackup

Dim db As DAO.Database 'Inside the transaction.
Dim strSql As String 'Action query statements.
Dim strMsg As String 'MsgBox message.
Dim confirmation As String 'MsgBox shows records transferred and
date.

'Step 1: Initialize database object inside a transaction.
Set db = CurrentDb

'Step 2: Execute the delete.
strSql = "DELETE * FROM BACKUPCOSTTABLE"
db.Execute strSql, dbFailOnError

'Step 3: Execute the append.
strSql = "INSERT INTO BACKUPCOSTTABLE ([Part Number], LAB1000,
MAT1000) SELECT * FROM COSTTABLE"
db.Execute strSql, dbFailOnError

'Step 4: Execute the delete.
strSql = "DELETE FROM COSTTABLE"
db.Execute strSql, dbFailOnError

'Step 5: Execute Spreadsheet Transfer
'Code to be run if the transfer in Excel gets corrupted or if there
is an error roll back to its original state
BACKUPERROR

'Step 6: Get user confirmation to commit the change.
strMsg = "Archive " & db.RecordsAffected & " record(s)?" & Date
confirmation = MsgBox(strMsg, vbOKOnly + vbInformation, "Successful
Transfer")

Exit_cmdbackup:
'Step 7: Clean up
On Error Resume Next
Exit Sub

Err_cmdbackup:
MsgBox Err.Description, vbExclamation, "Archiving failed: Error " &
Err.Number
Resume Exit_cmdbackup

End Sub
***************************************************************************­****************************************************

Hallo,

When you execute one statement at a time, check to see if the results
are as you expect at each step. Where does your code break down?

I have close Access and open again and it works I don't really know
what happen but it works. Thanks Smartin for your help.