VBA to Append With Primary Key

[graviz]

I currently have this code to spit out some data into a table, however I would like to tweak it to be more of an append query. I know I need to use "INSERT INTO" somehow based on searching, but I also want to ensure the table doesn't get duplicate records in it from the previous code. Manually adding a primary key isn't an option as the table may be deleted. Any suggestions on how I can tweak the code below is appreciated.

Public Function Output_RFC_Tables_Test()
Dim Conn As ADODB.Connection
Dim RS As New ADODB.Recordset
Dim MySQL As String
Dim ArrayRS As ADODB.Recordset
Set ArrayRS = New ADODB.Recordset
Set Conn = CurrentProject.Connection
RS.ActiveConnection = Conn
ArrayRS.ActiveConnection = CurrentProject.Connection
ArrayRS.Open ("REF_ARRAY")
While ArrayRS.EOF = False
    If ArrayRS.Fields.Item(2).Value <> "12/25/2025" Then
        MySQL = "SELECT RFC_STAGING_3.RFC, RFC_STAGING_3.RFC_TITLE, RFC_STAGING_3.RFC_STATE, RFC_STAGING_3.RFC_OBJECTIVE, RFC_STAGING_3.PM, RFC_STAGING_3.BUS_PORTFOLIO, RFC_STAGING_3.BRM, RFC_STAGING_3.Impacted_Teams, RFC_STAGING_3.Notes" & " " & _
        "INTO " & ArrayRS.Fields.Item(0).Value & " " & _
        "FROM RFC_STAGING_3" & " " & _
        "WHERE (((RFC_STAGING_3.DATE_TARGET_AFB) = #" & ArrayRS.Fields.Item(2).Value & "#) And ((RFC_STAGING_3.CCA_Project) = 1))" & " " & _
        "GROUP BY RFC_STAGING_3.RFC, RFC_STAGING_3.RFC_TITLE, RFC_STAGING_3.RFC_STATE, RFC_STAGING_3.RFC_OBJECTIVE, RFC_STAGING_3.PM, RFC_STAGING_3.BUS_PORTFOLIO, RFC_STAGING_3.BRM, RFC_STAGING_3.Impacted_Teams, RFC_STAGING_3.Notes" & " " & _
        "ORDER BY RFC_STAGING_3.PM;"
        
        RS.Open MySQL
        
        ArrayRS.MoveNext
    
    Else
    
        ArrayRS.MoveNext
    
    End If
Wend
Set Conn = Nothing
End Function

 

[TJPoorman]

Look at this link for the proper syntax for INSERT INTO SELECT.
Then to execute the SQL from your VBA would be something like this:

Public Function InsertIt()
Dim strSQL As String
Dim db as DAO.Database

strSQL = "INSERT INTO MyTable SELECT * FROM MyOtherTable"
Set db = CurrentDb

db.Execute(strSQL)

Set db = Nothing
End Function

 

[graviz]

Look at this link for the proper syntax for INSERT INTO SELECT.
Then to execute the SQL from your VBA would be something like this:

Public Function InsertIt()
Dim strSQL As String
Dim db as DAO.Database
 
strSQL = "INSERT INTO MyTable SELECT * FROM MyOtherTable"
Set db = CurrentDb
 
db.Execute(strSQL)
 
Set db = Nothing
End Function

So that's what I was trying but I didn't want to create duplicate records. Here's my code that works but it's creating dups:

Public Function Output_RFC_Tables_Test()
Dim Conn As ADODB.Connection
Dim RS As New ADODB.Recordset
Dim MySQL As String
Dim ArrayRS As ADODB.Recordset
Set ArrayRS = New ADODB.Recordset
Set Conn = CurrentProject.Connection
RS.ActiveConnection = Conn
ArrayRS.ActiveConnection = CurrentProject.Connection
ArrayRS.Open ("REF_ARRAY")
While ArrayRS.EOF = False
    If ArrayRS.Fields.Item(2).Value <> "12/25/2025" Then
        MySQL = "INSERT INTO " & ArrayRS.Fields.Item(0).Value & " " & _
        "SELECT RFC_STAGING_3.RFC, RFC_STAGING_3.RFC_TITLE, RFC_STAGING_3.RFC_STATE, RFC_STAGING_3.RFC_OBJECTIVE, RFC_STAGING_3.PM, RFC_STAGING_3.BUS_PORTFOLIO, RFC_STAGING_3.BRM, RFC_STAGING_3.Impacted_Teams, RFC_STAGING_3.Notes" & " " & _
        "FROM RFC_STAGING_3" & " " & _
        "WHERE (((RFC_STAGING_3.DATE_DEPLOY_1) = #" & ArrayRS.Fields.Item(2).Value & "#) And ((RFC_STAGING_3.CCA_Project) = 1))" & " " & _
        "GROUP BY RFC_STAGING_3.RFC, RFC_STAGING_3.RFC_TITLE, RFC_STAGING_3.RFC_STATE, RFC_STAGING_3.RFC_OBJECTIVE, RFC_STAGING_3.PM, RFC_STAGING_3.BUS_PORTFOLIO, RFC_STAGING_3.BRM, RFC_STAGING_3.Impacted_Teams, RFC_STAGING_3.Notes" & " " & _
        "ORDER BY RFC_STAGING_3.PM;"
        
        RS.Open MySQL
        
        ArrayRS.MoveNext
    
    Else
    
        ArrayRS.MoveNext
    
    End If
Wend
Set Conn = Nothing
End Function

I run the first function in this thread and then run this one however I don't want duplicates added to this table. Let me know if that makes more sense.

 

[TJPoorman]

Do you not have a Primary Key field in your tables?
This sounds like a design issue.

 

[graviz]

How about if you create a unique index for the fields you don't want duplicated. That should prevent any duplicates being added.

If the table gets deleted each time how would I do that. Basically what I'm saying is I can't go into the design of the table and set one to a primary key. There is a unique field if there's a way to do it through code.