Create a Sequencing PK without using AutoNumber field

[rdemyan]

I have a table that is imported from another system. I have no choice
but to use the data as is; they will not change it. The records are
not unique.

For reasons I don't want to get into, I can't use an AutoNumber field.
Also, there is no combination of fields that will provide a unique key.

So, I could assign a number in my import code. My thought would be to
just select the highest number currently existing in the table and
increment it by 1. This should work but if anyone knows of any
unintended consequences, I'd appreciate a heads up.

Thanks.

[Keith Hutchison]

rdemyan wrote:

I have a table that is imported from another system. I have no choice
but to use the data as is; they will not change it. The records are
not unique.

For reasons I don't want to get into, I can't use an AutoNumber field.
Also, there is no combination of fields that will provide a unique key.

So, I could assign a number in my import code. My thought would be to
just select the highest number currently existing in the table and
increment it by 1. This should work but if anyone knows of any
unintended consequences, I'd appreciate a heads up.

I've had timing issues when I've used this approach with multiple users
running import routines.

if you're importing via vba on a record by record basis I'd just
implement a counter.
Another key I sometimes use is a time stamp with microseconds and/or a
counter stored as a string.

Cheers

Keith

[Tom van Stiphout]

On 27 Dec 2006 08:25:41 -0800, "rdemyan" <rd*****@hotmai l.comwrote:

Unsure what problem you're trying to tackle.
You say you must use the data as is, then you say you could assign a
number in your import code. That reeks of your ability to add to the
data - perhaps an additional column.
What import code are you speaking of? DoCmd.TransferD atabase? We may
need to see some code.
Once the table is in Access, why not alter the table and add a PK?
Why require a PK? For pseudo-uniqueness? You already said the data has
duplicate rows. I would start there. What's the significance of having
two rows with the exact same values in all fields? It may be bogus and
could be filtered out, or perhaps it is significant. We may need to
know more about the data.
Once you have this unitue number, what would you do next?

Perhaps you could address these questions. It would narrow down the
next set of them.

-Tom.

>I have a table that is imported from another system. I have no choice
but to use the data as is; they will not change it. The records are
not unique.

For reasons I don't want to get into, I can't use an AutoNumber field.
Also, there is no combination of fields that will provide a unique key.

So, I could assign a number in my import code. My thought would be to
just select the highest number currently existing in the table and
increment it by 1. This should work but if anyone knows of any
unintended consequences, I'd appreciate a heads up.

Thanks.

[rdemyan]

The data is imported from a spreadsheet using a custom procedure. With
the way things are currently done, you can legitimately have duplicate
rows.

I just want to add a field so that I can for the purposes of my
database be able to find a unique row. For example, if the duplicate
rows are shown in a listbox along with other non-duplicate rows and a
user wants to delete one of the duplicate rows from the table, how
would he do it without a PK. Without a PK wouldn't the delete
operation delete all duplicate rows. Plus I would have to specify a
compound key of all fields in order to even just get the duplicates of
that specific instance.

Besides, I thought it was good practice to have PKs. Upsizing to SQL
Server requires that each table have a PK doesn't it.
Tom van Stiphout wrote:

On 27 Dec 2006 08:25:41 -0800, "rdemyan" <rd*****@hotmai l.comwrote:

Unsure what problem you're trying to tackle.
You say you must use the data as is, then you say you could assign a
number in your import code. That reeks of your ability to add to the
data - perhaps an additional column.
What import code are you speaking of? DoCmd.TransferD atabase? We may
need to see some code.
Once the table is in Access, why not alter the table and add a PK?
Why require a PK? For pseudo-uniqueness? You already said the data has
duplicate rows. I would start there. What's the significance of having
two rows with the exact same values in all fields? It may be bogus and
could be filtered out, or perhaps it is significant. We may need to
know more about the data.
Once you have this unitue number, what would you do next?

Perhaps you could address these questions. It would narrow down the
next set of them.

-Tom.

I have a table that is imported from another system. I have no choice
but to use the data as is; they will not change it. The records are
not unique.

For reasons I don't want to get into, I can't use an AutoNumber field.
Also, there is no combination of fields that will provide a unique key.

So, I could assign a number in my import code. My thought would be to
just select the highest number currently existing in the table and
increment it by 1. This should work but if anyone knows of any
unintended consequences, I'd appreciate a heads up.

Thanks.