How to Create a VBScript ODBC Connection

One of the most common uses for VBScript is to connect databases. Believe it or not, the process is relatively easy, and even beginners can establish a connection with ease. Here's everything you need to know on how to create a VBScript ODBC Connection.

What Are the Prerequisites for Establishing a Connection Using VBScript?

A database, of course
Any data source, which can be another excel file, CSV file, or any other data source

For the most part, VBScript developers tend to use Microsoft’s ActiveX database objects (ADO) to connect and extract data from various data sources.

An ADODB is comprised of 3 different objects:

Connections
RecordSets
Commands

Connecting to a Database

A data source is essentially a connection from a server or a workstation to a database. This can be on an isolated machine running SQL Server or a database file on the webserver. To specify the type of database, you need to identify and add a Data Source Name (DSN).

Data Source Names establish a connection to a database through an ODBC driver. A DSN would further contain the following details:

Database name
Directory
Database driver
UserID
Password

As soon as you create a DSN, you can use it within an application to pull information from the resident database.

DSNs can further be divided into two parts:

System DSN: A system DSN refers to a data source created on the web by the server’s admin.
File DSN: File DSN refers to the connection a script makes every time a database is accessed. VBScript needs to have the path and name of the database embedded within it. Furthermore, the database needs to reside on the server within a directory for your script to work.

What Is an ODBC Connection?

Open Database Connectivity (ODBC) is a protocol used to connect an MS Access database to external data, such as an SQL Server.

Adding an ODBC Connection Within Your System

Before proceeding, it’s essential to install the appropriate ODBC driver for the data source you want to connect to.
Click Start, and then Control Panel.

From the Control Panel, open Administrative Tools.

In the Administrative Tools dialog box, click on ODBC Data Sources (32-bit) or ODBC Data Sources (64-bit), depending upon your system.

The ODBC Data Source Administrator dialog box will appear once you reach the next screen.
Click User DSN, System DSN, or File DSN depending on the type of data source you want to add.
Click Add.
Select the driver you want to use, followed by Finish or Next.

Follow the instructions and enter the required connection information in any dialog boxes that follow.

Establishing a Database Connection Using ADODB Connection Object

        Set obj = createobject(“ADODB.Connection”) ‘<strong>Creating an ADODB Connection Object</strong>
Set obj1 = createobject(“ADODB.RecordSet”) ‘<strong>Creating an ADODB Recordset Object</strong>
Dim dbquery <strong>‘Declaring a database query variable bquery</strong>
Dbquery=”Select acctno from dbo.acct where name = ‘Wini Bhalla’” <strong>‘Creating a query</strong>
obj.Open“Provider=SQLQLEDB;Server=.\SQLEXPRESS;UserId=test;Password=P@123;Database =AUTODB” <strong>‘Opening a Connection </strong>
obj1.Open dbquery,obj <strong>‘Executing the query using recordset </strong>
val1 = obj1.fields.item(0) <strong>‘Will return field value</strong>
msgbox val1<strong> ‘Displaying value of the field item 0 i.e. column 1</strong>
obj.close <strong>‘Closing the connection object</strong>
obj1.close <strong>‘Closing the connection object</strong>
Set obj1=Nothing ‘<strong>Releasing Recordset object</strong>
Set obj=Nothing ‘<strong>Releasing Connection object</strong>

Setting Up Your ODBC Connection

Establishing a connection with your database is quite easy with an ODBC connection, even if you are not a programming expert. At the end of the day, we all do make mistakes, and that's the best way to learn programming.