PRB: Errors When Referencing Oracle Synonym or Table Names

- Professional Edition of Microsoft Visual Basic for Windows,

  version 3.0

SYMPTOMS

The following two errors may occur when you use the CreateDynaset command against an Oracle database:

1. Error 3024: Couldn't find file <ownername>.mdb

   This error occurs when the Microsoft Access database engine's SQL parser in Visual Basic version 3.0 cannot handle the Oracle-style reference of <ownername>.<tablename or synonym> within an SQL statement:

         Set DS = DB.CreateDynaset("Select * From <ownername>.<tablename>")
   

2. Error 3078: Couldn't find input table or query <ownername>.<tablename>

   This error occurs when only a table name is provided and duplicate tables exist or synonyms with different owners exist. In this case, the Microsoft Access database engine resolves the ambiguous reference by picking one of the duplicate table name owners based on alphabetical order:

         Set DS = DB.CreateDynaset("Select * From ,<tablename>")
   

RESOLUTION

Use the following techniques to prevent the error messages:

• Use DB_SQLPASSTHROUGH when creating the Dynaset.
• Attach the Oracle table to a Microsoft Access database.
• Remove all duplicate tables and synonyms from the Oracle database.

STATUS

The Microsoft Access database engine is not currently designed to handle Oracle-style references. The current design is under review and may be modified in future versions of the database engine.

MORE INFORMATION

Four Example Scenarios

The following four scenarios demonstrate how the Microsoft Access database engine in Visual Basic version 3.0 responds when you use the CreateDynaset command against an Oracle database.

1. You are the owner of the table and there are no duplicate tables or synonyms owned by you or any other user. You issue this command:

         Set DS = DB.CreateDynaset("<tablename>")
   

   The database engine resolves this ambiguous reference by assuming you are the owner, which is a reasonable assumption given that there are no other owners. This works without error.

2. You are the owner of the table and there are no duplicate tables or synonyms owned by you or any other user. You issue this command:

         Set DS = DB.CreateDynaset("<ownername>.<tablename>")
   

   The database engine's SQL parser is unable to resolve this reference. It interprets <ownername> to be a database name, whether you specify your ownername or someone else's. This results in error 3024:

         Couldn't find file <ownername>.mdb
   

3. You are the owner of the table and there's a duplicate table name in the database that has another owner. You issue this command:

         Set DS = DB.CreateDynaset("<tablename>")
   

   The database engine resolves this ambiguous reference alphabetically by selecting the first owner of a table with the given table name. If the other owner happens to be first alphabetically, then you receive error 3078:

         Couldn't find input table or query "<ownername>.<tablename>"
   

   where <ownername> is the name of the other owner.

4. You are the owner of the table and there's a duplicate table name in the database that has another owner. You issue this command:

         Set DS = DB.CreateDynaset("<ownername>.<tablename>")
   

   The database engine's SQL parser is unable to resolve this reference. It interprets <ownername> to be a database name. This results in error 3024:

         Couldn't find file <ownername>.mdb
   

Detailed Workarounds

Here are detailed suggestions to help you avoid the errors caused by the Microsoft Access database engine's inability to resolve your Oracle-style references:

1. When using a SQL statement against an Oracle database that contains duplicate tables, use the CreateDynaset Method with the DB_SQLPASSTHROUGH option (DB_SQLPASSTHROUGH = 64) as in this example:

         Set DS = DB.CreateDynaset("<ownername>.<tablename>", 64)
   

   This passes the SQL statement directly to the ODBC backend processor, bypassing the Microsoft Access database engine. The only drawback to this method is that the resulting dynaset will note be updatable.

2. Attach the Oracle table to a Microsoft Access database, and then reference it as a Microsoft Access table as in this example:

   Dim DB as Database Dim DS as Dynaset Dim TD as New TableDef

   Set DB = OpenDatabase("C:\VB\BIBLIO.MDB") ' Any temporary Microsoft

                                                ' Access database.
      TD.Name = "<newtablename>" ' New table name in the Access database.
      TD.SourceTableName = "<ownername>.<tablename>"
      TD.Connect = "ODBC;" ' A longer string can be used.
      DB.TableDefs.Append TD  ' Append the Oracle table.
   
      ' Create a dynaset based on the attached table:
      Set DS = DB.CreateDynaset("Select * from <newtablename>")
             .
      ' Any operations you perform on the attached table are
      ' applied to the actual table in the Oracle database.
      ' But you cannot use the OpenTable Method on an attached table.
   
      DB.TableDefs.Delete TD  ' Remove the attached table when finished.
   
      Instead of attaching and removing Oracle tables by using a temporary
      Microsoft Access database, you can attach all the Oracle tables to a
      permanent Microsoft Access database and reference that instead.
   
   

3. Remove all duplicate table names and synonyms from your Oracle database and reference only the table name in the SQL statement (see scenario 1).