When I link to a SQL table from the Access File-->External Data etc it gives
me a chance to designate the Primary Key. That allows the linked table to be
updatable and insertable, without the key it is read only.

What I can't seem to figure out is how to add an index to a linked table
when I'm doing it programmatically. The table gets linked without the line
    tbl.Keys.Append objKey
and fails with it.

Thanks for any help on this.

   Dim Con As New ADODB.Connection
   Dim Cat As New ADOX.Catalog
   Dim tbl As ADOX.Table
   Dim objKey As ADOX.Key
   Dim remotedbpath As String
   Dim bol_Ret As Boolean
   
   On Error GoTo errEH:
   
   bol_Ret = True
   
   'Hardcode path to SQL backend: only a DSN
   remotedbpath = "ODBC;DSN=amkSQL;Trusted_Connection=Yes;DATABASE=aMK;"
   
   With Con
      .CursorLocation = adUseClient
      .Provider = "Microsoft.Jet.OLEDB.4.0;"
      .Open "Data Source=" & Application.CodeDb.Name & ";"
   End With
   
   Cat.ActiveConnection = Con
   
   Set tbl = New ADOX.Table
   With tbl
       Set .ParentCatalog = Cat
       .Name = pstr_ACCESSLINKEDTableName
       .Properties("Jet OLEDB:Create Link").Value = True
       .Properties("Jet OLEDB:Link Provider String").Value = remotedbpath
       .Properties("Jet OLEDB:Remote Table Name").Value = "dbo." &
pstr_SQL_Table
       .Properties("Jet OLEDB:Cache Link Name/Password").Value = False

       'This fails, will not link with an index.
       If pstr_KeyField <> "" Then
           Set objKey = New ADOX.Key
           objKey.Name = "__uniquekey"
           objKey.Type = adKeyPrimary
           objKey.Columns.Append pstr_KeyField
           tbl.Keys.Append objKey

       End If
   End With
   ' Append the table to the Tables collection.
   Cat.Tables.Append tbl