by Lisa Hooper - GUI Computing
The new ODBCDirect workspaces available in VB 5 will provide great flexibility for those of us using VB as a front end to SQL Server or any other ODBC database source. This will especially be the case for those who have become comfortable with the DAO object model. ODBCDirect allows you to access ODBC data sources through the RDO with DAO objects, bypassing the Jet database engine. This gives you a more direct way to access server data by using the existing DAO object model directly on top of the ODBC API.
ODBCDirect functionality will open up a whole new world of data access combinations, which with proper research will allow you to easily leverage your server's performance capabilities like never before. Nevertheless we don't always have time to gain a full understanding for the best way to make use of a particular technology and just want to 'get things working' as quickly as possible.
This minimum approach can still provide great benefits with ODBCDirect, as the mere fact of bypassing Jet should provide considerable performance enhancements as well as the benefit of no longer having to distribute the jet engine along with your app. The following will provide a simple example of converting a small section of VB4 code which uses a DAO via Jet to access an SQL Server database, to VB 5 code bypassing Jet. The example will also illustrate the read-only dynaset issue which is a common problem when attempting this conversion.
The following code is VB 4 code which opens an ODBC database, creates a dynaset consisting of all records in the Titles table and changes the title field of the first record in the dynaset.
Dim ws As Workspace, db As Database, dy As Recordset Set ws = Workspaces(0) 'Creates the default workspace Set db = ws.OpenDatabase("", False, False, "ODBC;DSN=PubsData;UID=sa;PWD=;DATABASE=Pubs") Set dy = db.OpenRecordset("Select * from titles", dbOpenDynaset) dy.MoveFirst dy.Edit dy!Title = "This is the new title" dy.Update dy.Close db.Close ws.Close
When converting the above code to VB 5 we now want to bypass Jet by using an ODBCDirect workspace.
This line of code:
Set ws = Workspaces(0) 'Creates the default workspace
should be changed to something like this:
Set ws = CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC) Workspaces.Append ws
After this simple change we are now using an ODBCDirect workspace and completely bypassing Jet. Yet although the code is syntactically correct this is not the only change we need to make. If we ran the code now it would produce a runtime error on the line:
The error produced is "3027 - Can't update. Database or object is read-only".
This error is produced because the default for the lockedits argument of the OpenRecordset method is dbReadOnly for ODBC direct workspaces. Therefore, if you want to edit data on the server, you'll need to explicitly specify a lock type other than dbReadOnly for the lockedits parameter, for example:
Set dy = db.OpenRecordset("Select * from titles", dbOpenDynaset, dbExecDirect, dbOptimisticValue)
The dbOptimisticValue argument corresponds to optimistic concurrency using row values (concur values). The ODBC cursor library compares data values in old and new records to determine if changes have been made since the record was last accessed.
The code will now run successfully, making the appropriate changes to the titles table. As you can see, you can play around with the different types of recordsets and workspaces to find the one that best suits your objectives but in the meant time you can make a few quick changes to allow you to take advantage of VB 5's superior data access.