13.2.5- What are Options?

What are Options?

The last attribute in the list is known in the documentation as the Options. This rather unhelpful description does nothing to explain that this attribute is used to define what type of data source is being referred to. For example:

objRec.Open "Movies", strConnect, adOpenForwardOnly, adLockReadOnly, adCmdTable

The source here is a database table called Movies – and so we use the constant adCmdTable to tell ADO that Movies is a database table.

In other words, the Options parameter tells the recordset what form the data source will take. Are you using a database table, or a SQL query, or a stored procedure… or perhaps you're not sure? This parameter allows you to tell ADO how to evaluate the request, and hence enable it to make the most efficient use of its resources.

The most common options you can set are as follows:

• Text command (adCmdText) is used to indicate that Source parameter holds command text, for example, a SQL command.
• Table name (adCmdTable) is used to indicate that Source parameter holds the name of a table.
• Stored procedure (adCmdStoredProc) is used to indicate that Source parameter holds the name of a stored procedure or query.
• Table (adCmdTableDirect) is used to indicate that Source parameter holds the name of a table.
• Saved Recordset (adCmdFile) is used to indicate that Source parameter holds the file name of a saved recordset
• URL (adCmdURLBind) is used to indicate that Source parameter holds a URL.

The Options parameter doesn't map directly to a property of the Recordset object, in the way that the cursor type and lock type do. However, it does map onto the CommandText property of the Command object. We'll be looking at this in the next chapter .