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

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:

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.