Adding Primary Key to Linked Table.

[Ed S.]

Need some expert eyes on adding primary key to a linked table via VBA module. MS Access at version 97.

When following code executed, recieve error message: Run time error'3367':
Can't Append. An object with the name already exists in the collection.


Option Compare Database
Option Explicit
Dim db As Database
Dim tdf As TableDef
Dim strDSNName As String
Dim strTableName As String
Dim Idx As Index
Dim Fld As Field
'
Sub BuildTableLink()

Set db = CurrentDb
Set strTableName = "NewTable"
'
Set tdf = db.CreateTableDef(strTableName)
tdf.Connect = "ODBC;DSN="DSN01";;TABLE=" & strTableName & ""
'
tdf.SourceTableName = "ExternalTableName"
'
db.TableDefs.Append tdf
'
Set Idx = tdf.CreateIndex("PrimaryKey")
Set Fld = tdf.Fields("FldA")
Idx.Primary = True
Idx.Unique = True

* * * * Folling line Failes * * * *
Idx.Fields.Append Fld
tdf.Indexes.Append Idx
'
End Sub

Watch variable indicates 0 objects for Fields in Idx collection???

 

[Anne Troy]

To my knowledge, you CANNOT CHANGE the design of a linked table, VBA or no.

 

[Ed S.]

Thank you for your reply DreamBoat.

You are correct, I attempted to add a primary key to a Table's field in Design mode. Linked Tables do not allow modification to this property.

However, linked tables do allow primary keys to be added at the time the Table is created (very easy process using the link wizard).

So, my first issue is can a primary key be appended at the time the Table is Linked using VBA code? Not modification after the table is linked.

Second issue is, if linked table does allow primary key to be built at link time via VBA code, would appreciate some expample code or reference material to research.

 

[Corticus]

Hi,

I do not believe that you can edit the form of a table through VBA, before or after you have linked to it. Linking to a table can give you the ability to edit data within the table, and delete and add records, but not change the form of the table.

I think editing on this level must be done through the database file in which the table was built, and the file would not be able to be shared until the editing was completed.

Of course, I could be wrong

HTH,