Open Method – Microsoft Activex Data Object – ADO
The Open Method with all options explained
The Open Method is a common feature to all methods of connecting to a database.Once a connection is made you want to do something with the data such as retrieve (select) update, or execute a command or stored procedure – you do all these with the open statement. The open statement has the following structure:
(Note: the following information is paraphrased from the Microsoft Access 2003 documentation)
rst_name.Open str_source, str_connection, int_cursortype, int_locktype, lng_options
rst_name – is the local recordset variable name you have defined
str_source – optional argument which could be any of the following:
Full path or relative path to a file
SQL statement string (this option is used in our examples)
Stored procedure name
str_connection – optional argument which is a connection string
int_cursortype – optional argument which specifies the cursor type to be used by the open statement – the following are valid entries with there numeric constant in parenthesis:
adOpenForwardOnly (0) – this is the default cursor type if none is specified. This is similar to a static cursor, except that you can only scroll forward through records. This improves performance when you need to make only one pass through a Recordset.
adOpenKeyset (1) – The keyset cursor is like a dynamic cursor except that you can’t see records that other users add, although records that other users delete are inaccessible from your Recordset. Data changes by other users are still visible.
adOpenDynamic (2) – With the dynamic cursor additions, changes, and deletions by other users are visible, and all types of movement through the Recordset are allowed,.
adOpenStatic (3) – The static cursor provides a static copy of a set of records that you can use to find data or generate reports. Additions, changes, or deletions by other users are not visible.
adOpenUnspecified (-1) – The cursor type is not specified
int_locktype – Specifies the type of lock placed on records during editing.
adLockBatchOptimistic (4) Indicates optimistic batch updates. Required for batch update mode.
adLockOptimistic (3) Indicates optimistic locking, record by record. The provider uses optimistic locking, locking records only when you call the Update method.
adLockPessimistic (2) Indicates pessimistic locking, record by record. The provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately after editing.
adLockReadOnly (1) Indicates read-only records. You cannot alter the data.
adLockUnspecified (-1) Does not specify a type of lock. For clones, the clone is created with the same lock type as the original.
lng_options – Optional. A Long value that indicates how the provider should evaluate the Source argument if it represents something other than a Command object, or that the Recordset should be restored from a file where it was previously saved. The following are valid options:
ExecuteOptionEnum – which has the following constant options:
CommandTypeEnum – which has the following constant options:
Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016