Unanswered: How can I make a simple "copy/paste" with replacement query?
I'm working on an Access application, and due to the specific requirements of the form I want to make, I need to have a temporary table bound to the form. This temporary table will hold certain values grabbed from a permanent table linked to the SQL Server, save the modifications, and when I am done with it I need to put the values back into the permanent table. However, I can't just do a simple INSERT command because I don't want any repeats (there are two fields in all these tables which act as primary keys).
So, my question is: how can I make an SQL query which will have the same effect as a copy/paste with replacement operation in an OS? If an entry exists, I want to write the new values into it from the temporary table, and if an entry does not exist, I want to create a new entry for it.
Right now, I have something like this:
To copy the values from the permanent table (tblPermanent) to the temporary table (tblTemp, which is blank to begin with)
INSERT INTO tblTemp (field1,field2,field3...) SELECT field1,field2,field3... FROM tblPermanent WHERE field1 = value;
To insert the values back into the permanent table with replacement:
INSERT INTO tblPermanent (field1,field2,field3...) (SELECT field1,field2,field3... FROM tblTemp WHERE primarykey1 = value) ON DUPLICATE KEY UPDATE field1=tblTemp.field1 field2=tblTemp.field2 field3=tblTemp.field3 ... ;
I used that "ON DUPLICATE KEY" term because I could only find the MySQL syntax for how to do this with a Google search. Also, I'm dealing with something like 10+ fields here, but I'm not sure if I can just use the * term to refer to all of them.
I'm a total noob when it comes to databases and VBA programming (curse you, Microsoft, and your damn editor which is unable to respond to my scroll wheel!). Does anyone knows of a good reference book for VBA programming in Access for a fairly experienced programmer who is completely new to database programming and needs to accomplish a few specific tasks creating forms to manage the pre-existing tables in an MS SQL database? How about a comprehensive online repository of Visual Basic for Applications (the MSDN site is hardly comprehensive)? Thanks for your help.
You know this is a bad idea in a multi-user environment, right? You are bound to have people overwriting eachother's data because you are bypassing database locking.
Regardless, you will need to run two statements. The first should update any existing records with changes based upon linking the tables through the primary key. The second will insert new records where the primary key does not already exist.
If it's not practically useful, then it's practically useless.
Since your question is about Microsoft Access, I'm going to move this message thread to the Microsoft Access forum. I'd bet that you'll get a lot more useful answers there!
As a general rule when I'm using Access, I set optimistic locking and build a data set that contains all of the key columns for the table that I'm updating. Access does the rest "behind the scenes" for me. One word of warning about this, it is not terribly efficient... I'd give serious thought to using it in high volume production environments.