I have found the way to copy the record that I would like, but now I am having a violation of the Primary Key constraint. Here is what I am trying to do: We make a product that comes out of our maching into 2000lbs bags and it is givne a number, e.g. 26273. We store and sell it in those bags, but we also can sell it in smaller 50lbs and 25lbs bags. When we convert the bag from 2000lbs to 25lbs the product takes up 80 bags. Only 40 bags can be put onto a pallet, making the product number 26273 take up two pallets. The problem we have is when we store the pallet we scan the barcode of the product and then scan the barcode of the warehouse location, ONE pallet per location, and only ONE location per pallet. If we have two pallets with the same number than we cannot store them in the warehouse. To solve this problem my bosses what the first pallet to be number 26273B1 and the second pallet to be 26273B2 so that the pallets still contain the original number but is slighlty different in order to store them. When the product receives a number it also goes through several tests and that data is part of the record so both of the records still nedd to contain those test results. Whne I try to copy the record and place the B2 onto the number I get a Primary Key Constraint ODBC Failure. I know why I am getting the error, and I don't what to dissable the constraint to allow duplicate records, but I still need to have the ability to create this new record when we convert to 25lbs bags. So my question: Is there any way to copy a record, slighty change the Primary Key while copying it, and still be able to save it without the Primary Key Constraint error occuring. Also the database is in SQL with the interface front-end is in Access 2007. Any help would be greatly apppreciated. Thank you.
A:
I am not sure how you are wanting to copy a record i.e. in a form or programatically.
Note you can have a Primary Key based on more than 1 column so provided the 2 columns together are unique then you will not violate the constraint.
Mark3308
2009-06-30 13:28:58
A:
Simply build what is called an append query. You can pull all of the old previous values and substitute a value for any of the columns to be something of your choice.
So, the code + sql would look like:
Dim strSql As String
Dim oldPK As String
Dim newPK As String
oldPK = "26273B1"
newPK = "26273B2"
strSql = "insert into tblPallets (PalletNumber, Description, StockNumber)" & _
"select '" & newPK & "', Description, StockNumber" & _
"from tblPallets where PallentNumber = '" & oldPK & "'"
CurrentDb.Execute strSql, dbFailOnError
Albert D. Kallal
2009-06-30 13:29:52
I know SQL injection is not a serious issue for the Access database engine other than data corruption and security issues (e.g. revealing too much data) but is there any good reason for encouraging the use of code that does not use prepared statements in this day and age?
onedaywhen
2009-07-01 10:20:13
e.g. CREATE PROCEDURE ClonePallet ( :TargetPalletNumber CHAR(8), :NewPalletNumber CHAR(8) ) AS INSERT INTO Pallets (PalletNumber, Description, StockNumber)SELECT :NewPalletNumber, Description, StockNumber FROM Pallets where PallentNumber = :TargetPalletNumber;
onedaywhen
2009-07-01 10:26:22
Yes, there is a good number of reasons why to do it this way. First the example given will work on both sql server and ms-access. So the example is platform neutral. You can also use this code with MySql or even SharePoint. So this approach is platform independent and as written it will work with many different servers. Furthermore the example is less code and more flexible in that you don’t pre-define the parameters. If you bothered to read the JET white paper on performance you find that avoiding parameters with JET and building the string as sql can also run significantly faster.
Albert D. Kallal
2009-07-03 17:04:28
"If you bothered to read the JET white paper on performance..." -- the problem is a) with more than one SQL product to support I can't keep that information in my head and it first read it a long time ago and b) the Jet articles are slowly disappearing from MSDN e.g. this one disappeared in recent months advised against using domain functions (DSUM, DCOUNT, etc) on performance grounds...
onedaywhen
2009-07-07 20:10:24
Article was entitled 'Performance Overview and Optimization Techniques' probably referred specifically to Jet 3.0 but no doubt would still apply to the Access database engine, the URL I have (http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccessdev/html/ODC_MicrosoftOfficeDeveloperForumMicrosoftAccessMicrosoftJetDatabaseEngine.asp) now leads to a page that says, "The resource you are looking for has been removed, had its name changed, or is temporarily unavailable." Any idea how I can find it?
onedaywhen
2009-07-07 20:12:35
This is one of the reasons I would avoid the Access database engine for 'serious' work i.e. because much (most?) of it is currently undocumented and the documentation that exists is riddled with errors. I could bore you to death with many examples but here's my favourite for each: no documentation for CHECK constraints at all despite being the most important feature since 3.51; and the Access help for CREATE TABLE (http://office.microsoft.com/en-gb/access/HA012314411033.aspx) refers to temporary tables and mutli-column and/or NOT NULL named constraints that seemingly don't exist!
onedaywhen
2009-07-07 20:18:57
+1
A:
Why not store the original 2000 bag with the PK "26273-00-0000". That "00-0000" suffix indicates the original Bag.
For each subdivision into a smaller bag, "one-up" or increment the sequence that is suffixed at the end. You could use "00" for palletts, and "0000" for the bag-sequence number.
Hence "26273-B1-0001" - thru "26273-B1-0040" indicates the Product id that went into Pallett - one - the first 40 bags.
blispr
2009-06-30 14:21:06
Even though I absolutely LOVED this answer, my bosses do not. Why are decisions made by people with family ties instead actual knowledge!
gary A.K.A. G4
2009-06-30 14:46:16