PRB: Illegal to Use Find Methods w/ SQL PASSTHROUGH & ODBC DB (106111)

SYMPTOMS

When you create a Dynaset or Snapshot using the SQL PASSTHROUGH option with an ODBC database, the FindFirst, FindNext, FindLast, and FindPrevious methods give the error "Can't perform operation; it is illegal."

CAUSE

FindFirst, FindNext, FindLast, and FindPrevious work only on record sets opened by a query. Visual Basic version 3.0 doesn't use a query to open a record set when you use DB_SQLPASSTHROUGH, so these Find methods are not allowed, by design.

RESOLUTION

The SQL PASSTHROUGH option causes the query to be processed by an external database server, instead of by Visual Basic. Avoid using the SQL PASSTHROUGH option if you want to use the FindFirst, FindNext, FindLast, or FindPrevious methods with an ODBC database. Also, you can avoid the problem by creating a copy of the dynaset or snapshot. This will allow the Microsoft Access Engine to perform the FindFirst rather than allowing the ODBC server to do it. However, the the dynaset copy must not use the SQL PASSTHROUGH option. Here is an example:

   Dim db as database
   Dim ds as dynaset
   Dim newds as dynaset

   Set db = OpenDatabase("", 0, 0, "ODBC;DSN=texas")
   Set ds = db.createdynaset("Select * from Authors", 64) ' SQL PASSTHROUGH
   Set newds = ds.createdynaset()  ' No SQL PASSTHROUGH
   newds.FindFirst ""  ' Now FindFirst works on newds

MORE INFORMATION

Steps to Reproduce Behavior

The following code results in the "Can't perform operation; it is illegal" error:

   Dim db As database
   Set db = OpenDatabase("", 0, 0, "ODBC;DSN=texas")
   Dim ds As Dynaset   ' Creates a dynaset.
   ' The DB_SQLPASSTHROUGH option is 64:
   Set ds = db.CreateDynaset("select * from authors", 64)
   ds.FindFirst ""     ' FindFirst causes error message.