Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted May 30, 2000

Introduction to Database Manipulation with ADO - Page 6

By Benoy Jose

Parameters Object:

If a SQL query requires parameters then it can be supplied by adding them to the Command objects' parameters collection or by creating them on the fly as we execute the SQL query.

myCommand.Execute RecordsAffected, Parameters

An array of parameters can be passed to the Parameters object while the query is being executed.

myCommand.Execute Array (parameter1, parameter2 etc...)

Instead of passing parameters on the fly we can hold the parameters in the parameters collection. For this the parameter collection has methods like Append, Delete, Refresh, and properties like Count, Item etc. To add a new parameter an instance of the Parameter is made and its property values are set and finally the Append method of the Command object is called to append the parameters to it like it is done in line 5.

The parameters are not limited to stored procedures, they can also be used in SQL statements in Access where parameters are indicated by being placed in square brackets within the statement. This can be used as an alternate for SQL statements in which the 'where' clause changes dynamically.

We need to specify Type and Size for the parameters used in the Parameter object. For String values it is the size and for numeric values the precision needs to be specified.

The Property Object:

There are two kinds of properties: Built-in properties and Dynamic properties. The Built-in properties are those defined by ADO for objects as they are created. These are part of the object itself and can be used to set or get some property to the object. These are available irrespective of the provider being used.

The Dynamic properties are additional properties specifically provided by the provider.

Error Collection:

The Errors collection holds all of the Error objects for a specific Connection. Generally, if an ADO operation encounters errors, one or more Error objects are automatically added to the Errors collection. More detailed error messages can be obtained from these error objects instead of vague Runtime errors. The Errors collection has a clear method to clear all the errors. It also has two properties. The count property returns the number of error objects in the collection and the item property is used to retrieve the contents of the error objects in the collection. Two types of error objects are thrown depending on the type of error. Errors are returned by the data source whereas Err objects are created at compile time or runtime by VBScript or by an automation object like ADO.

Apart from critical errors that stop execution some properties and methods return warnings to the Errors collection . Warnings don't stop execution, but that could be due to small problems like data conversion, which could affect the precision of the result.

The Error object has properties like Description for detailed description , Source for the source of the error, Number for the ADO error code number etc.

Click here for code example 6.


Ado is the easiest way to provide dynamic content on the Internet. It is best suited for small and medium sized web sites as the development time and costs are minimum compared to other languages. Moreover its default support on Windows NT makes it an ideal choice for companies intending to do business on the net. ASP and ADO seamlessly combine with other Microsoft tools like the Siteserver, MTS and Commerce server making it scaleable and robust.

MS Access Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM