KnowledgeBase Archive

An Archive of Early Microsoft KnowledgeBase Articles

View on GitHub

Q149055: PRB: Jet Doesn’t Support QueryDefs on a Non-Attached ODBC Table

Article: Q149055
Product(s): Microsoft Visual Basic for Windows
Version(s): WINDOWS:4.0,6.0
Operating System(s): 
Keyword(s): kbODBC kbVBp kbVBp400 kbVBp600 kbGrpDSVBDB kbDSupport kbMDAC250
Last Modified: 11-JAN-2001

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