Microsoft Access ADO Connection String for ASP

 

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

Where:

  • rst_name – is the local recordset variable name you have defined

  • str_source – optional argument which could be any of the following:

    URL
    Full path or relative path to a file
    Command object
    SQL statement string  (this option is used in our examples)
    Stored procedure name
    Table 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:

adAsyncExecute
adAsyncFetch
adAsyncFetchNonBlocking
adExecuteNoRecords
adExecuteStream
adExecuteRecord
adOptionUnspecified

CommandTypeEnum – which has the following constant options:

adCmdUnspecified
adCmdText
adCmdTable
adCmdStoredProc
adCmdUnknown
adCmdFile
adCmdTableDirect

 

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016