MS Access Indexed View

Is it possible to add a index on a Ms Access View开发者_如何学Go ?

I am trying to run this query:

CREATE INDEX MyCustomViewIndex ON [MyView] (MyColumn) WITH DISALLOW NULL

and I am getting this error message.

Cannot execute data definition statements on linked data sources.

Any suggestions?

Regards, Alex

EDIT:

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 the tables are in the same MDB file

As usual for Access (ACE, Jet, whatever) the documentation is vague: it talks about 'tables' and of course a VIEW is a 'viewed table' but I think in this case it specifically refers to 'base tables'. I strongly suspect indexes are not supported for Access VIEWs.

When I try to create one using SQL DDL I too get the error, "Cannot execute data definition statements on linked data sources." Here's some VBA code to reproduce the error (creates a new .mdb in temp folder, no references required just paste into a VBA code module e.g. in Excel):

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 don't have any linked data sources in my .mdb so I conclude the error being shown is spurious and misleading. But the fact remains, an index cannot be created on an Access VIEW.

According to the error message it is a linked data source. That means that the table is not inside access but linked in.

Since Access does not control the table it cannot do the requested operataion.

Where is the table that the data comes from?