What is ODBCDirect?
	Why should I use ODBCDirect?
	When should I or shouldn't I use ODBCDirect?
	How do I refer to ODBCDirect in my application so that I don't load the Jet Engine?
	How do I establish a connection to SQL Server using ODBCDirect?
	What is the difference between the "Server Side" cursor and the "ODBC" cursor?
	I am using Excel and want to fill list boxes with information stored in SQL Server. How can I do this using ODBCDirect?
	What is the NextRecordset method and why should I use it?
	I need to create a recordset from a stored procedure in SQL Server. The stored procedure I am using requires a parameter. Can I still use this stored procedure, passing it a parameter to create my recordset?

What is ODBCDirect?
Keywords: ODBCDirect Workspace Object
Posted Apr 30, 1997

With DAO 3.5 comes a new client/server connection mode via the ODBCDirect Workspace object. ODBCDirect provides high-speed, server-side access directly to an ODBC-compliant database without loading the Microsoft Jet database engine into memory. It does this by providing a thin programming layer over ODBC drivers. This allows you to handle remote data sources using the familiar DAO object model. All this is accomplished because RDO 2.0 ships with Office 97 and Microsoft Access. Microsoft Access exposes RDO's functionality through ODBCDirect.

Why should I use ODBCDirect?
Keywords: ODBCDirect
Posted Apr 30, 1997

ODBCDirect offers the following advantages for ODBC operations:

• ODBCDirect can make your code faster and more efficient by providing direct access to ODBC data sources. Since it doesn't require loading the Microsoft Jet database engine, ODBCDirect consumes fewer resources on the client side. The ODBC server is responsible for all query processing.
• ODBCDirect gives you improved access to server-specific features that aren't available by using ODBC through Microsoft Jet. For example, for servers that support cursor specification, ODBCDirect allows you to specify where cursors are located, whether locally or on the server. In addition, to interact with stored procedures at the server level, you can specify input values and check return values, which you can't do when using Microsoft Jet.
• ODBCDirect also supports asynchronous queries. When you execute a query, you don't have to wait for the query to finish running before you begin another operation. You can track the query's execution by checking the StillExecuting property.
• ODBCDirect supports batch updating, enabling you to cache Recordset object changes locally and then submit these changes to the server in a single batch.
• With ODBCDirect, you can create simple cursorless result sets, or more complex cursors. You can also run queries that return any number of result sets. You can limit the number of rows returned and monitor all the messages and errors generated by the remote data source without affecting the performance of the executing query.

When should I or shouldn't I use ODBCDirect?
Keywords: ODBCDirect ISAM RDO
Posted Apr 30, 1994

ODBCDirect should be used just as you would use RDO or the ODBC API, with all server-based DBMS (Database Management Systems). It provides a familiar object model with which to program, and since it is only a very thin programming layer over the ODBC drivers, its performance is comparable to RDO or ODBC API. Remember that the whole purpose of ODBCDirect was to eliminate overhead, consume fewer resources and provide a familiar object model.

On the other hand, ODBCDirect should not be used with ISAM (Indexed Sequential Access Method) formats such as FoxPro, Dbase, Access, text and spreadsheets. The Microsoft Jet Engine provides unique capabilities that aren't available through ODBCDirect.

• Establishing an ODBCDirect connection to an ISAM database will normally take as long as, if not longer than when using a Jet Engine and the OpenDatabase method. This is because Jet is optimized to access ISAM databases (especially Access). With ODBCDirect, communication must be established with the ODBC Manager, which in turn will query the ODBC driver for a list of its capabilities and then make a connection with the database.
• Using ODBC against an ISAM database involves the use of single-tiered ODBC drivers. These drivers do not pass the SQL to the backend for processing, because there is no back end server running. In such a single-tiered implementation, the database file is processed directly by the driver. In other words, the driver processes all the ODBC and SQL calls. The downfall to this is that these drivers support a minimum set of SQL statements, so much of the native SQL will not work.
• You don't save resources when using ODBCDirect against ISAM databases. If you load the Jet Engine (via DAO), you don't load the ODBC components such as the ODBC Driver Manager and ODBC driver. Whereas with Server-based datasources such as SQL Server or Oracle, you have to load the ODBC components no matter what (unless to write directly to a proprietary API or DBLib).
• Using ODBCDirect severely limits what you can do with the database and what objects are available to you. For example, you can't loop through the object collections such as querydefs, tabledefs, forms or reports. Running parameter queries requires the same ODBC call and Parameter Marker syntax that you would use with SQL Server.
• You must use a Microsoft Jet workspace to update data in Recordset objects based on multiple-table joins.
• You cannot execute multiple Select statements against Microsoft Access databases (or most other ISAMs) because they do not support it. However, most Server based DBMSs (SQL Server, Oracle) do support multiple Select statements, thereby allowing the use of the NextRecordset method to retrieve multiple result sets.
• You cannot create or modify tables through the DAO object model when using ODBCDirect. You must use Data Definition Language (DDL) operations to perform those functions. This is because ODBCDirect doesn't provide a TableDef object. You can, however, perform DDL operations by using ODBCDirect to execute SQL DDL statements.
• The Jet Engine is specifically designed (with Rushmore Query Technology) to work with indexes, whereas the ODBC architecture isn't. Because of this the Find and Seek methods-not to mention Filter, Sort, CopyQueryDef, etc.-are not available when using ODBCDirect. If you want to find a record, you need to create a query using a WHERE clause that returns the result.

How do I refer to ODBCDirect in my application so that I don't load the Jet Engine?
Keywords: ODBCDirect Workspace Object
Posted Apr 30, 1997

Using ODBCDirect is as simple as defining the Workspace Object to use. The Workspace defines how your application interacts with data, either by using the Microsoft Jet database engine, or ODBCDirect. The most typical way of creating a Workspace Object is:

	Dim wkJet as Workspace
		Set wkJet=DBEngine.Workspace(0)			

Or

	Dim wkJet as Workspace
		Set wkJet=CreateWorkspace ("","","",dbUseJet)
		

To create an ODBCDirect Workspace object all you have to do is change the syntax a little:

	Dim wkODBCDirect as Workspace
		Set wkODBCDirect =CreateWorkspace ("","","",dbUseODBC)		

You'll notice that the only significant difference in the way we created the Workspace object was our use of the Type constant, dbUseODBC. When this is used instead of dbUseJet, the Jet Database Engine is not loaded. Instead, all activity occurs directly with the ODBC data source.

I used to use DAO to connect to my SQL Server. How do I establish a connection to SQL Server using ODBCDirect?
Keywords: ODBCDirect OpenConnection
Posted Apr 30, 1997

For this, we need to create a new Connection object using the OpenConnection method. The new Connection object can be viewed as just a modified Database object, containing information about the connection, such as the server name, the data source name, and so on. The following code sample demonstrates a typically "preconnect" routine using ODBCDirect.

Dim mdbODBCDirect as database

Sub PreConnect (UserName As String, Password As String)

	Dim szConnect As String, wkODBCDirect as Workspace
   
 	szConnect = "ODBC;DSN=MyServer;DATABASE=MyDatabase;"
 	szConnect = szConnect & "UID=" & UserName & ";"
 	szConnect = szConnect & "PWD=" & Password & ";"
   
	Set wkODBCDirect =CreateWorkspace ("","","",dbUseODBC)

	Set mdbODBCDirect = wkODBCDirect OpenConnection("", dbDriverNoPrompt, False, szConnect) 
End Sub

Once the Connection object has been set, you can then open and create recordsets from server data, execute stored procedures, run parameter queries, etc., using familiar DAO objects, methods and properties.

ODBCDirect allows you to specify the type of cursor to use by setting the DefaultCursorDriver property of the Workspace object. What is the difference between the "Server Side" cursor and the "ODBC" cursor?
Keywords: ODBCDirect DefaultCursorDriver
Posted Apr 30, 1997

Server side cursors should be used when returning large result sets from single Select SQL statements (this includes Stored Procedures as well) or when retrieving "long" datatypes (e.g. Memo in Access, Text and Image in SQL Server, and LONG in Oracle). They perform very well on large resultsets because they call 2 extended Stored Procedures on the Server, sp_cursoropen and sp_cursorfetch. This allows for multiple rows to be retrieved for a single fetch call.

The ODBC Cursor Library is needed if a query or Stored Procedure returns multiple results sets, as is the case when the NextRecordset method is used. The ODBC Cursor Library works very well with small result sets but loses steam as the result sets grow. This is because the ODBC Cursor Library supports only the snapshot cursor model with limited update capability. The CursorOption property gives us the opportunity to decide which cursor to use before we create the actual connection.

I am using Excel and want to fill list boxes with information stored in SQL Server. How can I do this using ODBCDirect?
Keywords: ODBCDirect SQL Server
Posted Apr 30, 1997

Filling a listbox in Excel is just as easy using an additional DAO method, GetRows, which stores the recordset results into an array. In the code sample below we have already created a connection to SQL Server and stored the connection in the object "Connection". We use the GetRows method to retrieve the information into an array. Lastly, we use Excel's Transpose function to transpose the array and fill the listbox.

Dim vRecords as Variant, rst as Recordset, vFinalOutPut as Variant

''' Create the recordset for the listbox 
Set rst= Connection .OpenRecordset("Select productname from Products where(products.unitprice>=10.00)", dbOpenForwardOnly,, dbReadOnly)

''' Assume no more than 1000 rows (a lot for a listbox!).  If less, only rows retrieved are returned.
vRecords = rst.GetRows(1000)

'''Transpose the array so that the columns and rows match a range makeup
vFinalOutPut = Application.WorksheetFunction.Transpose(vRecords)

''' Fill the list box.
lstProducts.List = vFinalOutPut			

The only caveat is that ODBCDirect (like RDO) does not allow you to retrieve long datatypes (e.g. Memo in Access, Text and Image in SQL Server, and LONG in Oracle) using the GetRows method.

What is the NextRecordset method and why should I use it?
Keywords: ODBCDirect NextRecordset
Posted Apr 30, 1997

The NextRecordset method allows you to retrieve multiple results sets when you execute a query containing multiple select statements. Grouping multiple select statements together and executing them as if they were one statement gives us additional benefits like reduced network traffic and less code. Stored Procedures that return multiple result sets are also incredibly useful and efficient. The NextRecordset method lets us retrieve them all in a separate and organized manner. Before we create a connection, we need to direct the WorkSpace object to use the ODBC Cursor Library (a necessary prerequisite for using the NextRecordset method).

Dim WkODBCDirect As WorkSpace

Set wkODBCDirect =CreateWorkspace ("","","",dbUseODBC)
WkODBCDirect. DefaultCursorDriver = dbUseODBCCursor			

Filling listboxes in Excel is just as easy using an additional DAO method, GetRows, which stores the recordset results into an array. In the code sample below we have already created a connection to SQL Server and stored the connection in the object "Connection". We use the GetRows method to retrieve the information into an array. Lastly, we use Excel's Transpose function to transpose the array and fill the listbox.

Dim rst as Recordset, szSQL as String, vAuthors, vPublishers, vStores, bNext as Boolean
Dim vFinalAuthors, vFinalPublishers, vFinalStores

''' Define the Select statements which will return multiple result sets
szSQL = "SELECT FirstName FROM Authors;"
szSQL = szSQL  & "SELECT CompanyName FROM Publishers;"	
szSQL = szSQL  & "SELECT Location FROM Stores;"

''' Create the recordset 
Set rst = .Connection.OpenRecordset(szSQL, dbOpenForwardOnly, dbExecDirect, dbReadOnly)

''' Loop through the returned results using NextRecords method , storing each 
''' Recordset result into an array
With rst
	vAuthors = rst.GetRows(1000)
	.NextRecordset
	vPublishers = rst.GetRows(1000)
	.NextRecordset
	vStores = rst.GetRows(1000)
End With

''' Now we need to transpose the results so they can go into the listboxes
vFinalAuthors = Application.WorksheetFunction.Transpose(vAuthors)
vFinalPublishers = Application.WorksheetFunction.Transpose(vPublishers)
vFinalStores = Application.WorksheetFunction.Transpose(vStores)

''' Fill the list boxes
lstAuthors.List = vFinalAuthors 
lstPublishers.List = vFinalPublishers
lstStores.List = vFinalStores

I need to create a recordset from a stored procedure in SQL Server. The stored procedure I am using requires a parameter. Can I still use this stored procedure, passing it a parameter to create my recordset?
Keywords: ODBCDirect SQL Server Recordset
Posted Apr 30, 1997

Yes! You can explicitly pass parameters to SQL statements and stored procedures via the Parameter property. SQL Statements are written using either ODBC syntax or the target SQL Server syntax (usually Transact-SQL). Stored Procedures, on the other hand, are Transact-SQL statements that sometimes accept parameters. Stored Procedures are very useful because they have already been optimized by SQL Server and may reside in memory, making them very fast!

In the past you had to create a Visual Basic function that builds and runs a QueryDef object using a SQL statement that concatenates the parameter criteria as literal values. This obstacle is overcome now by using ODBC standard syntax (Escape clauses, Call syntax and Parameter Markers). ODBCDirect sends the SQL statement to the ODBC driver for translation, after which it is sent directly to the server. Here is a querydef object being created to run a Stored Procedure which accepts a parameter to limit the records returned (notice the ODBC syntax). . In the code sample below we have already created a connection to SQL Server and stored the connection in the object "Connection".

Dim szSQL As String, qdf As QueryDef, rst As Recordset
   	 
	''' Call the byRoyalty Stored Procedure using ODBC syntax
szSQL = "{ call byRoyalty (?) }"

''' Create the temporary querydef.  By default it is prepared as a temporary Stored Procedure 
''' SQL Server side.
Set qdf = .Connection.CreateQueryDef("",szSQL)	

''' Explicitly pass the Stored Procedure the parameter represented 
''' by the ODBC Parameter Mark "(?)"
qdf.Parameters(0).Type = dbInteger
qdf.Parameters(0) = 40
   	
Set rst = qdf.OpenRecordset()

You can still use the old syntax (basically the name) if you want to execute a Stored Procedure that does not have any parameters, but you have to use the OpenRecordset method. This is because the name of a Stored Procedure is not Transact-SQL, nor can it be translated by the ODBC driver so the CreateQueryDef method won't work.

Back to Top

Back to the FAQ Table of Contents

You have the right to remain silent. Anything you say will be misquoted, then used against you.