Changing Field Length; Any Issues?

[HMEpartsmanager]

Schedule to update database on: 04/04/2013 to 04/07/13

Proposed: Change field size from 7 characters to 8 characters

NOTE: THIS IS THE PRIMARY KEY FOR THIS TABLE

Table: tblVitem
Field Name: vitemId
Data Type: Text
Field Size: 7 (xxx-999)

Vendors all have a prefix of 3 Letters.

As I enter the subform, I provide the data needed for a
vendor purchase and the OnUpdate event will go to another
table and grab the next incremental part number for this
vendor. Then it is converted to a string and concatenated
to the vendor id. And thus; we have a new vitemId.

Example: AZN is for Amazon. When I enter the data for
an item that AZN can be a vendor for, the VendorItemID
will be created with AZN-001 upon completion.
This 7 digit text field has worked well up until you hit
999 parts to assign to a vendor.

When I ran into this a year or so ago, to fix the immediate
problem, I created secondary vendors as a work-a-round. :-(

So now I have AZN and AZ2 that are vendor files with the
exact same data, except their assigned vendor code. This
is also done to a couple of vendors in my database.

Now that I have time, I would like to fix these issues.

Example: Vendor AZN = Amazon, and I have 999 different items
that I have or can buy from them in our database. To put in
that 1,000th part; I created a AZ2 Vendor that = Amazon. All
data in required fields are the same for both vendors, only the
creation of a bogus vendor AZ2 was used to gain another 999
part numbers to utilize. Wrong approach, but fixed the
immediate problem back when it was discovered as an issue.

So I am planning on reading all parts associated with AZ2. Convert
part number AZ2-001, to AZN-1000. Read in AZ2-002, and convert
this to AZN-1002, etc... down the line. This will force the
vendor name back to what it really should be, and not the bogus
vendor ID that was used to gain more item selections.

My biggest concern is that with these changes, the fields being
changed are also the Primary Keys for these tables. So changing
Item AZN-001 to AZN-0001 as the primary key, will have an effect
on the other tables (links?) that were using AZN-001 as their
key to get data; if I state it right. Will forms, reports, and
other tables be able to still pull the correct data on that
primary key, even though the primary key has changed it's
value from AZN-001 to AZN-0001; however it does still keep
it's position as the primary key? I am not sure how this
will affect my forms and reports and if this is going to get
too deep to fix.

It makes sense the way I am envisioning it, but, a big task
to make sure each step is taken correctly. If I set it up
as a checklist, maybe I can make it go smoothly. :-)

Thank you for any advice, suggestions, solutions, and notes that
are provided. I have lived with the database this long, I just
think it needs to be updated while I have a little time to prepare
the next phase of the database. And the 999 part limit is one
that needs adjusting for the future.



Tim

[June7]

Can use string manipulation functions to modify the value to insert a leading 0 to each key. This can be done in an UPDATE action on the PK field in Vendors table.

AZN-0001 will not link to AZN-001. Need to update the FK of related records. I think if you have CascadeUpdate set in the Relationships builder, this will happen automatically when the PK is changed, if PK is changed in the existing field, not created in a new field. http://www.ehow.com/how_13631_unders...g-updates.html
http://office.blogs.webucator.com/20...t-access-2010/

However, changing the related records that need FK changed from AZ2- to AZN- (and other vendors) is trickier.

[HMEpartsmanager]

An UPDATE action was what I was thinking of doing.

I do have CascadeUpdates setup in the relationships, so I would think changing 1 FK, should change the other links associated with it. What I am not sure of though, is some background items seem to be needed before doing an Update action. Such as changing any field in any table where this FK is being used (such as the tblOrderLine that is used for ordering), to hold the new proposed 8 digits. If I was to just change the FK in the tblVitem, without changing any other fields to 8 digits; It could be real bad.

But to your point of AZN-0001 will not link to AZN-001; I realize that portion, and why I posted. I couldn't remember that function that happens when you change data and all links change with it. Now that you bring it up, it was called referential integrity. Because all these relationships have this on as they were created, I was hoping this was going to be my 'saviour' and time saver for this. I take it you think that should work also?


I think, that my plan to do this would require me to change all field lengths that are affected to the correct length first. Now that these fields in varying tables all hold enough room, then an Update action could take place to give a new ID. Then referential integrity should take care of the changes amongst the tables. Does this sound right to you?

I think forms should all stay intact, as they are merely bringing in data from tables, and they don't care if the data is 7 or a new 8 digit length. Other than maybe some small formatting issues, it should work......?

Sure is nice to post and discuss something of this scope before undertaking the chore. Thanks for chipping into the conversation.

Tim

[June7]

The cascade update will get you partway. An expression can be built that will add a leading 0 in the sequence but there is no way for the process to know that AZ2 should now become AZN. Unless you have a table that associates the old with the new that can be used to modify the child records foreign key from AZ2 to AZN. Once that is done, the AZ2 record in parent table can be removed.

[HMEpartsmanager]

The cascade update will get you partway. An expression can be built that will add a leading 0 in the sequence but there is no way for the process to know that AZ2 should now become AZN. Unless you have a table that associates the old with the new that can be used to modify the child records foreign key from AZ2 to AZN. Once that is done, the AZ2 record in parent table can be removed.

I will review this tomorrow and see if I can't get the flow set up. I want to have a flow/checklist of what I have to accomplish before I get started. I think I can manage changing field sizes, make sure that all links in PK fields are set with referential integrity and cascading updates, and will have backups of the database before I start. After I get a checklist made, then I can work on a way to get from AZ2 to AZN. My initial rough thoughts are:

Query to get all records where left 3 digits are (AZ2)
Read in the right 3 digits,
convert to numeric,
add 1000,
PK now equals "AZN-" & Trim(String(numeric_value_calculated))
re-write the PK with the new vitemId.
Get next AZ2 in the query and repeat.

Something like that is on the top of my head.

Tim

[June7]

Is AZ2/AZN the only translation that needs to be done? No other vendors?

[HMEpartsmanager]

Is AZ2/AZN the only translation that needs to be done? No other vendors?

No, there are a few others over the years. FER/FE2, FSH/FST, and I think a few others.

[HMEpartsmanager]

Thought I would post a more literal explanation of where I am at right now.


Priority List of Items To Take Care of
--------------------------------------
Goal is to change the database so that vendors can have
more than 999 items associated with each vendor. To fix,
I am going to change the allowed 7 digit character Primary Key,
to an 8 digit character Primary Key. This will allow up to
9999 items to be associated with each vendor.
Secondary goal is to change the current 'bogus' vendors
that were created, and re-number them to the new length,
and then delete the old 'bogus' vendor Id name.

Table: tblVitem
Field: vitemId
Description: 7 Characters - (XXX-999)
Data type: Text
Field Size: Change from 7 to 8 Characters

Table: tblOrderLine
Field: ordlineVitemId
Description: 7 Characters - (XXX-999)
Data Type: Text
Field Size: Change from 7 to 8 Characters

Table: tblVendor
Field: vndId
Data Type: Text
Field Size: 3 Characters

Affected Vendors & Current Count In Table
-----------------------------------------
AZN & AZ2 (AZN=999 & AZ2=175)
FAS & FAT (FAS=868 & FAT=833)
FER & FE2 (FER=979 & FE2=235)
FSH & FST (FSH=861 & FST=883)
HOM & HDT (HOM=999 & HDT=184)

Now that I have identified what table/fields are affected, and what vendors are affected, I can proceed to write some type of query to make it all happen. I do not mind a gap from the beginning vendor series number, and the new vendor series. So i am thinking of taking the starting item from the secondary vendor, and give it #1001, and then proceed to the end incrementing by +1.

I will post more later.

Tim

[HMEpartsmanager]

Checklist Of Things To Make Sure All Goes Smoothly

1) Check Relationships for 'vitemId' and 'ordlineVitemId' to make sure that:
Enforce Referential Integrity is Checked
Cascade Update Related Fields is Checked

There is a 1-to-Many relationship from 'vitemId' to 'ordlineVitemId'. This has been checked and is set.


Next steps have not been taken yet.

2) Change Field Size for both 'vitemId' and 'ordlineVitemId' from 7 characters to 8 characters.

This will allow a longer field length to increase the numeric part of the Primary Key to go up to 9999 instead of the current 999 limit. Note that both of these two fields are Primary Keys for their respective tables.

3) Create an Update Action on 'tblvendor' to read in current value, and change to new value.

' ================================================== =======
' ================================================== =======
'
' Routine Logic
'
'
' ** Need To Insert A Loop Here To Step Through All Records
'
'
Dim tstPart As String
Dim tstVendor As String
Dim tstItem As String
Dim tstOldNumeric As Integer
'
Dim tstNewPart As String
Dim tstNewVendor As String
Dim tstNewItem As String
Dim tstNewNumeric As Integer
'
tstPart = vitemId
tstVendor = Left(vitemId, 3)
tstItem = Right(vitemId, 3)
tstOldNumeric = Val(tstItem)
'
If tstVendor = "AZ2" Then
tstNewVendor = "AZN"
tstNewNumeric = tstOldNumeric + 1000
tstNewItem = Trim(Str(tstNewNumeric))
tstNewPart = tstNewVendor + "-" + tstNewItem
'
vitemId = tstNewPart
'
End If
'
'
' ** Need To Loop Back To Get Next Record
'
' ================================================== =======
' ================================================== =======

4) Repeat Step 3 with each vendor that has the duplicate vendor entries. Change the Update Action to the next vendor that needs to be changed, and run the query again for each vendor that is being changed.

Following the above steps, I should have a database where two tables have been updated to a new Primary Key, yet retaining their current links to each other.

This is how far I got today. Does anyone see anything I am missing? I realize I still have to write the query, but is my logic going in the right direction? In the steps above, is there a glaring omission I am not seeing?


Thank you,

Tim

[June7]

I am confused by 'both of these two fields are Primary Keys'. A foreign key field cannot be a primary key in a 1-to-many relationship.

Code can be simpler.

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

The variables can be populated in a loop that references a recordset of the translation pairs, assuming you have a table of those pairs or can build an array of those pairs and reference the array.

Or why even use VBA code - since they are so few just manually run an Access UPDATE query object 5 times.

[HMEpartsmanager]

I am confused by 'both of these two fields are Primary Keys'. A foreign key field cannot be a primary key in a 1-to-many relationship.

Just a quick note here as my day is just starting here, but you are correct.

I was thinking because the 'ordelineVitemId' was the main link from Vendor Items to Order Lines; that it was a primary key. Sorry about that, and thanks for pointing it out.

The only primary key in this situation is the 'vitemId' for the Vendor Table.

I will review the code you suggested above later today.

Tim

[orange]

Just noticed this thread. Keep a backup (or 2) of what you have, it may be needed to resolve any anomalies.
Please keep us posted of how your conversion works - pro and con. It seems (and I know hindsight is always 20/20) that you have a lot of intelligence built into your codes. Trying to outguess a business process(es) with codes and the number of possible records often gets developers into trouble. Often it is better to use meaningless autonumber/sequence as PK for the database software and keep your own identifiers for things you need to control.

Anyway, make sure you keep backups and keep us up to date on your progress.
Good luck.

[HMEpartsmanager]

Just noticed this thread. Keep a backup (or 2) of what you have, it may be needed to resolve any anomalies.
Please keep us posted of how your conversion works - pro and con. It seems (and I know hindsight is always 20/20) that you have a lot of intelligence built into your codes.

Anyway, make sure you keep backups and keep us up to date on your progress.
Good luck.

Thanks for chipping into the conversation, thought process.

Definitely going to be making copies of the database before the start. Then after the process, I am going to go through with a liberal comb to see if it all worked as it should. If something does go odd on me, I can alway resurrect a backup and try again with fixed/adjusted code. I am planning on doing this later this week. There are only 2 of us who use this database, and the other person is on vacation at the end of the week. I figured this would be a good time to update so the only data entry needed to catch up on, will be whatever I don't have accomplished before I start the upgrade.

I grew up with code writing from the late 70's and early 80's; so my logic stems from the old Basic languages in existance at the time. You literally had to write code step by step. Today's languages have more smooth logic designs, but I am not as versed in the new functions. At least by writing the thought process in public, people can comment on it when I am missing something. Especially as June just posted about a single update line for an Update Query. It looks like something to check out. Can't wait to delve into after I finish this reply.

And yes, for anyone following; and for those who may catch this thread in the future, a follow through on how it all went down will be here. My goal not only for me to get everything lined up correctly, was to allow a future person to come in here and see how it was done when they have to do something similar to what I am undertaking.

Thank you,

Tim

[HMEpartsmanager]

Code can be simpler.

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

Using the above, it would seem it would go through each known record in the table of Order Lines; this could and would be many.

However, using your query above, wouldn't changing your line to be the Vendor Table (The one part of the 1-to-many), force the matching records in the Order Lines Table to change to the new number? I am basing this on the Referential Integrity being set in this relationship. Do I have this right that it would be better to use the main table in this scenario (vitemId), instead of each of the individual purchase order lines where this part was ordered?

If I am right, then I could use:

strOld = "AZ2-"
strNew = "AZN-"
CurrentDb.Execute "UPDATE tblVitem SET vitemId = '" & strNew & "' & Val(Right(vitemId,3)) + 1000 WHERE vitemId LIKE '" & strOld & "*'"

I changed the tablename to tblVitem, the ordlinerVitemId to vitemId, the Middle String 5, to a Right String 3, ordlineVitemId to vitemId. I am not sure if there was a special purpose and calling of the Mid$ function that needed to be 5 characters. But as I posted now, maybe I make a bit more sense of the route I am going towards. I hope I didn't misunderstand your code posting. :-)

The variables can be populated in a loop that references a recordset of the translation pairs, assuming you have a table of those pairs or can build an array of those pairs and reference the array.

Or why even use VBA code - since they are so few just manually run an Access UPDATE query object 5 times.

I lose it here. If I am setting the Vendor Part Number (vitemId) to the new part number, the many side of this relationship should get changed automatically shouldn't they? This is why I am lost on this part. Why would I need a tranlation pair table? I could create it I think, but I am missing the logic on why I need it and how I would walk through it.

Guidance or clarification would be nice.

Thank you June. I am appreciating everyone pitching in to make sure a smooth update will happen.

Tim

It just dawned on me, that maybe you were talking about the original vendor part numbers being lengthened the extra digit. I had forgot about that. So maybe that is the referencing you are talking about? If so, could a similar update (execute) be given that took the AZN-, and left it AZN-, but instead of adding 1000, have the line reformat the part portion #001 to be 0001? and 999 would get converted to a string 0999? I may have missed something here.

[HMEpartsmanager]

For the message above, my thought was that the conversion from:

AZN-001 to AZN-0001
up to
AZN-999 to AZN-0999

Would happen first. Then utilizing the Plus 1000 to the update you (June) posted, that line would execute afterwards to get the secondary vendor items in line with the new numbering scheme.

Tim