key length of 100 char.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

KBDB - 28 Apr 2008 14:25 GMT

Hi,
I have always learn that you do not make a key with the length of 100
characters because it uses too much internal memory to process and slows the
system down. Is this still true with SQL and VBA? What suggestions would
you have me give my client? They want the part number to be 100 char.

Thank you,

Kathleen

Reply to this Message

Tom van Stiphout - 28 Apr 2008 14:56 GMT

Let them do it. PartNo is not a good PK anyway, because it may change
over time. You may say Cascade Update but I say too much risk.
Therefore add a PartID autonumber, and use it as the PK. It will
always be hidden, never shown on any form or report. It is simply
there to provide structure to your db (it is used as Foreign Key in
several related tables).

-Tom.

>Hi,
>I have always learn that you do not make a key with the length of 100
[quoted text clipped - 5 lines]
>
>Kathleen

Reply to this Message

KBDB - 28 Apr 2008 15:27 GMT

Tom,

I like that ideal but I have just one more question. I have set up a
auto/number before and when I tried to import data from Excel, I kept getting
a subscript out of range error. I could not figure out how to do this. We
are loading all the files from Excel speadsheets. For the auto/number field;
I made Excel spreadsheet field a format of number with no decimal. I started
at 1 and went up. What did I do wrong?

Thank you,

Kathleen

> Let them do it. PartNo is not a good PK anyway, because it may change
> over time. You may say Cascade Update but I say too much risk.
[quoted text clipped - 14 lines]
> >
> >Kathleen

Reply to this Message

Tom van Stiphout - 28 Apr 2008 15:43 GMT

Not sure that error was related. Subscript refers to an array index:
dim x(5) as string
for i = 1 to 10
debug.print x(i) 'will get error at x=6
next i

Also, autonumbers don't have to be imported, their values will be
auto-created when you import PartNo etc.

Last resort you import to a "temp" table with very loose restrictions,
and if the data is good, you copy that data to the production table
with an Append query.

-Tom.

>Tom,
>
[quoted text clipped - 27 lines]
>> >
>> >Kathleen

Reply to this Message

Bob Quintal - 29 Apr 2008 00:02 GMT

> Let them do it. PartNo is not a good PK anyway, because it may
> change over time. You may say Cascade Update but I say too much
[quoted text clipped - 4 lines]
>
> -Tom.

Horrors. The Part Number is used as primary key in every MRP system
part definition table I've ever seen, and I've see a lot, and as
foreign key in the order lines table, production order tables,
inventory tables etc.

>>Hi,
>>I have always learn that you do not make a key with the length of
[quoted text clipped - 6 lines]
>>
>>Kathleen

Signature

Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

Reply to this Message

Bob Quintal - 28 Apr 2008 23:59 GMT

> Hi,
> I have always learn that you do not make a key with the length of
[quoted text clipped - 6 lines]
>
> Kathleen

in the USA, MIL-STD-961, para. 5.6.2.2.1 states that a Part
Identification Number (PIN) shall follow the following rules: If a
PIN is needed, its construction shall be provided by the DoD
activity requiring it. PINs shall be kept short and shall not exceed
32 characters.

In 30+ years in industry, I've never seen a need for more than 24
characters. 32 is plenty.

Signature

Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

Reply to this Message