Unique index in linked table
Joe Clarke
column and a text column.
The Primary Key in SQL for the table is on the identity column, but I also
have a unique index on the text column.
When I link the table in Access, the text column is [automatically?]
selected as the primary key, and I can't change it in design mode.
How do I change the tabledef for this linked table to properly identify the
primary key?
TIA,
Joe
Brian Camire
1. In order to be able to update a linked table, Access needs a unique
"pseudo index". A unique pseudo index doesn't create anything in the source
DBMS (SQL Server, in your case), take up any extra space in your Access
database, or even improve query performance. It just tells Access which
fields uniquely identify each record. Access needs to uniquely identify
each record in order to update a linked table.
2. When you link a table, Access (Jet) attempts to define a unique pseudo
index based on the information (if any) provided by the ODBC driver on the
indexes defined on the source table (in SQL Server, in your case). Jet
chooses the first unique index according to a pre-defined order (see the
"How Microsoft Jet Determines If a Remote Table Can Be Updated" topic in the
"Microsoft Jet Database Engine Programmer's Guide" -- check MSDN).
Basically, you can't easily control which pseudo index Jet chooses. To work
around this, you can create views in SQL Server and link to the views
instead of the tables. Views don't have indexes, so Jet can't automatically
define a pseudo index, which leaves you free to define whichever pseudo
index you want. Alternatively, if you link to a table instead of a view and
Jet automatically chooses an index you don't like, you can execute a "DROP
INDEX" SQL statement *in Access* to delete the pseudo index.
3. Once you've linked a view or table, you can define a pseudo index by
executing a "CREATE INDEX" SQL statement in Access.
See the "CREATE INDEX Statement" topic in the Access help.
The following MSDN articles may also help:
http://support.microsoft.com/support/kb/articles/Q112/1/31.asp
http://support.microsoft.com/support/kb/articles/Q209/1/23.ASP
"Joe Clarke" <j...@integra-sys.com> wrote in message
news:uYZW0vGI...@TK2MSFTNGP11.phx.gbl...
Joe Clarke
linked ODBC table, even if there are other indexes on the table in the
source.
In my case, for this table Access defined two indexes: a primary key index
[incorrectly] referencing the text field, and other index referencing the
identity field.
Can I drop both indexes and then only define the one I want?
Joe
"Brian Camire" <bca...@hotmail.com> wrote in message
news:ur2dN4G...@TK2MSFTNGP11.phx.gbl...
Brian Camire
you want, you only need to drop the first index (the primary key). The
second pseudo index doesn't need to be a primary key -- as long as it is
unique, Access can use it to update records.
"Joe Clarke" <j...@integra-sys.com> wrote in message
news:uypdMXJI...@TK2MSFTNGP11.phx.gbl...
Kevin Sun [MS]
207745.KB.EN-US ACC2000: Access May Choose an Unexpected Index as the
Primary Key
http://support.microsoft.com/default.aspx?scid=KB;EN-US;207745
==============
ACC2000: Access May Choose an Unexpected Index as the Primary Key WGID:147
ID: 207745.KB.EN-US CREATED: 1998-11-24 MODIFIED: 2001-03-09
Public |
============================================================================
===
----------------------------------------------------------------------------
---
The information in this article applies to:
- Microsoft Access 2000
----------------------------------------------------------------------------
---
This article applies only to a Microsoft Access database (.mdb).
Moderate: Requires basic macro, coding, and interoperability skills.
For a Microsoft Access 2002 version of this article, see
KBLink:292047.KB.EN-US: .
SYMPTOMS
========
When you link a table from an ODBC data source, such as Microsoft SQL
Server or ORACLE, and that table contains more than one unique index,
Microsoft Access may select the wrong index as the primary key.
CAUSE
=====
When you link a table from an ODBC data source, the Microsoft Jet database
engine makes a call to SQLStatistics, an ODBC API function used to identify
the first unique index to select as the primary key. SQLStatistics returns
index information in the following order: Clustered, Hashed, Non-clustered,
or other indexes. In addition, each index is listed alphabetically within
each group.
NOTE: All indexes created within ORACLE are treated as non-clustered
indexes. Therefore, the order of the index is determined by the name,
rather than by the type.
RESOLUTION
==========
To ensure that the Jet database engine properly selects the desired index
as the primary key when linking the table from your ODBC backend, you can
rename the index so that it appears first alphabetically.
NOTE: When using SQL Server version 6.x, this behavior only occurs if you
are using non-clustered unique indexes.
MORE INFORMATION
================
Steps to Reproduce Behavior
---------------------------
1. In a Microsoft SQL Server utility (such as ISQL/w), run the following
commands:
CREATE TABLE tblPKTest
(
ID int Primary Key Nonclustered,
Fname varchar(50) null,
Lname varchar(50) null,
Field3 varchar(50) null
)
go
CREATE UNIQUE INDEX aaa on tblPKTest(Fname,Lname)
2. Start Microsoft Access and create a new database.
3. On the File menu, point to Get External Data, and then click Link
Tables.
4. In the Link dialog box, click ODBC Databases in the Files of type
box.
5. In the Select Data Source dialog box, click the data source name
that points to your SQL Server database, and then click OK. Supply any
necessary logon information, and click OK.
6. In the Link Tables dialog box, select the tblPKTest table that you
created in Step 1, and then click OK.
7. Open the linked tblPKTest table in Design view; click Yes to the
prompt that you cannot modify all properties of a linked table. Note
that the PrimaryKey is not the ID field as expected, but the combined
index of the Fname and Lname fields.
REFERENCES
==========
The third-party products that are discussed in this article are
manufactured by companies that are independent of Microsoft. Microsoft
makes no warranty, implied or otherwise, regarding the performance or
reliability of these products.
QUERY WORDS
===========
indexes
============================================================================
===
Publishing Keywords : kbAccess2000 kbAccess2000Search kbAccessSearch
Keywords : kb3rdparty kbprb
Revision Type : Major
Workgroup : DS - Access [147]
Billing Product : Access 2000 Win32 English [98573]
Original Language : EN-US
Source Language : EN-US
============================================================================
===
Sincerely,
Kevin
Microsoft Support
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! - www.microsoft.com/security
--------------------
| From: "Brian Camire" <bca...@hotmail.com>
| References: <uYZW0vGI...@TK2MSFTNGP11.phx.gbl>
<ur2dN4G...@TK2MSFTNGP11.phx.gbl>
<uypdMXJI...@TK2MSFTNGP11.phx.gbl>
| Subject: Re: Unique index in linked table
| Date: Thu, 22 May 2003 15:48:15 -0400
| Lines: 96
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <OELSIsJI...@TK2MSFTNGP10.phx.gbl>
| Newsgroups:
microsoft.public.access.odbcclientsvr,microsoft.public.data.odbc
| NNTP-Posting-Host: 64.72.237.19
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.data.odbc:16105
microsoft.public.access.odbcclientsvr:26796
| X-Tomcat-NG: microsoft.public.data.odbc