How to programmatically create an index on a linked table

[Martin Rueegg]

I link a table to a view on the SQL Server. To make it updatable in access
there is a need for a primary index on the linked table. When linking the
table through Linked Table Manager I will be asked to specify the fields for
that primary key.

Now I want to create the link through VBA using CreateTableDef with a
connect parameter. So far so good. Unfortunately I'm not able to create an
Index with CreateIndex - or more precise, I'm not able to append it to the
TableDef.

When trying to append the index to the TableDef after the TableDef has been
appended to the database I get the error 3057 (= Operation not supported for
linked tables).

When trying to append the TableDef to the database *after* having appended
the index to the TableDef.Indexes then I get the error 3039 (= Index could
not be created; too many indexes already defined). This even if there is no
Index in the Indexes collection of the TableDef object after appending it to
the Database.

Sounds kind of wired. To explain it more clearly I'd like to explain the
what I observed:

a) creating a link to a table with no index
a.1) SET td = CreateTableDef("Name",,"SourceTableName","ConnectString")
a.2) CurrentDb.TableDefs.Append td
a.3) td.Indexes.Count = 0

b) creating a link to a table with an index
b.1) SET td = CreateTableDef("Name",,"SourceTableName","ConnectString")
b.2) CurrentDb.TableDefs.Append td
b.3) td.Indexes.Count = 1

c) creating a link to a view with no index
c.1) SET td = CreateTableDef("Name",,"SourceTableName","ConnectString")
c.2) CurrentDb.TableDefs.Append td
c.3) td.Indexes.Count = 0
-> now the table is not updatable

d) creating a link to a view with no index
d.1) SET td = CreateTableDef("Name",,"SourceTableName","ConnectString")
d.2) CurrentDb.TableDefs.Append td
d.3) SET idx = td.CreateIndex("Name") 'And Append Fields to Index
d.4) td.Indexes.Append idx
-> error 3057 (= Operation not supported for linked tables)

e) creating a link to a view with no index
e.1) SET td = CreateTableDef("Name",,"SourceTableName","ConnectString")
e.2) SET idx = CreateIndex("Name") 'And Append Fields to Index
e.3) td.Indexes.Append idx
e.3) CurrentDb.TableDefs.Append td
-> error 3039 (= Index could not be created; too many indexes already defined)

Is there an other way to create the index on the view form the access side
or do I need to create the index on the server view?

thanks for your comments,
Martin.

[Martin Rueegg]

ok, i found a workaround:

according to http://support.microsoft.com/kb/209123/EN-US/ it *is* possible
to create an index after having appended a linked TableDef to the database.

so instead of creating an index using the object model I now just run an
sql-query creating the required index:

f) creating a link to a view with no index
f.1) SET td = CreateTableDef("Name",,"SourceTableName","ConnectString")
f.2) CurrentDb.TableDefs.Append td
f.3) CurrentDb.Execute "CREATE UNIQUE INDEX IndexName ON TableLocalName
(Field, List)
-> now the query is updateble!

hope this helps others ...

best regards,
martin.