DOCUMENT:Q149055  11-JAN-2001  [vbwin]
TITLE   :PRB: Jet Doesn't Support QueryDefs on a Non-Attached ODBC Table
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:4.0,6.0
OPER/SYS:
KEYWORDS:kbODBC kbVBp kbVBp400 kbVBp600 kbGrpDSVBDB kbDSupport kbMDAC250

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Learning Edition for Windows, version 6.0 
 - Microsoft Visual Basic Professional Edition for Windows, version 6.0 
 - Microsoft Visual Basic Enterprise Edition for Windows, version 6.0 
 - Microsoft Visual Basic Enterprise Edition for Windows, version 4.0 
-------------------------------------------------------------------------------

SYMPTOMS
========

Jet does not support named QueryDefs on a non-attached ODBC database. A non
attached ODBC database is one that is opened directly with the OpenDatabase
method of the WorkSpace object without the use of an .mdb file.

RESOLUTION
==========

The preferred method for opening an external ODBC table is to attach it to an
.mdb file. For additional information, please see the following article(s) in
the Microsoft Knowledge Base:

   Q150716 : DAO: How To Attach to and Create QueryDefs on ODBC Tables

If this method is not appropriate for your application, the following example
shows how to createQuerydefs with no name:

      Set qd = db.CreateQueryDef("")
      qd.SQL = "Select * from authors"

STATUS
======

This behavior is by design because Jet does not have a place to store the
QueryDef object on the ODBC source.

MORE INFORMATION
================

Steps to Reproduce Behavior
---------------------------

 - If you use the <object>.CreateQueryDef("qdname") method and specify a
   QueryDef name, you get the error 3219 - Invalid operation.

 - If you specify a <object>.Name property, you get error 3251 - Operation
   is not supported for this type of object.

The following information is from Guide To Data Access Objects, Chapter 7, Data
Access Choices:

In many cases, attaching a table is a faster way to access external data than
opening a table directly, especially when the data is located in an ODBC
database. In Visual Basic version 4.0, you can even use SQL Passthrough to
directly query attached ODBC databases. If possible, it's best to consider
attaching external tables rather than opening them directly. If you're using
external data in an ODBC database and your particular situation requires opening
the external tables directly, keep in mind that performance will be
significantly slower when you are using the data.

Sample Program
--------------

The following example describes how to create a Querydef with no name. There is
no need to set up a DSN with the ODBC Admin utility as it uses a "DSN-less" ODBC
connection.

1. Start a new project in Visual Basic. Form1 is created by default. Add two
   command buttons to Form1, command1 and command2 by default.

2. Paste the following code in the General Declarations section of form1:

      Dim db As Database
      Dim cn As String
      Dim qd As QueryDef

      Private Sub Form_Load()
        'open db directly, without attaching
        cn = "odbc;driver={SQL Server};server=myserver;" & _
          "database=pubs;uid=myuid;pwd=mypwd"
        Set db = OpenDatabase(Name:=pubs, Exclusive:=False, _
          ReadOnly:=False, Connect:=cn)
      End Sub
      Private Sub Command1_Click()
        'If we try to name it, we get the following error:
        'Error 3251 "Operation is not supported for this type of object."
        Set qd = db.CreateQueryDef("")
        'If we try to name it like this we get error 3219 "Invalid operation."
        'qd.Name = "abc"
        qd.Connect = cn
        qd.SQL = "Select * from titles"
      End Sub
      Private Sub Command2_Click()
        Dim rs As Recordset
        Set rs = qd.OpenRecordset()
        Call displayResults(rs)
       End Sub
      Sub displayResults(rs As Recordset)
        Dim f As Field, s As String, i As Integer
        For Each f In rs.Fields
          s = s & f.Name
        Next f
        Debug.Print s              'print column headers
       While Not rs.EOF And i < 5
          s = ""
          For Each f In rs.Fields
            s = s & f.Value
          Next f
          Debug.Print s            'print first 5 rows
          rs.MoveNext
          i = i + 1
        Wend
      End Sub

3. Change the DRIVER, SERVER, DATABASE, UID, and PWD in the OpenConnection
   method. Modify the SQL statement contained in the Command1_Click event to
   match the SQL data source.

4. Start the program or press the F5 key. Click on the Command1 button to create
   the Querydef, and command2 to execute the query, which will display the first
   5 rows of data in the debug window.

REFERENCES
==========

Jet Database Engine Programmer's Guide, Microsoft Press, page 323

Additional query words:

======================================================================
Keywords          : kbODBC kbVBp kbVBp400 kbVBp600 kbGrpDSVBDB kbDSupport kbMDAC250 
Technology        : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB600Search kbVBA600 kbVB600 kbVB400Search kbVB400
Version           : WINDOWS:4.0,6.0
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2001.