automate primary key assignment for linked SQL tables in Access 97

[1manedp]

I have a SQL 2000 backend with an Access 97 front-end and want to better control data access with SQL views. SQL Server 2000 allows one to insert data using a view, provided a unique identifier is specified when the link is created.

I have written a vb script that automates the linking process (using tabledefs and the connect property) for all my tables and views, but I cannot find where in Access I can specify the primary key for a linked view outside of manually linking each one, which is not an option. I have spent some time in Help and searching the web. [COLOR=#04041ff0]If such a property exists, please let me know. [/color]

Failing that, I suspect I will have to use views only for browsing and run a stored procedure for inserts, which will complicate the project.

Thanks in advance,
-Steve

 

[jryan224]

Have you looked at the CREATE INDEX Statement in ACCESS 97? With it you can set a primary key. I have used it to set primary keys in Access 97 tables but I have not tried it with SQL 2000. Worth looking at.