Re: Clustered index=PK not returned by OpenSchema

From: Douglas J. Steele (NOSPAM_djsteele_at_NOSPAM_canada.com)
Date: 03/26/04 

Date: Fri, 26 Mar 2004 07:37:37 -0500

I don't believe Jet uses Clustered Indexes, so it doesn't surprise me that
you're always seeing the property as being False. 

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)
"onedaywhen" <onedaywhen@fmail.co.uk> wrote in message
news:b8c9d0b7.0403250900.416ef4be@posting.google.com...
> No takers? Let me try again.
>
> Consider this code, executed from a MS Access module within a new
> blank database (questions follow):
>
> '<Code>--------
> Option Explicit
>
> Sub Test()
> Dim Con As ADODB.Connection
> Dim rs As ADODB.Recordset
> Set Con = CurrentProject.Connection
> With Con
>   On Error Resume Next
>   .Execute "DROP TABLE Table1"
>   On Error GoTo 0
>   .Execute "CREATE TABLE Table1" & _
>            " (Col1 INTEGER PRIMARY KEY)"
>   Set rs = .OpenSchema(adSchemaIndexes)
>   If Not rs.EOF Then
>     MsgBox rs!INDEX_NAME & _
>              ": PK=" & CStr(rs!PRIMARY_KEY) & _
>              ", Clustered=" & CStr(rs!Clustered)
>   End If
> End With
> End Sub
> '</Code>-------
>
> 1. Am  I correct in assuming the primary key will be the clustered
> index for the table?
> 2. If so, does anyone know why the OpenSchema shows PK=True and
> Clustered=False?
>
> Many thanks.
>
> --
>
> onedaywhen@fmail.co.uk (onedaywhen) wrote in message
news:<b8c9d0b7.0403240654.476a8346@posting.google.com>...
> > I'm using ADO's OpenSchema method with adSchemaIndexes on a Jet 4.0
> > .mdb database. For each index found, in the resulting recordset the
> > CLUSTERED field is 'false' even when the PRIMARY_KEY field is 'true'.
> > It is my understanding that for Jet a table's primary key is always
> > the clustered index (I wish I was wrong on this one). Can anyone shed
> > any light on this discrepancy e.g. is this known behavor of the OLE DB
> > provider for Jet?
> >
> > Many thanks.
> >
> > --

Relevant Pages

  • Re: Student Project
    ... Jet 4 also works with Access 2002 and Access 2003. ... Doug Steele, Microsoft Access MVP ... > I don't know what version of Provider handles 2002, ... > Phillip Windell ...
    (microsoft.public.vb.database)
  • Re: Trouble with DAO "SEEK" in converting application to SQL Express back end.
    ... "Rico" wrote ... Jet tables in the same MDB/MDE. ... Server DB, to include criteria in your Query or SQL ... Microsoft Access MVP ...
    (comp.databases.ms-access)
  • Re: TreeView Control will not work
    ... Jet OLEDB:System database=C:\Documents and Settings\ES\Application ... Doug Steele, Microsoft Access MVP ... then select Tools | References from the menu bar. ... then go back in and unselect the reference you just added. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Query to delete records that are before 90 days from today
    ... It might be marginally faster to use ... I think Jet is smart enough to know that it only has to figure out Date-90 ... Doug Steele, Microsoft Access MVP ... >> I need to delete all records before Today minus 90, ...
    (microsoft.public.access.modulesdaovba)
  • Re: How to make updatable subform
    ... your example is with JET and not with SQL-Server - the subject ... nothing and is outside of the topic of this newsgroup is a better example. ... Dim con As ADODB.Connection ... If .RecordCount = 1 Then ...
    (microsoft.public.access.adp.sqlserver)