Can you really give a index on the Ms Access View ?
I'm attempting to run this question:
CREATE INDEX MyCustomViewIndex ON [MyView] (MyColumn) WITH DISALLOW NULL
and i'm getting this error message.
Cannot execute data definition claims on linked data sources.
My View definition:
create view MyView as SELECT TableA.Field1, MyUnionQuery.Field2 FROM TableA, MyUnionQuery
Select * from ViewX UNION select * from ViewY
All of the tables have been in exactly the same MDB file
As always for Access (ACE, Jet, whatever) the documentation is vague: it discusses 'tables' not to mention a
VIEW is really a 'viewed table' however i think within this situation it particularly describes 'base tables'. I strongly suspect indexes aren't supported for Access
After I attempt to create one using SQL DDL I too obtain the error, "Cannot execute data definition claims on linked data sources." Here's some VBA code to breed the mistake (produces a brand new .mdb in temp folder, no references needed just paste right into a VBA code module e.g. in Stand out):
Sub NoIndexedViews() On Error Resume Next Kill Environ$("temp") & "\DropMe.mdb" On Error GoTo 0 Dim cat Set cat = CreateObject("ADOX.Catalog") With cat .Create _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & _ Environ$("temp") & "\DropMe.mdb" With .ActiveConnection Dim Sql As String Sql = _ "CREATE TABLE T (col1 INTEGER);" .Execute Sql Sql = _ "CREATE VIEW V (col1) AS SELECT col1 FROM T;" .Execute Sql On Error Resume Next Sql = _ "CREATE INDEX idx ON V (col1) WITH DISALLOW NULL" .Execute Sql MsgBox Err.Description End With Set .ActiveConnection = Nothing End With End Sub
I do not have any linked data sources during my .mdb and so i conclude the mistake being proven is spurious and misleading. But the reality is, a catalog can't be produced with an Access
Based on the error message it's a linked databases. This means that the table isn't inside access but linked in.
Since Access doesn't control the table it can't perform the asked for operataion.
Where's the table the data originates from?