Introduction to the ADO Connection Object

,

ADO (Active Data Objects) is really the way to access data right now.

It's pretty much replaced RDO and DAO for new projects. ADO.Net is on the way,

but not quite here yet. Whether you're a DBA or a developer, ADO is something

you need to know. This week I'm going to explore the Connection object, in

upcoming articles I'll also provide some introductory coverage of both the

Recordset and Command objects.

If you have VB or Access, you'll find those environments are great

productivity boosters - the strongly typed variables, syntax checking, online

help, all help you work faster and learn faster. If you're using VBScript, you

can still make it work, it may just take you a bit longer to get there.

If you have VB/Access, set a reference to 'Microsoft Active Data Objects

2.6'. If you don't see it, you may see older versions like 2.5 or even 2.1. I'd

encourage you to apply the latest MDAC to get the bug fixes, but it's not

critical to the examples that follow. 

As the name implies, you'll be working with objects, which means using the

set statement. Here is how to begin using the most basic object in ADO, the

connection object, using VB:

dim oConnection as adodb.connection

set oConnection = new

adodb.connection

Or in VBScript:

dim oConnection

set oConnection=createobject("adodb.connection")

You've just "instantiated" the object. Before you can actually do

anything with it, you need to "open" it, like this:

oconnection.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data

Source=eg;Application Name=TestApp"

New developers often find getting the connection string correct the hardest

part of using ADO. Just looking at it, you'll see the 'provider' is the SQLOLEDB

provider - you'll definitely want to use this one when connecting to SQL.

Initial Catalog is the same concept as default database. Setting the application

name is a great best practice - ever run sp_who2 and see a blank program name?

That's because the programmer didn't take the time to add this to the connection

string. The order of the items in the connection string doesn't matter, the only

time case matters is for the password if you're providing one or the

server/database is case sensitive.

Still, aside from doing the copy and paste thing, it's a lot to remember when

you're trying to right a quick 10 minute utility app. Try this trick to always

get it right. Create a empty Notepad file on your desktop called connection.txt.

Rename it to connection.udl. Double click it. You'll be in the UDL designer,

possibly even better than sliced bread. Click on the provider tab to get

started, then select 'Microsoft OLE DB Provider for SQL Server'.

Now click on the connection tab. Either type in your server name or pick it

from the drop down list. Move down and select how you want to connect - NT if

you're using a trusted connection (lan login) or the specific name/password if

you're using a SQL login. If you're using the SQL login option, go ahead and

check the box called 'Allow Saving Password'. Move on to step #3 and pick the

database, and then click Test Connection - you should get a 'OK' if it worked.

Now skip the advanced tab (you can explore this later!) and move to the All

tab. Highlight application name and click the Edit Value button. For this

article I'm using the value of TestApp.

Click the OK button. Rename the connection.udl file to connection.txt, then

double click to open. You should see something like this:

[oledb]

; Everything after this line is an OLE DB initstring

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=eg;Application Name=TestApp

Look familiar? All you have to do is cut and paste that line into your code.

No syntax errors, no trying to figure out if the password is wrong. Now let's

actually DO something with this connection!

Connection objects can only execute "action" queries (insert,

update, and delete). Here are some examples:

oConnection.execute "Delete from

Customers_History"

oConnection.execute "Truncate

table Customers_History"

oConnection.execute "Insert Into

SomeTable (CategoryID, CategoryName) values (1,'ADO Articles')

oConnection.execute "Update

SomeTable set CategoryName='ADO Articles & Tips' where CategoryName='ADO

Articles'

You can also do cross database actions just like you would in Query Analyzer:

oConnection.execute "Truncate table

Pubs.dbo.Publishers"

You can also execute stored procedures, in this example passing the parameter

'5' to the custOrderHist stored procedure:

oConnection.execute "custOrderHist

5"

When you're done, you always want to do a good clean up to avoid memory leaks

and to release the connection.

oConnection.close

set oConnection = nothing

Not too bad so far is it? Next week we'll talk about how to begin using the

recordset object which will leverage what we covered in this article. As you

test these, please do so in a test environment! As always, I'd love to hear any

comments or questions you have about the article - just click the 'Your Opinion' tab below.

Rate

5 (3)

Share

Share

Rate

5 (3)