Changing Field Length; Any Issues?

[HMEpartsmanager]

strOld = "AZ2-"
strNew = "AZN-"
CurrentDb.Execute "UPDATE tablename SET ordlineVitemId = '" & strNew & "' & Val(Mid(ordlineVitemId,5)) + 1000 WHERE ordlineVitemId LIKE '" & strOld & "*'"

In case this was lost in the discussion, here are some helpful numbers:


there are about 500 Vendors in the vendor files (ACB, AZN, FSH, FST, etc...)
there are about 16,000 vendor items in the system (ACB-001, AZN-989, FSH-453, etc...)
there are about 45,000 purchase order line items (ACB-001, etc...)

So changing all 16,000 vendor items first from 7 characters to 8 characters seems to be the first step. Then the referential integerity will change all 45,000 purchase order line items.

From there, with the new sizing in place, the duplicate vendor issue can be addressed.

Before I forget, when using something like Val(Right(vitemId)) + 1000; does it place any null characters in there when writing it back out as a string? I seem to remember numbers have a leading space and had to be 'trimmed' to get the true length of the number. In our case, we want just 4 digits, and it has to have 4 digits. So no space or null, but zero(es) before any number that is not 4 digits.

Tim

[June7]

That depends. I might not fully understand the Vendor ID PK structure.

I was probably thinking you wanted to end up with one AZN vendor ID but if you want AZN-0XXX and AZN-1XXX then I expect the relational integrity/cascade update can serve to fix AZ2-XXX to AZN-1XXX.

[HMEpartsmanager]

Create query in Design view
Choose tbleVitem
Change query type to Update
Choose vitemId as the field
Table is already filled in (tblVitem)
Using BUILD for the Update To, I get this using the builder
Update To: Left( [tblVitem]![vitemId] , 4) & "0" & Mid( [tblVitem]![vitemId] , 5, 3)
Save query as Tst7to8

Logic looks fine?

Now I can't run it yet as I need to go and change vitemId to 8 characters long from it's current 7 characters. But I can't change the length of the field unless I get rid of the relationship with ordlineVitemId, so I do this. Relationship deleted between these two. I then change the length of both of these 2 fields.

Son of a gun!

I had trouble yesterday for a couple of hours doing tests and was getting Key Violation Errors. This morning, starting from scratch, posting my steps I am taking, and it all worked! lol

Ok, so the tblVitem (vitemId) has all been changed to a new 8 digit part number. Now on to the tblOrderLine (ordlineVitemId) and see if I can create the exact same type of update using:

Left( [tblOrderLine]![ordlineVitemId] , 4) & "0" & Mid( [tblOrderLine]![ordlineVitemId] , 5, 3)

Wow! I am batting perfectly this morning. This test worked. The only thing left to do would be to re-establish the relationship between these 2 fields. Ok, that is done.

According to what I have done above, I should see the original relationships and links all back to the way they were before there was an update? That is my guess.

Off to review results and compare links to see if anything looks odd.

Tim



I run the query, and I get the key violations. Now this leaving everything intact. Referential integrity is left alone.

[HMEpartsmanager]

These Work!

UPDATE tblOrderLine SET tblOrderLine.ordlineVitemId = Left(tblOrderLine!ordlineVitemId,4) & "0" & Mid(tblOrderLine!ordlineVitemId,5,3);

UPDATE tblVitem SET tblVitem.vitemId = Left(tblVitem!vitemId,4) & "0" & Mid(tblVitem!vitemId,5,3);

After reviewing the files, nothing is sticking out as an issue; that is a good sign. I will proceed to test on changing the duplicate vendors next. As long as this procedure works, and still references after the changes, the original links, this could be the solution.

Notes: Forms will have to be slightly adjusted to display the longer part numbers, but that is easy. I will also need to review my auto-increment number procedure. Obviously if my current number system for giving records a unique identifier has a current count of 845, the next number will be 846. This can't work, as the current routine will try to format this into a XXX-846; where with the new system, I need it to be xxx-0846. Should be a simple fix.

I still need to review more of course, just to make sure everything is fine, but the progress forward is a big burden taken off as this was going to be the hardest part to update the 16,000 items, yet still keep the links to all the records. I feel confident that I am on my way to a smooth update this weekend.

Other than that, I am pleased this morning with the test results. Very Happy!

Tim

[HMEpartsmanager]

Using this to test secondary vendor FAT; change to FAS
While doing so, add the digit "1" as a thousand indicator
instead of going numeric, add 1,000, and back to a string.

Update To: "FAS-1" & Right([tblVitem]![vitemId],3)
Criteria: "FAT" --- Didn't work
Changed to: Like "FAT-*" --- This did work, but pointed out a Vendor name issue upon review.

I will need to have an update that will also change all vendors of "FAT" to "FAS". I might be able to make this as one query?


Next Test:
Update To: "FSH-1" & Right([tblVitem]![vitemId],3)
Changed to: Like "FST-*"
Also added field 'vndId' from table 'tblVendor'
Update To: "FSH"
This test found 700 records to change, but had Key Violations.

So this may be the same situation as above where I had to disconnect the relationship, change the affected tables first, and then re-establish the relationship. I think the thing to keep in mind is that I have to disconnect, change the vendor name, then hook back up; all without touching files in between so nothing gets out of sync.

The day is upon me, but I think testing went well. I can continue later today or tomorrow.

Tim

[HMEpartsmanager]

I have been testing and getting everything ready for this week, and I think most of the things I have been worried about are now settling down.

One main issue I noticed, was that Relationships between fields that are being updated have to be severed. After I sever the relationship, I change the field lengths on both fields in the respective tables. Then I run my update query to make the changes. Then immediately put the relationship back. Without doing it this way, an error pops up about a Key Violation. I am changing data in both tables with the exact same functions, so no position (primary key links) are being moved or changed that are not being done the same to the corresponding table.

While I thought referential integrity would have taken care of these issues for me, that is not the case. From what I can gather, referential integrity works fine with editing data on your own on each record, but not when changing in mass quantities with other tables that 'need' those links to stay the same. I can understand that.

That seemed to be the main hurdle, understanding that I had to disconnected and reconnect relationships to do 16,000 item changes in bulk. :-)

Other than that, my checklist and order to perform these duties is coming along just fine. I have even finished my routine to get the next incremental number after the renumbering phase takes place. It was easier than I thought. A simple number <= 2000, then NewNumber=2001. The first time it runs, it will create the next incremental number of 2001. After that, it will always just go up by 1. So I am pleased at the progress. When I finish my checklist of items, I will print it out and go step by step just like I am doing on the TestDatabase. It should go smoothly with the time and effort I am putting into this.

Thanks to all who have contributed to this conversation. I shall give an update when I actually do the update to our working version of the database. After 8 years later, it is time to move on to Version 3, the 3rd major update that affects data.

Tim

For future: Spend extra time in the database design when creating a database. And from what I have learned, try to give 'extra' space so you know you can not surpass the extra room given at the beginning.

[orange]

The referential integrity you mentioned will work/does work. The issue is the field sizes you had would not accommodate you new values. You could have broken the relationships, adjusted the sizes of the fields involved in the relationship and then reestablished the links.

You can say you have 3 digit numbers that were linked. But if you want to change one of those fields to accept 8 digits, then I think access is saying like your field sizes are different - that could be an issue.

Good stuff! And the lesson now is you know about design, and more than you want to know about those fields that will "never, never be bigger than...". It's a lesson that will help you from now on.

[HMEpartsmanager]

Well, it went rather smoothly. I felt more confident on Saturday when I came in to take care of things. Having tested queries and procedures all week, I think I knew the flow that had to follow.

Disconnecting the relationship between the Vendor Item ID & the Order Item ID, changing the fields to 8 characters, then re-establishing the links worked. This allowed for only 1 query to update the Vendor ID; which updated the order item id's by referential integrity. Then it was simple renumbering the items. All items were updated to have a prefixed zero to go from XXX-999 to XXX-9999 format. Then I did a query for the secondary vendors. That was just a matter of find all FAT, change to FAS and add 1000 to the part number; old FAT-002 would be FAS-1002. Worked this way for the few secondary vendor situations I had. Then changed the main routine for incrementing part numbers to start at 2000 now.

After testing it all, it went just fine. I was more concerned about a big change to the structure than maybe I should have been, but to me; it looked like a challenge and I didn't want to screw it up since we depend on the database.

The only real 'issue' we had was when the office manager started to use the database on Monday morning, she was typing 'blindly' and was not paying attention. When she gets an invoice, she checks to make sure the line items are priced correctly to match, and also adds any freight costs as a new line item. Well, she was so used to putting in LHS-011 as a freight code for the past 8 years, that she wasn't thinking about it. The listbox would bring up LHS-0112 as it probably falls alphabetical in it's description of Brackets. So there are about 6 line items added that were for Brackets and Part # LHS-0112; when it should have been Freight Part # LHS-0011. Now she knows and will be paying closer attention.

Other than that, quite clean. Thanks to all who chipped into this conversation.

Tim