PRB: Jet Doesn't Support QueryDefs on a Non-Attached ODBC Table (149055)

   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.

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.