SQL Statement: INSERT & DELETE

[marcellvries]

Hi there,

I'm developing a database (planning system) and I'm facing the following problem.

When I click on a button on the form, I would like to add all records from tblPlanning with Confirm = true to tblRealisatie and subsequently remove those records from tblPlanning. So in fact I would like to move records from tblPlanning to tblRealisatie.

I just can't get the insert and delete parts to work at the same time. What do I do wrong? The code is below.

With kind regards,

Marcell

-------


Private Sub cmdRealiseren_Click()

Dim rsRealiseren As ADODB.Recordset
Dim rsDelete As ADODB.Recordset

Dim sqlRealiseren As String
Dim sqlDelete As String

Set rsRealiseren = New ADODB.Recordset
Set rsDelete = New ADODB.Recordset

sqlRealiseren = "INSERT INTO tblRealisatie " _
& "SELECT * FROM tblPlanning " _
& "WHERE Confirm = True;"
sqlDelete = "DELETE * FROM tblPlanning WHERE Confirm = True;"

rsRealiseren.ActiveConnection = CurrentProject.Connection
rsRealiseren.Source = sqlRealiseren
rsRealiseren.CursorType = adOpenDynamic
rsRealiseren.LockType = adLockOptimistic
rsRealiseren.Open

Set rsRealiseren = Nothing

rsDelete.ActiveConnection = CurrentProject.Connection
rsDelete.Source = sqlDelete
rsDelete.CursorType = adOpenDynamic
rsDelete.LockType = adLockOptimistic
rsDelete.Open

Set rsDelete = Nothing

Forms!frmPlanningRealiseren.Requery
End Sub

 

[HarleyQuinn]

Seem's you're using ADO, you can use a Connection object and can run queries from it without the need for Recordsets.

How about something a bit shorter:

Private Sub cmdRealiseren_Click()
Dim cn As ADODB.Connection

Dim sqlRealiseren As String
Dim sqlDelete As String

Set cn = New ADODB.Connection

sqlRealiseren = "INSERT INTO tblRealisatie " _
                & "SELECT * FROM tblPlanning " _
                & "WHERE Confirm = True;"
sqlDelete = "DELETE * FROM tblPlanning WHERE Confirm = True;"

cn.ConnectionString = "YourConnectionString"
cn.CursorType = adOpenDynamic
cn.Open

cn.Execute (sqlRealiseren)
cn.Execute (sqlDelete)

cn.Close

Set cn = Nothing

Forms!frmPlanningRealiseren.Requery
End Sub

This is just knocked up off the top of my head so might not run first time but should give you an idea of how to use an ADODB.Connection.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PHV]

Why not simply this ?

sqlRealiseren = "INSERT INTO tblRealisatie " _
                & "SELECT * FROM tblPlanning " _
                & "WHERE Confirm = True;"
sqlDelete = "DELETE * FROM tblPlanning WHERE Confirm = True;"
CurrentDb.Execute sqlRealiseren
DoEvents
CurrentDb.Execute sqlDelete

BTW, tblRealisatie and tblPlanning should have the very same schema.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HarleyQuinn]

Yeah, native DAO might be a better way to go, but suppose at least you'll know how to do both DAO and ADO for future reference now

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[marcellvries]

Hi there,

Thank you both for your replies. The first solution I cannot get to work. I'm now trying the second solution, will let you know in a few mins.

I must commit that I'm a newbie with regard to access, sql and vba so I'm having quite a hard time

With kind regards,

Marcell

 

[marcellvries]

Okay well I tried the following code, but yet it isn't working as it should. Both tables are exactly the same.

Problem 1: When I click the button there are records removed from tblPlanning, but nothing is added to tblRealisatie. When I disable the sqldelete part, records are added to tblRealisatie.

Problem 2: When I select multiple records (by checking 'Bevestigen', and then click on the button, sometimes nothing happens, sometimes only 1 record is removed (or added to other table), sometimes all records are deleted. So I need to click multiple times.

Private Sub cmdRealiseren_Click()

Dim sqlRealiseren As String
Dim sqlDelete As String

sqlRealiseren = "INSERT INTO tblRealisatie " _
                & "SELECT * FROM tblPlanning " _
                & "WHERE Bevestigen = True; "
sqlDelete = "DELETE * FROM tblPlanning WHERE Bevestigen = True;"

CurrentDb.Execute sqlRealiseren
DoEvents
CurrentDb.Execute sqlDelete
DoEvents
End Sub

 

[TheAceMan1]

marcellvries . . .

[blue]Private Sub cmdRealiseren_Click()   
   Dim sqlRealiseren As String
   Dim sqlDelete As String
   [purple][b]Dim Db as DAO.database

   Set Db = CurrentDB[/b][/purple]

   [green]'The rest of the code[/green][/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[marcellvries]

Hi, thank you, but I still got the same problems as stated in my previous reply...

Gr. marcell

 

[JoeAtWork]

I think the DoEvents are messing you up. Take them out.

 

[Golom]

Might be a buffer thing ... and raise some errors to see what's happening

Private Sub cmdRealiseren_Click()

Dim sqlRealiseren As String
Dim sqlDelete     As String

sqlRealiseren = "INSERT INTO tblRealisatie " _
                & "SELECT * FROM tblPlanning " _
                & "WHERE Bevestigen = True; "

sqlDelete = "DELETE * FROM tblPlanning WHERE Bevestigen = True;"

On Error Resume Next
CurrentDb.Execute sqlRealiseren[red], dbFailonError[/red]
Debug.Print CurrentDb.RecordsAffected
If Err.Number <> 0 Then
   MsgBox "INSERT FAILED" & vbCrLf & _
          Err.Number & " - " & Err.Description
   Err.Clear
End If
DBEngine.Idle dbRefreshCache

CurrentDb.Execute sqlDelete[red], dbFailonError[/red]
Debug.Print CurrentDb.RecordsAffected
If Err.Number <> 0 Then
   MsgBox "DELETE FAILED" & vbCrLf & _
          Err.Number & " - " & Err.Description
   Err.Clear
End If
DBEngine.Idle dbRefreshCache

End Sub

 

[marcellvries]

Thank you for your replies JoeatWork and Golom.

JoeatWork: removing DoEvents doesn't change anything.

Golom, with the code you provided no errors come up but it just doesn't work as it should.

The sqlrealiseren part only works when I disable the sqldelete part (by putting ' before each line). Also, the results of a single mouseclick are unpredictable. With the first click nothing happens. After that, sometimes no records are processed and sometimes only one record is copied/deleted.

It might be that I started all wrong and that there is an easier solution so let me explain what I would like to do. I'm designing a planning system for a security company I work for. On a specific form you can enter the location, person, date, starting time, and end time (tblPlanning).

Afterwards you should be able to view the planning, make any corrections (someone might be replaced, the times may have changed etc.) and finally confirm so that you can see the real hours worked.

I thought that the easiest way to confirm was to add a checkbox (chkBevestigen) on the form (linked to yes/no field in tblPlanning) and a command button. When clicking, the records with 'Bevestigen = True' would be copied to tblRealiseren, and removed from tblPlanning.

Hopefully this will clarify things a bit.

With kind regards,

Marcell

 

[Golom]

Add a bit of code

Private Sub cmdRealiseren_Click()

    Dim sqlRealiseren               As String
    Dim sqlDelete                   As String
    Dim rs                          As DAO.Recordset
    Dim Ans                         As Integer

    sqlRealiseren = "INSERT INTO tblRealisatie " _
                    & "SELECT * FROM tblPlanning " _
                    & "WHERE Bevestigen = True; "

    sqlDelete = "DELETE * FROM tblPlanning WHERE Bevestigen = True;"

    Set rs = CurrentDb.OpenRecordset("Select Count(*) As [Recs] FROM tblPlanning WHERE Bevestigen = True")

    Ans = vbNo
    If rs![Recs] = 0 Then
        MsgBox "No Records To Copy or Delete", vbInformation, "No Records"
    Else
        Ans = MsgBox(rs![Recs] & " records will be copied & deleted" & vbCrLf & vbCrLf & _
                     "Is that OK?", vbQuestion + vbYesNo, "Copy & Delete?")
    End If
    rs.Close
    Set rs = Nothing

    If Ans = vbYes Then
        On Error Resume Next
        CurrentDb.Execute sqlRealiseren, dbFailOnError
        Debug.Print CurrentDb.RecordsAffected
        If Err.Number <> 0 Then
            MsgBox "INSERT FAILED" & vbCrLf & _
                   Err.Number & " - " & Err.Description
            Err.Clear
        End If
        DBEngine.Idle dbRefreshCache

        CurrentDb.Execute sqlDelete, dbFailOnError
        Debug.Print CurrentDb.RecordsAffected
        If Err.Number <> 0 Then
            MsgBox "DELETE FAILED" & vbCrLf & _
                   Err.Number & " - " & Err.Description
            Err.Clear
        End If
        DBEngine.Idle dbRefreshCache
    End If
End Sub

If the message box pops up with "No Records To Copy or Delete" then this routine is working properly. It is not failing to copy and delete ... it just doesn't have anything that qualifies and you need to look elsewhere for the problem.

 

[marcellvries]

Hi Golom,

Thank you very much! First it didn't work but then I removed the referential integrity between tblPlanning and tblRealisatie (don't ask why I enabled that in the first place ) and now it's working fine.

I hope someday I will understand VBA the way you do.

With kind regards,

Marcell