I have inherited the maintenance of a legacy Access database.  This database gets its data from our company's Unix based operations software via a nightly update.  The Unix based system has significant field size limitations with regards to customer name (which is a company name).  The customer's company name field is limited to 23 characters.  It stores two fields worth of data, Address Line 1 and Address Line 2 as well.  Both also are limited to 23 characters each.  The current data has about 950 customer records.

As a result of this field size limitation, a significant amount of the customer's company names will not fit into the company name field.  The person who enters that information for our company insists on the whole name being shown in the system so they have repeatedly entered the "balance" of the customer's company name into the address line 1 field and then put the data that would go into address line 1 into the address line 2 field.  This has created a big issue when trying to develop new upgrades to the existing legacy database such as utilizing mapping integration for example.

Obviously, I'm not restricted to 23 characters in the Access legacy database but there is no way to expand the field size in the Unix based system.  My work around to this point has consisted of creating a separate manually updated table in the back end called "tblCustomersStatic".  This table consists of the customer's company name, a separate company "DBA" field in case that is different than the company's legal name, corrected address line 1 field, and a corrected address line 2 field. The two tables are related via the company's ID field which is the customer's account number.

Since we don't often add many new customers, maybe a dozen a year on average, my work around has been to run a "missing records" query which picks up any new customers that I need to manually add to the tblCustomersStatic table once per week.  I then go to the tblCustomersStatic table and add the missing data to those fields.  I then run an update via VBA to refresh the true tblCustomers table and the problem is resolved.  That's worked fine for the last several years.

I'd like to automate the process as much as possible so that, God forbid, I'm not there someday, the updating can continue on.  Over the years, I've tried various techniques of using query criteria, in string searches but the data can be so random as to length, characters, numbers, etc. that I can never seem to account for all possible outcomes to get the data correctly broken into the corrected fields. That's also why I'm posting the question here at it seems to fit in with this lesson.

My question is...

Are there better ways to address this issue?  Has anyone else had a similar problem and how did you approach it? What degree of success were you able to achieve?

Any guidance would be greatly appreciated.

P.S. telling the person who's entering the data in the wrong fields is also not an option.  I already have.  She refuses to change because it's her firm belief that the information must be in there completely so that if we ever take a customer to court over an outstanding debt, if the customer's name isn't in there in its entirety that we would automatically lose the case. The funny part is, I've worked at the company for 27 years and in that entire time we have never, not once, taken a customer to court over an unsettled debt.  Even when it was possible to do so and even when it was a significant amount (five figures). They have written it off every time.  So, it's pretty much like trying to explain calculus to a donkey.  She also owns the company and is my boss, so it just ain't happenin'. :)

P.P.S.  Richard, I've taken classes on Access from nearly every major platform over the past four years - Linkedin Learning, Udemy, etc.  I think your lessons are better than any I've taken.  The only other instructor I've found that's close is Bruce Myron who has his classes on Udemy.  However, your structure and progression is much more thorough than anybody, bar none.  Thank you for sharing your knowledge and experience.