OpenDatabase Method

Description

Establishes a user session to the database. It creates a new OraDatabase object using the given database name, connection string, and specified options.

Usage

Set oradatabase = orasession.OpenDatabase(database_name,connect_string, options) Set oradatabase = oraserver.OpenDatabase(connect_string, options)

Arguments

The arguments for the method are:

Arguments Description
database_name The Oracle Network specifier used when connecting the data control to a database.
connect_string The user name and password to be used when connecting to an Oracle database.
options A bit flag word used to set the optional modes of the database. If options = 0, the default mode settings apply. The following table shows the possible modes, which can be combined by adding their respective values.

Constants

The following table lists constants and values for the options flag.

Constant Value Description
ORADB_DEFAULT &H0& Visual Basic Mode (Default):

Field (column) values not explicitly set are set to Null when using the AddNew or Edit method. The Null values override any database column defaults. Wait on row locks when using Edit ("SELECT...FOR UPDATE").

Nonblocking SQL functionality is not enabled.

ORADB_ORAMODE &H1& Oracle Mode:

Lets Oracle Database set the default field (column) values when using the AddNew method. The Oracle default column values are fetched again from database immediately after an insert or add operation.

Note: If you use triggers, fetch the data again using the full Oracle Mode.

ORADB_NOWAIT &H2& Lock No-Wait Mode:

Does not wait on row locks. When you use the Edit method to update a row that is locked by another user or process, Lock No-Wait mode results in an immediate return of an error code.

Note: This option only applies to the OraDynaset object. It has no effect on OraSQLStmt objects or ExecuteSQL calls. It only raises an error in the case of a locked row.

ORADB_NO_REFETCH &H4& Oracle Mode (No Refetch):

Performs like the Oracle Mode, but does not refetch data to the local cache. This boosts performance.

Note: Use the No Refetch mode only when you intend to insert rows without editing them, because database column defaults cause inconsistencies between database data and the local cache. Attempting to edit after inserting in this mode causes a Data has been modified (4119) error.

ORADB_NONBLK &H8& Nonblocking Mode:

Turns on Nonblocking mode on SQL statement execution. Nonblocking mode affects the SQL statements processed using the ExecuteSQL, CreateDynaset, or CreateSQL methods.

Note: This feature has been deprecated.

ORADB_ENLIST_IN_MTS &H10& Enlist in MTS Mode:

Determine whether the OraDatabase object enlists in the Microsoft Transaction Server (MTS) mode.

ORADB_ENLIST_FOR_ CALLLBACK &H20& Enlist For Callbacks Mode:

Turn on the event notification. This mode has to be enabled to receive Failover Notifications.


These values can be found in the oraconst.txt file. For creating a valid database alias, see the Oracle Net Services Administrator's Guide.

Examples of valid connect_string arguments include:

  • "scott/tiger"

  • "system/manager"

  • "/"

Remarks

An OraConnection object is created automatically and appears within the OraConnections collection of the session. Opening a database has the effect of opening a connection but does not perform any SQL actions.

One possible connection error that could be returned is:

ORA-28001 "the password has expired"

The user can change the password using the ChangePassword method.

Examples

This example demonstrates how to programmatically create a dynaset and all of the underlying objects. Copy and paste this code into the definition section of a form with text boxes named txtEmpNo and txtEName. Then, press F5.

Sub Form_Load ()
 
 'Declare variables
 Dim OraSession As OraSession 
 Dim OraDatabase As OraDatabase 
 Dim OraDynaset As OraDynaset 
 
 'Create the OraSession Object.
 Set OraSession = CreateObject("OracleInProcServer.XOraSession")
 
 'Create the OraDatabase Object by opening a connection to Oracle.
 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
 
 'Create the OraDynaset Object.
 Set OraDynaset = OraDatabase.CreateDynaset("select empno, ename from emp", 0&)
 
 'Display the first record.
 txtEmpNo = OraDynaset.Fields("empno").value
 txtEName = OraDynaset.Fields("ename").value
 
End Sub