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

• Enterprise Edition of Microsoft Visual Basic version 4.0, for Windows

SUMMARY

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.

STATUS

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

WORKAROUND

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:

   ARTICLE-ID: Q150716
   TITLE     : DAO: How To Attach to and Create QueryDefs on ODBC Tables

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

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.

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