469,303 Members | 2,025 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,303 developers. It's quick & easy.

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

77 64KB
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,

10 7670
mshmyob
904 Expert 512MB
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
3,080 Expert 2GB
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
Joe Y
77 64KB
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
32,173 Expert Mod 16PB
Actually, if you use an UPDATE query, it will add any records that don't already exist anyway.
Apr 17 '12 #5
Mariostg
332 100+
@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
32,173 Expert Mod 16PB
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
Mariostg
332 100+
@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
32,173 Expert Mod 16PB
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
904 Expert 512MB
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
32,173 Expert Mod 16PB
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.

Similar topics

6 posts views Thread by BaWork | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.