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.
