Image of Navigational Map linked to Home / Contents / Search ADO 2 - The Next Generation

by Jason Tyro - GUI Computing
Image of Line Break

One of Microsoft's latest babies is ActiveX Data Objects or ADO. Since its conception, about 30 minutes ago, there have already been two and a half versions, with ADO 2 being the current release. So far, this looks like it might be the goods, and the standard for the near future, but hey, you can never be really sure!

Basic ADO code is not that far removed from DAO so there's no big learning curve, however it is sufficiently different to make the average programmer make good use of the help file to start with. And it offers some cool new ways to do things, which just "doing it the DAO way" might never uncover.

So let's dive right on in...

"Enterprise to DS9... Request Docking Connection"

Before we can delete loads of mission critical data that cannot be restored we must connect our sorry excuse for a workstation to a datasource, where all that lovely data is kept.

Let's connect to a server :

	Dim conServerConnection as ADODB.Connection
	Dim sConnection as String

	sConnection = "driver={SQL Server};" 
	sConnection  = sConnection  & "server=<SERVER NAME>;"
	sConnection  = sConnection  & "uid=<USER ID>;"
	sConnection  = sConnection  & "pwd=<PASSWORD>;"
	sConnection  = sConnection  & "database=<DATABASE>"

	Set conServerConnection = New ADODB.Connection 
	conServerConnection.ConnectionString = sConnection
	conServerConnection.Open

Now that's one way of doing it directly, here's another using a DSN or Data Source Name

	Dim conServerConnection as ADODB.Connection
	Dim sConnection as String

	sConnection = "DSN=<DATA SOURCE NAME>;" 
	sConnection  = sConnection  & "uid=<USER ID>;"
	sConnection  = sConnection  & "pwd=<PASSWORD>"

	Set conServerConnection = New ADODB.Connection 
	conServerConnection.ConnectionString = sConnection
	conServerConnection.Open

As you can see the connection string doesn't refer to the server and database we're interested in. This information is already held in the DSN which can be configured via the ODBC 32 Administration tool in Control Panel.

Now from the two examples above we see that the OPEN command is a fundamental call. However we can actually use it to refer to a DSN directly and not use the CONNECTIONSTRING property, as in :

	Dim conServerConnection as ADODB.Connection

	Set conServerConnection = New ADODB.Connection
	conServerConnection.Open "<DATA SOURCE NAME ", "<USER ID>", "<PASSWORD>"

Well that's all pretty logical really - Create a new connection, give it a path to find and open it. Wham Bam Thank-you Mam !

"DS9 to Enterprise… Two to Beam Up."

So now we've established a connection, we should now try and get some data from the server to the workstation to make all this hard work worth while.

We can do this by utilising the highly regarded RECORDSET object.

This will look something like...

	Dim rsRecordset as ADODB.Recordset

	Set rsRecordset = New ADODB.Connection
	rsRecordset.Open = "<TABLE NAME>", adOpenForwardOnly, adLockPessimistic, adCmdTable

That will give us the entire table specified in <TABLE NAME>. If we want to specify a SQL statement then we need to change the OPEN command to :

	Dim rsRecordset as ADODB.Recordset
	Dim sSQL as String

	sSQL = "SELECT * FROM <TABLE NAME> WHERE <FIELD NAME> = <CRITERIA>"

	Set rsRecordset = New ADODB.Recordset
	rsRecordset.Open = sSQL, adOpenForwardOnly, adLockPessimistic, adCmdText

The 'adCmdText' tells ADO to run the SQL statement rather than the previous example where the adCmdTable tells ADO to return all the rows from the table specified ie. SELECT * FROM <TABLE NAME>.

The other 2 constants adOpenForwardOnly and adLockPessimistic can be changed with a variety of constants depending on the cursor type and locking required.

Now we are not limited to using the OPEN command to populate a recordset, we can also use the EXECUTE method of the CONNECTION object. To whit :

	Dim rsRecordset as ADODB.Recordset
	Dim sSQL as String

	sSQL = "SELECT * FROM <TABLE NAME> WHERE <FIELD NAME> = <CRITERIA>"

	Set rsRecordset = conServerConnection.Execute(sSQL)

As may become apparent, ADO allows you to the same operations in many weird and wonderful ways.

"But Captain, the engines'll never take it!"

Taking care of our precious network bandwidth is something we should consider….or the System Administrator will be down on us!

Pulling down 50,000 records to client workstation is probably not the best way to go. However if you must do so, then a decision should be made on your choice of CURSOR LOCATION. Keeping with the default, Server Side (adUseServer) will give you fast access to your data. However the resource-eating recordset connection will have to stay intact until you close recordset itself.

	ConServerConnection .CursorLocation = adUseServer

Choosing the Client Side (adUseClient) allows you to 'disassociate' your recordset from the connection thus freeing up database resources. However the access time to your data is increased dramatically - there's no such thing as a free lunch !

	ConServerConnection .CursorLocation = adUseClient

"Number One - Make It So."

The COMMAND object is something else that you can have a play with. Here we have yet another way of populating a recordset with client data.

This little ditty uses a stored procedure that is already on the server database. It will look something like :

	CREATE PROCEDURE <SP_NAME> ( @ID int )
	AS
	SELECT * FROM <TABLE NAME>  WHERE <FIELD> = @ID

This stored procedure will select all the records from <TABLE NAME> where the <FIELD> is equal to the input parameter @ID, which is a variable that we will assign to it from our client application.

	Dim cmdCommand As New Command
	Dim rsRecordset As adodb.Recordset
	Dim sConnect As String

	sConnection = "DSN=<DATA SOURCE NAME>;" 
	sConnection  = sConnection  & "uid=<USER ID>;"
	sConnection  = sConnection  & "pwd=<PASSWORD>"

	cmdCommand.ActiveConnection = sConnection

	cmdCommand.ActiveConnection.CursorLocation = adUseClient

	cmdCommand.CommandText = <SP_NAME>

	cmdCommand.CommandType = adCmdStoredProc

	cmdCommand.Parameters(1).Value = <CRITERIA VALUE>

	Set rsRecordset = cmdCommand.Execute

Looks a bit wacky but the recordset is populated with the records we're interested in and is disassociated from the connection because of our intelligent use of the CURSOR LOCATION!

Now here's a clever way of using of returning values of a single record without having to use any sort of Recordset type thingy. By defining output parameters in our SQL stored procedure we can return the values of the fields we require into the command object. The stored procedure will look like this :

	CREATE PROCEDURE <SP_NAME>
	(
	@ID int,
	@Out1 Char (30) OUTPUT
	)
	AS
	SELECT @Out1 = <FIELD> FROM <TABLE NAME> WHERE <CRITERIA FIELD> = @ID
	RETURN(0)

The Out1 parameter will take on the value of <FIELD> and works much in the same way as the 'By Ref' clause in VB.

So to use this, our code will look like :

	Dim cmdCommand As New Command
	Dim sConnect As String

	sConnection = "DSN=<DATA SOURCE NAME>;" 
	sConnection  = sConnection  & "uid=<USER ID>;"
	sConnection  = sConnection  & "pwd=<PASSWORD>"

	cmdCommand.ActiveConnection = sConnection

	cmdCommand.CommandText = <SP_NAME>

	cmdCommand.CommandType = adCmdStoredProc

	cmdCommand.Parameters.Append cmd.CreateParameter("ID", adInteger, adParamInput, 11)

	cmdCommand.Parameters.Append cmd.CreateParameter("OUT1", adChar, adParamOutput,  30)

	cmdCommand(0) = <CRITERIA VALUE>

	cmdCommand.Execute

	MsgBox cmdCommand(0) & vbTab & cmdCommand(1)

Obviously this code is for read only type scenarios but I think it is a rather neat and certainly efficient way of returning records.

"...there's a change in the Space/Time Continuum, Captain!"

So now we have a lovely recordset full of unsuspecting records that have no idea what's in store for them ! Well that's not entirely true - if we have used the adLockReadOnly parameter, we can only look but not touch. However with any other locking parameter those little records are fair game, as long as we have sufficient server, database, access rights and all those associated bothersome settings correct.

As you can probably guess there are a variety of ways to edit said records.

To add a new record, the familiar ADDNEW command is one way to go.

	rsRecordset .AddNew
	rsRecordset .Fields(0) = <VALUE 1>
	rsRecordset .Fields(1) = <VALUE 2>
	rsRecordset .Fields(2) = <VALUE 3>
	' etc...
	rsRecordset .Update

Now to edit the current record, there is an noticeable lack of the EDIT command we came to love and abuse in DAO. In fact in ADO such a command is not necessary. So assuming we are on a current record we can :

	rsRecordset .Fields(0) = <VALUE 1>
	rsRecordset .Fields(1) = <VALUE 2>
	rsRecordset .Fields(2) = <VALUE 3>
	' etc...
	rsRecordset .Update

Now that's doesn't hurt does it ?

But we aren't limited to changing data this way. Intelligent use of the EXECUTE command on the connection object will allow SQL statements to be run on the server. So...

	Dim sSQL as String

	sSQL = "UPDATE <TABLE NAME>"
	sSQL = sSQL & " SET <FIELD NAME> = <VALUE>"
	sSQL = sSQL & "  WHERE <FIELD NAME> = <CRITERIA>"

	conServerConnection.Execute sSQL

Of course this also allows us to use INSERT, DELETE etc. to our heart's content.

"Captain's Log..."

Well there you go, pretty straight forward really. ADO has many more features that would require far more time to go into, and this article could only ever provide a quick tour of the variety of ways to code that ADO opens up. Every development has different requirements, and ADO's flexibility in providing an assortment of many different data access scenarios gives us all a much more powerful tool than DAO could ever have been.

Explore a bit - you'll like what you find!



Written by: Jason Tyro
October '98

Image of Arrow linked to Previous Article Image of Arrow linked to Next Article
Image of Line Break
[HOME] [TABLE OF CONTENTS] [SEARCH]