Microsoft KB Archive/149055

= PRB: Jet Doesn't Support QueryDefs on a Non-Attached ODBC Table =

Article ID: 149055

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 4.0 Enterprise Edition

-



This article was previously published under Q149055



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:

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



Steps to Reproduce Behavior

 * If you use the .CreateQueryDef("qdname") method and specify a QueryDef name, you get the error 3219 - Invalid operation.
 * If you specify a .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.

 Start a new project in Visual Basic. Form1 is created by default. Add two command buttons to Form1, command1 and command2 by default.  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

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

