By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,389 Members | 1,989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,389 IT Pros & Developers. It's quick & easy.

How to overwrite an old record when using INSERT INTO statement?

P: 68
My Access DB handles product formulation and specification. In the formula form, many text boxes, such as Cost or ingredient statement, are calculated fields based on each ingredient data in the formula.

Once a formula is complete, it becomes an ingredient. The new ingredient ID and Version in the ingredient table (table name TProduct) is supposed to inherit the formula ID and Version. I built a command button to copy ID, Version, cost, and other information from Formula form to Ingredient form using INSERT INTO statement.

However, I am stuck with one issue. If the record already exists, INSERT INTO statement does not update new information in the existing record. User will not notice that the record in Ingredient Table is not updated.

I don’t know how to modify the code to (1) warn user that same ID and Version exists in Ingredient table; (2) delete the existing ingredient record; (3) create a new record with updated information from formula table.

Below is my current INSERT INTO code. Is there a simple way to handle this? Thanks.

Expand|Select|Wrap|Line Numbers
  1.  Dim dbs As Database
  2. Set dbs = CurrentDb
  3.  
  4. Dim strRID As String
  5. Dim strRver As String
  6. Dim strCost As Currency
  7. Dim strStatement As String
  8.  
  9. strRID = Me.[Txt_ID]
  10. strRver = Me.[txt_Version]
  11. strCost = Me.[txt_Total_Cost]
  12. strStatement = Me.[txt_RecipeStatement]
  13.  
  14. dbs.Execute " INSERT INTO TProduct " _
  15. & "(productID, ProductVersion, UnitCost, spec, vendorID, VendorLocation) VALUES " _
  16. & "('" & strRID & "' , '" & strRver & "', '" & strCost & "', '" & strStatement & "', '10000', 'NYC');"
  17.  
  18. dbs.Close 
Apr 15 '12 #1

✓ answered by mshmyob

Unfortunatley Access does not have something like "INSERT OR IGNORE" like SQLite but what you could do is

1) do a DLOOKUP based on the PK
2) If the DLOOKUP returns NULL (ie: not found) the do step 3 else do step 4
3) INSERT statement
4) UPDATE statement

cheers,

Share this Question
Share on Google+
10 Replies


mshmyob
Expert 100+
P: 903
Unfortunatley Access does not have something like "INSERT OR IGNORE" like SQLite but what you could do is

1) do a DLOOKUP based on the PK
2) If the DLOOKUP returns NULL (ie: not found) the do step 3 else do step 4
3) INSERT statement
4) UPDATE statement

cheers,
Apr 15 '12 #2

nico5038
Expert 2.5K+
P: 3,072
When you replace the existing record with the INSERT record, you could first issue a DELETE * FROM tblX x WHERE EXISTS (select ID from tblY y where x.id = y.id)
This will remove the dupes and the INSERT will add them with the most recent information.
Apr 15 '12 #3

P: 68
Although Nico5038's method looks easier, I have already got it done using mshmyob's method. Both methods are valuable lesson for my vba journal.

Thanks for both of you.

Joe
Apr 16 '12 #4

NeoPa
Expert Mod 15k+
P: 31,494
Actually, if you use an UPDATE query, it will add any records that don't already exist anyway.
Apr 17 '12 #5

100+
P: 332
@NeoPa
I remember I read something about this that you mentionned somewhere else and indeed that works (I think it was a link with example you gave). But in the SQL statement, you have to indicate a JOIN clause.
Apr 18 '12 #6

NeoPa
Expert Mod 15k+
P: 31,494
A curious comment Mario. I agree 100%, of course. It would indeed be required, but so would various other components of the required SQL. I suppose, because that's almost self-evident, I'm just curious that you'd bring it up.
Apr 18 '12 #7

100+
P: 332
@NeoPa
I just wanted to emphasize the UPDATE point, because before I read that other post of you (I am too lazy to find it again, or maybe you have to many posts lol) it never occured to me an UPDATE statement could be used to also INSERT provided the proper syntax of course.
Apr 18 '12 #8

NeoPa
Expert Mod 15k+
P: 31,494
Interesting point Mario. Like yourself, I had no idea about that for many years. I'd been working in Access (and SQL generally) and bemoaning the absence of such a facility. It was only while researching something for someone on here that I happened to see a reference to it and so I checked to find that it did, indeed, handle the add/amend situation perfectly.

PS. I couldn't find where I found this information. I have no reference in my database and neither MSDN nor Jet SQL Help (Finding Jet SQL Help) seems to have the explanation that I obviously found somewhere. Frustrating :-(
Apr 18 '12 #9

mshmyob
Expert 100+
P: 903
It is easy to create an update statement without using the Join clause though.

But your point of using UPDATE to insert a record (in Access) if it does not exist is new to me. Other systems use the INSERT OR IGNORE statement, or the REPLACE statement.

I will have to look up the exact syntax because I wouldn't want it to inadvertantly add a new record if I didn't want one added.

cheers,
Apr 18 '12 #10

NeoPa
Expert Mod 15k+
P: 31,494
Indeed. That was one of the first things I thought about when I came across it originally.
Apr 18 '12 #11

Post your reply

Sign in to post your reply or Sign up for a free account.