PRB: Errors When Referencing Oracle Synonym or Table Names

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).