Converting Applications to ODBCDirect

The following are tips for converting existing applications that run against Microsoft Jet databases to ODBC data sources. They assume the database has already been placed on the server and that you will only want to use ODBCDirect to access your data. In the meantime, the tips listed below will quickly get you up and running with ODBCDirect.

• Change the Workspace Type — If you do not create any workspaces, you still need to tell DAO what the default workspace type should be ODBCDirect. This is accomplished by setting the DefaultType property of DBEngine to dbUseODBC before doing any operations that need the default workspace. Once a workspace is created, you cannot change its type.
  
  
• If you explicitly create workspaces in your application while DefaultType is set to dbUseODBC, then all new workspaces will be ODBCDirect workspaces. However, if you want your code to be self-documenting, or if you will be using both Microsoft Jet and ODBCDirect workspaces, you can pass a fourth parameter to the CreateWorkspace method, specifying the type of workspace to create.
  
  
• Change OpenDatabase — You will need to change the arguments passed to OpenDatabase. Instead of passing a database name in the first parameter, you will now pass a connection string in the fourth parameter. (Note that all connection strings start with "ODBC;".)
  
  
• You may decide to open connections instead of databases. In this case, change your OpenDatabase calls to OpenConnection. Both functions take similar arguments.
  
  
• Handling Data Definition Language (DDL) — DAO’s ODBCDirect functionality does not support the TableDefs or Indexes collections. This means your application will no longer work if it creates new TableDefs or uses the Indexes collection of a TableDef.
  
  
• If this is a problem there are two ways you can change your code: You can create a Microsoft Jet workspace and open a second database to the data source, doing all DDL work within it. Or you can execute SQL calls to create and find objects. SQL calls are best if you want to keep from loading Microsoft Jet. Creating a second database works well if you want to limit the amount of code that changes.
  
  
• Creating/Using QueryDef objects — In ODBCDirect workspaces the Database object does not support the CreateQueryDef method. That is handled by the Connection object. In your code, you need to change all CreateQueryDef calls to be executed on the Connection property of the Database rather than the Database object itself, or change all OpenDatabase to OpenConnection. QueryDef objects created in ODBCDirect are not stored in the database and are lost when the object is closed or goes out of scope.
  
  
• Opening Recordset objects — ODBCDirect Recordset objects default to the fastest Recordset type rather than the most functional, as in Microsoft Jet. Typically this is a Recordset that cannot scroll backwards and is read only. If you need more functionality in the Recordset, you may specify that explicitly. If you need to scroll backwards or need bookmarks, then choose a different Recordset type, such as dbOpenDynaset. If you need to update the Recordset, choose a locking type, such as dbOptimistic.
  
  
• Code written in a Microsoft Jet workspace that plans on editing data and opens a Recordset with only the name argument provided will not run in ODBCDirect. However, you can change the OpenRecordset to supply a Recordset type and locking constant that will allow updating.
  
  
• Parameterized queries — ODBCDirect does not support named parameters. The syntax for a parameter in an SQL statement is a ‘?’, rather than a name as in Microsoft Jet. For example, the Microsoft Jet SQL "SELECT * FROM Employees WHERE LastName = txtName" creates a parameter called “txtName.” In ODBCDirect, the SQL would read "SELECT * FROM Employees WHERE LastName = ?". In the Parameters collection, the name of the parameter would be "Parameter1". It should be noted that Microsoft Jet allows you to say "Parameters iAuthId Integer; SELECT * from authors where au_id = iAuthId". Since ODBCDirect does not have named parameters, you cannot use the "Parameters …;" part of the previous example.
  
  
• Review program for optimizations — Always go back over the program and look for optimizations because ODBCDirect offers new and different ways to improve performance.