Solved Import and Append to Existing Table (1 Viewer)

pooldead

Registered User.
Local time
Today, 02:38
Joined
Sep 4, 2019
Messages
136
I have a table that is populated with users and their associated roles to certain applications. I receive spreadsheets from their managers with either an Approve or Deny. The spreadsheet fields match the table exactly. My question is: how can import the responses to existing user field instead of creating a new record each time?

Basically, all I need to import is the Approve/Deny into the user's existing record.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:38
Joined
Oct 29, 2018
Messages
21,357
Hi. Does your table and the spreadsheet have a unique value field that you can use to link the two records together? If so, you would want to use an UPDATE query, instead of an APPEND.
 

pooldead

Registered User.
Local time
Today, 02:38
Joined
Sep 4, 2019
Messages
136
Hi. Does your table and the spreadsheet have a unique value field that you can use to link the two records together? If so, you would want to use an UPDATE query, instead of an APPEND.

Yes, they both share a field called "Username". I'll try Update instead.
 

pooldead

Registered User.
Local time
Today, 02:38
Joined
Sep 4, 2019
Messages
136
Code:
                    sqlStr = "SELECT table1.[USERNAME], table1.Roles, table1.[APPROVE/DENY] " & _
                             "FROM table1;"
                    Set rs1 = CurrentDb.OpenRecordset(sqlStr)
                        rs1.MoveLast
                        rs1.MoveFirst
                        Do While Not rs1.EOF
                            sqlStr = "UPDATE Table2 " & _
                                     "SET Table2.[APPROVE/DENY] = '" & rs1.Fields("APPROVE/DENY") & "'" & _
                                     "WHERE (([Table2].[USERNAME]='" & rs1.Fields("USERNAME") & "') AND ([Table2].Roles = '" & rs1.Fields("Roles") & "'));"
                            DoCmd.RunSQL (sqlStr)
                            rs1.MoveNext
                        Loop

I pasted the code above should anyone come across this in the future. I imported to a temp table, then did a Select query to a recordset in order to run the Update. Worked like a charm!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:38
Joined
Oct 29, 2018
Messages
21,357
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom