[AccessD] Where is the PK info stored?

 Not really sure, but when you link the external table, JET tries to figure
out the best way to talk with the table.  I would imagine that modifying the
connect property is being viewed as a new link and it goes through that
process again.  But why it doesn't end up the same way I can't tell you.

  I'm not sure what it is your doing that's causing the problem, but as part
of your process after your done re-linking, maybe you should take Lambert's
code, identify the PK index, Drop it, then use an Create Index command,
i.e.:

DROP INDEX CategoriesCategoryName ON Categories;

CREATE UNIQUE INDEX LinkCategoryID ON Categories (CategoryID);

To force the field(s) choice in the index.  Note that this doesn't affect
the table in SQL, your just modifying how the table is described on the
Access side.

You can do this as well too when JET makes an incorrect choice about the
fields to be used for the index.

Jim.

 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, February 29, 2012 02:17 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Where is the PK info stored?

Jim, Lambert,

And so why does this index get stripped off when I modify the connect
property of the tabledef?  It 
looks to be readonly, i.e. once stripped off I cannot rebuild it.

John W. Colby
Colby Consulting

Reality is what refuses to go away
when you do not believe in it

On 2/29/2012 1:02 PM, Heenan, Lambert wrote:
>   Right. The index on the table has a 'Primary' property that is true or
false
>
> Here's a sample function that returns *one* primary key field. You'll to
modify it for tables with compound primary keys.
>
> Lambert
>
> Function GetPKName(strTable As String) As String
> Dim db As DAO.Database
> Dim curTbl As TableDef
> Dim cntKey As Integer
> Dim curIdx As Index
> Dim cntIdx As Long
> Dim curIdxFld As Field
> Dim curFld As Field
> Dim cntFlds As Long
>
>      Set db = CurrentDb
>      Set curTbl = db.TableDefs(strTable)
>      For cntFlds = 0 To curTbl.Fields.Count - 1
>          For cntKey = 0 To curTbl.Indexes.Count - 1
>              Set curIdx = curTbl.Indexes(cntKey)
>              '** Loop though all the fields in the current index
>              For cntIdx = 0 To curIdx.Fields.Count - 1
>                  Set curFld = curTbl.Fields(cntFlds)
>                  Set curIdxFld = curIdx.Fields(cntIdx)
>                  '* is the current field part of the primary key?
>                  If (curFld.Name = curIdxFld.Name) And (curIdx.Primary =
True) Then
>                      GetPKName = curIdxFld.Name
>                  End If
>              Next cntIdx    ' move to next field in the current index
>          Next cntKey    ' move to next index in current table
>      Next cntFlds
> End Function
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
> Sent: Wednesday, February 29, 2012 12:47 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Where is the PK info stored?
>
>
> <<Does anyone know where Access hides this PK field info?>>
>
>    It stores it as an index as part of the tabledef.
>
> Jim.
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Wednesday, February 29, 2012 11:11 AM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Where is the PK info stored?
>
> When I link to a view in SQL Server it stops and asks what field is the
PK.
> If you go into the
> "table definition" in the database window, that field is set as the PK.
> Having a PK is required to
> allow the "table" (linked view) to be editable.  Somehow (not sure yet
how) I have managed to strip out that information, and suddently editable
things become uneditable.  I relink the view, telling it which field is the
PK and it is back editable again.
>
> I am trying to determine how I am mangling this thing but knowing where
Access places this info would help me in understanding the issue.
>
> Does anyone know where Access hides this PK field info?
>
> --
> John W. Colby
> Colby Consulting
>
> Reality is what refuses to go away
> when you do not believe in it
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com