Q150716: HOWTO: DAO: Attach to and Create QueryDefs on ODBC Tables
Article: Q150716
Product(s): Microsoft Visual Basic for Windows
Version(s): WINDOWS:2.5,4.0
Operating System(s):
Keyword(s): kbODBC kbVBp kbVBp400 kbGrpDSVBDB kbDSupport kbMDAC250
Last Modified: 02-MAR-2000
-------------------------------------------------------------------------------
The information in this article applies to:
- Microsoft Visual Basic Enterprise Edition, 32-bit, for Windows, version 4.0
- Microsoft Data Access Components version 2.5
-------------------------------------------------------------------------------
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:
Q149055 : 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.
1. Start a new project in Visual Basic. Form1 is created by default.
2. Add three Command buttons to Form1: Command1, Command2, Command3 by default.
3. 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.
REFERENCES
==========
"Jet Database Engine Programmer's Guide", page 323, published by Microsoft
Press.
Additional query words:
======================================================================
Keywords : kbODBC kbVBp kbVBp400 kbGrpDSVBDB kbDSupport kbMDAC250
Technology : kbVBSearch kbAudDeveloper kbMDACSearch kbVB400Search kbVB400 kbMDAC250
Version : WINDOWS:2.5,4.0
Issue type : kbhowto
=============================================================================
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.
Copyright Microsoft Corporation 1986-2002.