Microsoft KB Archive/150716

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

Article ID: 150716

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft Visual Basic 4.0 32-Bit Enterprise Edition
 * Microsoft Data Access Components 2.5

-



This article was previously published under Q150716



SUMMARY
This article describes how to attach and create Querydefs on external ODBC tables. The method for opening external ODBC tables is to attach the tables to an .mdb file.

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.

If it is not appropriate for the application to attach the ODBC tables, it is possible to create Querydefs with no name to accomplish the procedure.

For additional information, please see the following article in the Microsoft Knowledge Base:

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



MORE INFORMATION
The following is information from "Guide To Data Access Objects," Chapter 7, Data Access Choices, that explains this procedure:

In many cases, attaching a table to access external data is faster than opening a table directly, especially if the data is located in an ODBC database. In Visual Basic version 4.0, SQL Passthrough is used to query attached ODBC databases directly. Consider attaching external tables rather than opening them directly. Using external data in an ODBC database requires opening the external tables directly so performance is significantly slower when using the data.

Sample Program
The following example describes how to attach to and create a Querydef on an ODBC table using a "DSN-less" ODBC connection. With this procedure, it is not necessary to set up a DSN with the ODBC Admin utility.

 Start a new project in Visual Basic. Form1 is created by default. Add three Command buttons to Form1: Command1, Command2, Command3 by default.  Paste the following code in the General Declarations section of Form1: Dim db As Database Dim cn As String Private Sub Form_Load cn = "odbc;driver={SQL Server};server=myserver;" & _ "database=pubs;uid=myuid;pwd=mypwd" If Dir("mydb.mdb") <> "" Then ' database exists, so just open it. Set db = OpenDatabase(Name:="mydb", Exclusive:=False, _           ReadOnly:=False, Connect:="") Else 'database does not exist, create it and attach authors table. Set db = CreateDatabase(Name:="mydb", Connect:=dbLangGeneral, _           Option:=dbVersion30) Dim td As TableDef Set td = db.CreateTableDef td.Name = "Authors" td.SourceTableName = "Authors" td.Connect = cn       End If      End Sub Private Sub Command1_Click Dim qd As QueryDef On Error Resume Next Set qd = db.QueryDefs("abc") ' test for existence of querydef. If Error > 0 Then Set qd = db.CreateQueryDef(Name:="abc") qd.Connect = cn         qd.SQL = "Select @@Version" 'native SQL Server End If       Set qd = db.QueryDefs("xyz")  ' test for existence of querydef. If Error > 0 Then Set qd = db.CreateQueryDef(Name:="xyz") qd.Connect = cn         qd.SQL = "Select * from titles" ' generic SQL. End If       On Error GoTo 0 End Sub Private Sub Command2_Click Dim rs As Recordset Dim qd As QueryDef Set qd = db.QueryDefs("abc") Set rs = qd.OpenRecordset Call displayResults(rs) End Sub Private Sub Command3_Click Dim rs As Recordset Dim qd As QueryDef Set qd = db.QueryDefs("xyz") 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



NOTE: You need to change the DRIVER, SERVER, DATABASE, UID, and PWD parameters in the OpenConnection method. Also you must modify the SQL statements contained in the Command1_Click event to match your SQL data source.


 * 1) Press the F5 key to start the program.
 * 2) Click the Command1 button to create the Querydefs. Click the Command2 and Command3 buttons to execute the Querydefs. Note that the first five rows of data appear in the Debug window.

