Creating index on linked SQL View

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

I am writing a procedure to allow me to switch the connection string of the
tables in my Access 2000 database between production server and development
server. So far so good, it is working like it should, except for one thing.

When a SQL Server view is manually linked in Access, there are no indexes or
primary keys on the view, so Access gives you the ability to set the primary
key(s) by displaying a list of fields to you in which you can set the
primary keys.

As most may know, when you refresh the link on such a linked view, access
loses the primary keys that you set when you initially linked the view.
This is also happening when my code changes the connection string of the
linked view.

My question is this: Is there any way to reset, through code, these
indexes?

Thanks in advance.

Alan Sloan
 
You can create a "Fake" index in Access that tells Access which columns make
a Unique record in the linked table. The index is only used by Access is
completely unknown to the Server.

The following example creates an index on an ODBC linked table. The table's
remote database is unaware of and unaffected by the new index. The following
example won't work if you simply paste it into Microsoft Access; you first
need to create an ODBC linked table called OrderDetailsODBC.

(CREATE INDEX Statement in Help has some examples)

CREATE UNIQUE INDEX OrderID ON OrderDetailsODBC (OrderID);
 
Back
Top