To Change Main Key Field Input Mask After 10 Years

[doyle60]

I have a database that has been used for about 10 years now. It is
split and has over 50 users who log in and all. It has 600 tables,
including ODBC linked tables to other databases and some that I should
have probably deleted. It has about 1500 queries, many of which are
probably outdated.

Anyway, it has been asked of me to change my main key field for a PO
number from an input mask of "00000?" to "0000000".

I do not believe I hard coded any queries to deal with the first input
mask, which forces the user to enter a 5 digit number plus an optional
letter if they want. I did not create an autonumber for this main
table and the lone key is PO. Perhaps I should have way back when but
it hasn't been a problem until today.

Anyway, how dangerous is it to me to change this key now, a key that
is involved in just about every query and process?

The reason for the request is so the company's main frame number can
be the same as the Access PO number. The main frame uses a number
and, of course, my Access PO uses a Text field.

It would seem dangerous to me to change the field type from a text to
a number field. I'm afraid of type mismatches. So I could leave it
Text but just change the input mask.

Any advise or warnings would be greatly appreciated.

I'm thinking of just creating another field altogether and just bring
this new field in where ever it is needed. But the real link would
have to be filled in some how, either programmatically or by hand and
forgotton.

Thanks,

Matt

 

[dymondjack]

This is quite the dilemma...

It would seem dangerous to me to change the field type from a text to
a number field. I'm afraid of type mismatches.

Correct... you would have to re-code every part of the project that touches
this field (queries, forms, relationships & tables, reports, vba code,
macros.... everything). And if you didn't get everything right, you may very
well cause irreversable damage to the project. Not recommended at all.


I can't really offer too much as far as the changing the input mask... I
would *think* that it would probably be fairly safe (I don't believe that it
would effect any infromation currently in the tables/queries), but I don't
know for sure, but based on the complexity of the project the only way to
find out would be to (make a backup, and) test it, making sure that all
existing procedures work, as well as any new procedures. No small task in
any case.

I mainly wanted to post against changing the datatype of the field... thats
definately a disaster.






--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[John W. Vinson]

The reason for the request is so the company's main frame number can
be the same as the Access PO number. The main frame uses a number
and, of course, my Access PO uses a Text field.

It would seem dangerous to me to change the field type from a text to
a number field. I'm afraid of type mismatches. So I could leave it
Text but just change the input mask.

Any advise or warnings would be greatly appreciated.

A number that's not used for calculations probably SHOULD be a Text field
(especially if it uses leading zeroes); your mainframe programmers may be
causing more trouble than benefit!

I agree with Dymondjack that converting the field would be fraught with
difficulties. It could be done but you'ld need to do a whole lot of spadework
and testing.

However just removing or changing an Input Mask should be innocuous - it won't
change the actual contents of any fields in the table. You may want to run a
search with a criterion of

LIKE "*[!0-9]*"

to find records containing any nonnumeric characters.

You can still do a Join from a text field to a number field, using the Val()
function to return a numeric value. It'll be slow because it must call the
function on every row and can't use indexes, and you won't be able to enforce
referential integrity. You could <shudder> have a redundant Number field
containing the same value as the text field, just for queries joining to this
mainframe number, but that has all the hassles of any redundancy - keeping the
two in perfect synch could be a real pain. It's a messy situation in which
none of the solutions are ideal!

 

[David H]

Does the Access database link directly to any tables on the main frame?
Is there any sort of export from the Access database to the mainframe?
How are the PO numbers generated?

I would not even begin to try and update the field given the size of the
database. I would rather focus on any issues that having a PO with a letter
would cause. If you're exporting information to the main frame, any PO with a
letter should crap out. As such you could create an Exception report that
identifies PO's with letters in the PO so that the PO number can be changed.
Granted that might cause issues if the PO has been issued.

David H

The reason for the request is so the company's main frame number can
be the same as the Access PO number. The main frame uses a number
and, of course, my Access PO uses a Text field.

It would seem dangerous to me to change the field type from a text to
a number field. I'm afraid of type mismatches. So I could leave it
Text but just change the input mask.

Any advise or warnings would be greatly appreciated.

A number that's not used for calculations probably SHOULD be a Text field
(especially if it uses leading zeroes); your mainframe programmers may be
causing more trouble than benefit!

I agree with Dymondjack that converting the field would be fraught with
difficulties. It could be done but you'ld need to do a whole lot of spadework
and testing.

However just removing or changing an Input Mask should be innocuous - it won't
change the actual contents of any fields in the table. You may want to run a
search with a criterion of

LIKE "*[!0-9]*"

to find records containing any nonnumeric characters.

You can still do a Join from a text field to a number field, using the Val()
function to return a numeric value. It'll be slow because it must call the
function on every row and can't use indexes, and you won't be able to enforce
referential integrity. You could <shudder> have a redundant Number field
containing the same value as the text field, just for queries joining to this
mainframe number, but that has all the hassles of any redundancy - keeping the
two in perfect synch could be a real pain. It's a messy situation in which
none of the solutions are ideal!

 

[doyle60]

I link the Access PO field with the main frame's Reference field, and
both are text.

I believe it best to introduce a new number field and to change all my
reports and forms to use it, but to keep the true link the original
PO.

After that change, I will link Access's new field with the main
frame's PO, not the Reference field (because they will not longer need
to put the Access PO in the mainframe's Ref.

I think this the safest thing to do and one that won't trouble 95% of
the users.

Matt