PRB: Views Created Using ADOX Are Not Visible in Access 2000

ID: Q246213


The information in this article applies to:
  • ActiveX Data Objects (ADO), versions 2.1, 2.1 SP1, 2.1 SP2, 2.5


SYMPTOMS

When using Microsoft ActiveX Data Objects (Microsoft ADO Ext. 2.1 for DDL and Security) to Append a new query (View) to an Access 2000 database, the query icon does not appear among the database queries when you start Access 2000. However, if you iterate through the Views collection or the Tables collection, it is listed.


CAUSE

At this time, Microsoft Access 2000 does not expose the interface that makes these views visible or available outside of its form level.


RESOLUTION

The only way to have new queries or views visible at this time is to do the following:

Use Access 2000 user interface to create the query.
-or
Use DAO to create the queries.
You can also type the view name directly into an SQL statement or a Form, Report, List Box, or Combo Box RecordSource. Microsoft Access then uses it, but the query may not operate correctly; see the More Information section for details.


STATUS

This behavior is by design.


MORE INFORMATION

Microsoft Jet database engine 4.0 has both a legacy mode, that is used by DAO and Microsoft Access 2000, and an ANSI standard SQL-92 mode, which is used by ADO. Microsoft Access 2000 hides ANSI mode queries from the user because differences may make the queries operate incorrectly when the Microsoft Jet database engine is running in legacy mode.

Please check the Wild Card Characters and Access 2000 and Legacy Application Compatibility sections of the following Knowledge Base article for more details: For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

Q225048 INFO: Issues Migrating from DAO/Jet to ADO/Jet

Steps to Reproduce Behavior

  1. Start Visual Basic 6.0 and select Standard EXE project. Form1 is created by default.


  2. Using the Project and References menu, make a reference to the following type libraries:
    Microsoft ActiveX Data Objects 2.1 Library
    Microsoft ADO Ext. 2.1 for DDL and Security

    or

    Microsoft ActiveX Data Objects 2.5 Library
    Microsoft ADO Ext. 2.5 for DDL and Security


  3. Add three command buttons (Command1, Command2, Command3) and the following code to the default form:
    
    Option Explicit
    
    Dim cat As ADOX.Catalog
    Dim i as Integer
    
    Private Sub Command1_Click()
    Dim cmd As ADODB.Command
    Dim vw As ADOX.View
    Dim tbl As ADOX.Table
    
      Set cmd = New ADODB.Command
      cmd.CommandType = adCmdText
      cmd.CommandText = "SELECT * FROM orders"
        
      cat.Views.Append "AllOrders", cmd
        
      Set vw = cat.Views.Item("AllOrders")
      Set cmd = vw.Command
    
      Debug.Print vw.Name
      Debug.Print cmd.CommandText
        
      Set cmd = Nothing
            
    End Sub
    
    Private Sub Command2_Click()
      Debug.Print "Views Collection:"
      For i = 0 To cat.Views.Count - 1
        Debug.Print cat.Views.Item(i).Name
      Next
      Debug.Print "======End Views collection======="
    End Sub
    
    Private Sub Command3_Click()
      Debug.Print "Tables Collection:"
      For i = 0 To cat.Tables.Count - 1
        Debug.Print cat.Tables.Item(i).Name
      Next
      Debug.Print "======End Tables collection======="
    End Sub
    
    Private Sub Form_Load()
      Command1.Caption = "Create / Append new View"
      Command2.Caption = "Display Views Collection"
      Command3.Caption = "Display Tables collection"
        
      Set cat = New ADOX.Catalog
      ' Change the connection string as appropriate
      cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND.MDB;"
    
    End Sub
    
    Private Sub Form_Unload(Cancel As Integer)
      Set cat = Nothing
    End Sub 
    Change the connection path to point to Nwind.mdb on your computer.


  4. Run the project.


  5. If you click Create / Append new View, the immediate window displays the name of the view AllOrders followed by the query text Select * from Orders.


  6. If you click on Display Views Collection or Display Tables collection buttons, the names of the views and/or the names of the Tables are shown and AllOrders is in both.


  7. Use Access 2000 to open the Nwind.mdb file and click Queries then notice that the new view AllOrders is not listed.



REFERENCES

For more information, please refer to the following Knowledge base article: For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

Q225895 ACC2000: ADO Includes Queries and Views in the Tables Collection

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Hussein Abuthuraya, Microsoft Corporation

Additional query words:

Keywords : kbJET kbGrpVBDB kbGrpMDAC kbDSupport kbMDAC250
Version : WINDOWS:2.1,2.1 SP1,2.1 SP2,2.5
Platform : WINDOWS
Issue type : kbprb


Last Reviewed: December 30, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.