Creating Unique Record Identifier for a SQL View .. codes?

[FuzMic]

Up the learning Curve series .. .mdb ODBC link to SQL2k/SQLEXPRESS server

Situation
1) A .mdb needs to refresh link to a "VIEW" in another DNS, set to an .mdf in SQLSrv.
2) Using GUI the link is straight forward to link to a unique field in the VIEW so that the latter is updatable. This will manifest as "_uniqueindex" in the Indexes collection using TableDef (same manifestion when you browse the View in Table Design via Access)

Issue & Tinkering
1) On the link.refresh using TableDef codes, the index don't exist
2) Tried using TableDef namely .. CreateIndex, CreateField, Append
3) Tried using SQL Pass-Through Query that has working ODBC Connect String.
The Query is as follows
CREATE UNIQUE INDEX NewIndex ON ViewName ([UniqueKeyField]);
But ODBC fail call .. not Schema Bound. After setting VIEWwith SBound in the SQL Srv, the next fail call
mention that there is no "unique Cluster Index". The VIEW as it is, is fine using GUI to set the Unique
Record Identifier.

.. hence needs rapid help.

Question
How to use codes to create the unique field so that the VIEW is updateable. Thanks in Advance.

 

[FuzMic]

Solution but new update error

Continuation.. After tinkering, found a resolution to the start of this thread

As long as the tables in the VIEW does not have the following:
1 fields with data type of floating & real; changed to decimal (Ms kb exist for this point)
2 no wild card selection *.* of the whole table in the VIEW ie only select the necessary fields
3 timestamp field in table (not selected in VIEW) (again Ms kb exist)

We can then create a index within SQLSrv using Manage Indexes.

Once an index is so created, during the the ODBC linking from a FE .mdb, the following is true
1 will not ask for unique Record Identifier and
2 the View becomes updateable within the FE .mdb

But .. new issue
Even though the View is updateable there is update error; error message relates to SET ARITHABORT issue. Currently no inkling on how to resolve this. Any rapid help appreciated.

 

[FuzMic]

Period: Set Arithabort Resolved

Once "SET ARITHABORT ON", there is no more issue of updating the View. The setting can be done by

1. Using a PassThrough Query with codes SET ARITHABORT ON with the connection properties set to the DNS that describe the connection to the .mdf database

2. Use VBA codes with the FE .mdb, codes snips as follows
Dim qdf as QueryDef
Dim CDb as DAO.database

Set CDB = Currentdb()
Set qdf = CDb.CreateQueryDef("")
qdf.Connect = "..connection string.."
qdf.SQL = "SET ARITHABORT ON"
qdf.ReturnsRecords = False
qdf.Execute dbSQLPassThrough

Guess this thread should come a close; meanwhile still facing issues in the .mb link to SQL2k learning curve.