Upsized primary key fields and "aaaaa"

[Tech_vs_Life]

In tables upsized to sql server 2005 from access 2007, the upsized primary
key fields all have "aaaaa" prefixed to their names, and "_PK" suffixed. It
used to be that Access needed the "aaaaa" to determine the primary key when
linking--does anyone happen to know if that is still needed in Access 2007?
or can I rename those indexes, dropping the prefix but keeping the "_PK"
suffix? (I assume sql server doesn't need the "aaaaa" prefix.) Thanks.

 

[Guest]

Hi

Seems like you are having fun doing this upsize!!

In tables upsized to sql server 2005 from access 2007, the upsized primary
key fields all have "aaaaa" prefixed to their names, and "_PK" suffixed. It
used to be that Access needed the "aaaaa" to determine the primary key when
linking--does anyone happen to know if that is still needed in Access 2007?
or can I rename those indexes, dropping the prefix but keeping the "_PK"
suffix? (I assume sql server doesn't need the "aaaaa" prefix.) Thanks.

From http://office.microsoft.com/en-us/access/HP052730091033.aspx
<quote>
The Upsizing Wizard converts Microsoft Access primary keys to Microsoft SQL
Server non-clustered, unique indexes and marks them as SQL Server primary
keys. If you choose to link the upsized SQL Server table to your Access
database, the Upsizing Wizard also adds the prefix "aaaaa." Microsoft Access
chooses the index that is first alphabetically in the list of available
indexes as the primary key and the "aaaaa" prefix ensures that the right
index is chosen.

All other indexes retain their names, except where illegal characters are
replaced with the "_" character. Unique and non-unique Microsoft Access
indexes become unique and non-unique SQL Server indexes. Note that SQL Server
doesn't support ascending or descending indexes.
</quote>

As this says "Microsoft Access chooses" it is not clear if it is a
requirement for Access itself rather than the upsizing wizard

John

 

[Brendan Reynolds]

Access has never needed anything prefixed to field names to determine which
fields constitute a primary key - or for any other reason, for that matter.
As long as your field and index names are not too long and don't include any
illegal characters Access doesn't care what you call them, and never has.

 

[Tech_vs_Life]

See the other post; Microsoft claims Access does need them (when linking to
sql server), but I'm not sure if that's true in acc 2007: "Microsoft Access
chooses the index that is first alphabetically in the list of available
indexes as the primary key and the 'aaaaa' prefix ensures that the right
index is chosen." I never heard of any other reason given for the 'aaaaa'
prefixing.
But maybe, as the other poster (John Bell) suggested, it's a requirement of
the access upsizing wizard, not access itself?
If so, it seems like a silly coding issue that they should have corrected,
rather than leave the user to correct. I just have to loop through and get
rid of all the "aaaaa"'s.

 

[Tech_vs_Life]

it is not clear if it is a
requirement for Access itself rather than the upsizing wizard

Good point. I assume it's probably just the upsizing wizard, but maybe
someone will confirm.

 

[Sylvain Lafontaine]

If I remember correctly, this is not a requirement from Access itself but a
requirement from ODBC because ODBC will use the first unique index in
alphabetical order as the primary key. This was a well known requirement
for ODBC 3.5 and I'm almost sure that this is still true with ODBC 4.0;
however, I cannot tell you for sure.

This is not limited to Access ODBC linked tables, so for example you will
need this if you are accessing the SQL-Server from a VB or an ASP
application using ADO and the ODBC driver.

For the mixed Microsoft ODBC/OLEDB provider and for using DAO with OLEDB
(instead of ADO), I don't know.

In case of doubt, maybe it's better to leave it this way.

 

[Brendan Reynolds]

Which other post? Where is the quote taken from?

 

[Tech_vs_Life]

Sorry, I thought you had the rest of the thread. Here is the repost of the
post I was talking about:

In tables upsized to sql server 2005 from access 2007, the upsized primary
key fields all have "aaaaa" prefixed to their names, and "_PK" suffixed.
It used to be that Access needed the "aaaaa" to determine the primary key
when linking--does anyone happen to know if that is still needed in Access
2007? or can I rename those indexes, dropping the prefix but keeping the
"_PK" suffix? (I assume sql server doesn't need the "aaaaa" prefix.)
Thanks.

From http://office.microsoft.com/en-us/access/HP052730091033.aspx
<quote>
The Upsizing Wizard converts Microsoft Access primary keys to Microsoft SQL
Server non-clustered, unique indexes and marks them as SQL Server primary
keys. If you choose to link the upsized SQL Server table to your Access
database, the Upsizing Wizard also adds the prefix "aaaaa." Microsoft Access
chooses the index that is first alphabetically in the list of available
indexes as the primary key and the "aaaaa" prefix ensures that the right
index is chosen.

All other indexes retain their names, except where illegal characters are
replaced with the "_" character. Unique and non-unique Microsoft Access
indexes become unique and non-unique SQL Server indexes. Note that SQL
Server
doesn't support ascending or descending indexes.
</quote>

As this says "Microsoft Access chooses" it is not clear if it is a
requirement for Access itself rather than the upsizing wizard

John

 

[Brendan Reynolds]

Well I can't say for sure about the upsizing wizard, but it is certainly not
a requirement of Access itself. Here's a test that anyone can try for
themselves ...

In the SQL Sever 2000 version of Northwind, the Customers table has five
indexes, City, CompanyName, PK_Customers, PostalCode and Region. Try
creating a new MDB, and linking that table. Does Access mistakenly assume
that City is the primary key? Nope. Does Access correctly recognize that
PK_Customers is the primary key? Yup.

I tested in Access 2003.

 

[Brendan Reynolds]

Thanks. I really don't know what they were trying to say there. As you say,
the statement is somewhat ambiguous. I hope the test that I've described
elsewhere in this thread might be useful.

--
Brendan Reynolds
Access MVP

Sorry, I thought you had the rest of the thread. Here is the repost of
the post I was talking about:

In tables upsized to sql server 2005 from access 2007, the upsized
primary key fields all have "aaaaa" prefixed to their names, and "_PK"
suffixed. It used to be that Access needed the "aaaaa" to determine the
primary key when linking--does anyone happen to know if that is still
needed in Access 2007? or can I rename those indexes, dropping the prefix
but keeping the "_PK" suffix? (I assume sql server doesn't need the
"aaaaa" prefix.) Thanks.

From http://office.microsoft.com/en-us/access/HP052730091033.aspx
<quote>
The Upsizing Wizard converts Microsoft Access primary keys to Microsoft
SQL
Server non-clustered, unique indexes and marks them as SQL Server primary
keys. If you choose to link the upsized SQL Server table to your Access
database, the Upsizing Wizard also adds the prefix "aaaaa." Microsoft
Access
chooses the index that is first alphabetically in the list of available
indexes as the primary key and the "aaaaa" prefix ensures that the right
index is chosen.

All other indexes retain their names, except where illegal characters are
replaced with the "_" character. Unique and non-unique Microsoft Access
indexes become unique and non-unique SQL Server indexes. Note that SQL
Server
doesn't support ascending or descending indexes.
</quote>

As this says "Microsoft Access chooses" it is not clear if it is a
requirement for Access itself rather than the upsizing wizard

John

 

[Tech_vs_Life]

Thanks, that answers the question, and I hadn't found that info elsewhere.
I guess I'll leave it as is, though it's not beautiful.
If anyone happens to know that it's not a limitation any more (in odbc 4),
please post.

 

[Tech_vs_Life]

I see Access selecting the right primary key in my tests also, regardless of
alphabetic order, but maybe it was an issue with older versions of odbc
drivers (assuming that access mdb is using an odbc driver to access sql
server). There was another post on this thread on odbc, explaining the
issue.

 

[Tech_vs_Life]

note: I haven't been able to get Access 2003 or 2007 to misidentify
not-the-first-in-alphabetic-order primary keys on sql server 2005 tables,
regardless of which method I use, so perhaps the problem was fixed--unless
the problem comes up only in less common situations.

 

[Sylvain Lafontaine]

Yes, I agree with that. I've just made myself some tests using tables with
multiples uniques indexes and with or without a timestamp and Access doesn't
seem to show any problem; so I think that this is an old problem that have
been solved since a long time.

However, I've done these tests with Access and not with other ODBC clients
such as VB or ADO. However, even with VB or ADO, most people will use OLEDB
instead of ODBC; so it's not really an issue for anyone any longer.

 

[Tech_vs_Life]

fwiw, I discovered that Microsoft's "Sql Server Migration Assistant for
Access" does not add the "aaaaa" prefix. However it prefixes the table
name and a $ to original index name, and the primary key alone gets renamed
"tablename$PrimaryKey"

 

[Guest]

In a well designed system, there will only be one unique index on
each table :~).

In a not-badly-designed real system, there will probably be only a
few tables that have multiple unique indexes.

If you have one and only one unique index on each table, you
can ignore or alter the naming convention.

ODBC does not use indexes at all. Indexes are a property of
the Back End database. What ODBC uses is a (set of) data fields.
ODBC identifies a set of data fields to uniquely define a "row",
and it is up to the back end to use an index if it wishes to.

Access/ODBC select a (set of) unique identifying field(s) from
(the alphabetically first unique index). But, if you wish, you can
tell Access/ODBC to use a different set of fields. You are
required to do this if Access/ODBC does not find a unique index.
If Access/ODBC has already chosen a set of fields to use to
uniquely identify a "row", you can tell DAO to drop the pseudo
index it uses to identify the fields, and create a new pseudo index
using the fields you want.

(david)

 

[David Portas]

In a well designed system, there will only be one unique index on
each table :~).

In a not-badly-designed real system, there will probably be only a
few tables that have multiple unique indexes.

Huh? I can't possibly agree. Firstly, here's the logical argument.
Let's take the simplest possible case:

CREATE TABLE tbl (a INT NOT NULL, b INT NOT NULL, ...);

Assuming the following dependencies: {A}->{B}, {B}->{A} it's obviously
"better" to enforce both keys rather than just one. If only one is
enforced then the table doesn't accurately reflect the business rules
and incorrect data could result. In SQL Server, indexes are
automatically created to support keys - except for one or two trivial
special cases they are by far the most effective method to enforce
uniqueness. Or would you propose to use triggers instead?

Secondly, there is the practical argument. Indexes are a tool for
enhancing database performance. If indexed columns are required to be
unique then performance enhancement is best served by creating a
unique index. Unique indexes provide the optimizer with extra
information that it can use to achieve the most efficient plan.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

 

[Guest]

Assuming the following dependencies: {A}->{B}, {B}->{A} it's obviously

If you mean that A and B mean the same thing, one of
the columns should probably be removed.

I realise that this is can be a difficult discipline, but I'm surprised
that you have more than a few tables with this problem.

(david)

 

[David Portas]

If you mean that A and B mean the same thing, one of
the columns should probably be removed.

I don't mean that. I mean A determines B and B determines A. In that
case the table I proposed would be in Fifth Normal Form and would have
two keys.

I realise that this is can be a difficult discipline, but I'm surprised
that you have more than a few tables with this problem.

There is a much more common example unrelated to my first one. It is
implicit that a table in at least 2NF and having a surrogate key must
also have a natural key. So the requirement to support two keys is
very common indeed and the commonest way to implement keys is to use a
unique index. Perhaps your comments primarily relate to Access? I
don't have much to do with that product but based on my (limited and
therefore prejudiced) experience of Access data models I can only
assert that anything is possible!

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

 

[David Portas]

If you mean that A and B mean the same thing, one of
the columns should probably be removed.

For example: ISO country codes. Country Code determines Country Name.
Country Name determines Country Code.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--