SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Introduction to the ADO Connection Object

By Andy Warren, (first published: 2001/11/08)

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:

; 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.


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.

Total article views: 30986 | Views in the last 30 days: 10
Related Articles

DB@ Linked Server - Cannot initialize the data source object of OLE DB provider "IBMDASQL"

Cannot initialize the data source object of OLE DB provider "IBMDASQL"


SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - C...


Clicking on Links in Email

Every security awareness presentation makes the warning about opening attachments or clicking on lin...


provider: TCP Provider, error: 0 ...

provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote

stored procedures