Can you really give a index on the Ms Access View ?

I'm attempting to run this question:


and i'm getting this error message.

Cannot execute data definition claims on linked data sources.

Any suggestions?

Regards, Alex


My View definition:

create view MyView as SELECT TableA.Field1, MyUnionQuery.Field2
FROM TableA, MyUnionQuery 

MyUnionQuery definition:

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 VIEWs.

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 = _
      .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 VIEW.

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?