SQL Statements does not Work


J

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
*******************************************************************************************************************************
 
Ad

Advertisements

S

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?
 
Ad

Advertisements

J

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.
 

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

Top