When using the SQL Server table/view linking tool built in to Access, you can select a column to be a primary key for a View to make the data results editable but when linking SQL Server Views via VBA code at run-time, you need to use CREATE INDEX statements.
After you use the manual linking tool and select keys, you can then use some code to create the CREATE INDEX stmts and then add those to your “RefreshLinks” (or whatever you name yours) VBA module code.
For Each td In CurrentDb.TableDefs
If Left(td.NAME, 3) = “vw_” Then ‘(or whatever View naming convention is used to get the views)
‘Get unique index on view if there is one.
If td.Indexes.Count > 0 Then
Set idx = td.Indexes(“__uniqueindex”)
If Err.Number = 0 Then
ReDim aryCols(idx.Fields.Count – 1)
For x = 0 To UBound(aryCols)
Debug.Print “CurrentDb.Execute (“”CREATE UNIQUE INDEX ” & idx.Fields(x).NAME & “Index ON ” & td.NAME & ” (” & idx.Fields(x).NAME & “) WITH PRIMARY””)”
Next
Else
Err.Number = 0
End If
End If
End If
Next td
The above code will give you a list of CREATE INDEX statements in the Access Immediate Window that you can then copy/paste to your VBA linking module.
CurrentDb.Execute (“CREATE UNIQUE INDEX MyIndexName_Index ON MySQLServerViewName (MyIndexName_Index) WITH PRIMARY”)