MS Access copy table to another table VBA code

Question

I have a problem I need to create vba code for ms access 2016 which will copy the contents of table 1 to table 2 with

If there are duplicates in Table 2, it displays a message about the duplicates and a question whether to continue or abort.

I have this vba code but I don't know how to convert it to work the way I want I got this error on image

My error

Private Sub txtVWI_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_txtVWI_BeforeUpdate
Dim intResponse As Integer
Dim strTable As String
Dim strFind As String
Dim strSQL As String
Dim rst As ADODB.Recordset
Dim Conn As ADODB.Connection
Set Conn = CurrentProject.Connection
Set rst = New ADODB.Recordset
strTable = "Główna"
strSQL = "SELECT Count(Główna.Data/Godzina) AS Duplikaty" & _ " FROM Główna" & _ " GROUP BY Główna.Data/Godzina" & _ " HAVING Count(Główna.Data/Godzina)>1"
Set rs = db.OpenRecordset(strSQL) 
If rs.RecordCount = 0 Then strSQL = "INSERT INTO Główna" & _     " SELECT Tymczasowa.*" & _     " FROM Tymczasowa"     DoCmd.RunSQL (strSQL)
rst.Open strSQL, Conn
If rst(0) > 0 Then ' duplikaty znalezione.
    If Me.NewRecord Then
        intResponse = MsgBox("Ten zestaw danych już istnieje" & vbCrLf & "chcesz zduplikować zestaw danych?", vbYesNo)
            If intResponse = vbNo Then
                Me.Undo
            End If
    End If
End If
Exit_txtVWI_BeforeUpdate:
    Exit Sub

        rst.Close
        Conn.Close
        Set rst = Nothing
        Set Conn = Nothing
Err_txtVWI_BeforeUpdate:
    MsgBox Err.Description
    Resume Exit_txtVWI_BeforeUpdate
End Sub

You use Count, it looks like you get 1 record even if the count is 0. — user3819867, Sep 30 '19 at 12:19
Should post error message as text in question, not an image. — June7, Sep 30 '19 at 18:12
The 2 rs recordset lines in question are not in image code - why? — June7, Sep 30 '19 at 18:58

June7 · Answer 1 · 2019-09-30T19:47:16.740

If you are going to use concatenation and line continuation character then should actually continue on next line.

Table name has special character. Use [] to delimit. Can simplify SQL statement.

Not seeing db variable declared and set. Don't really need it for one-time use. Should declare rs variable.

Dim rs As DAO.Recordset
strSQL = "SELECT Count(*) AS Duplikaty" & _ 
          " FROM Główna" & _ 
          " GROUP BY [Data/Godzina]" & _ 
          " HAVING Count(*)>1"
Set rs = CurrentDb.OpenRecordset(strSQL) 
If rs.RecordCount = 0 Then strSQL = "INSERT INTO Główna" & _
         " SELECT Tymczasowa.*" & _
         " FROM Tymczasowa"

Advise not to use punctuation/special characters in naming convention.

MS Access copy table to another table VBA code

1 Answers1