How to connect database using VBScript- Complete tutorial for SQL, Oracle, MySQL, Excel, Sybase, MS Access

VBScript provides one of the best ways to connect to any kind of database. That is through the ADODB connection. Lets see how to connect database using VBScript

You just need to have the correct connection string with the right credentials. If you have these two then you can write code to connect to almost any kind of database in just 3 lines.

In order to connect to the database, we need to follow below steps:

1. Create an object of ADODB, like below: 

Set con=createobject("adodb.connection")

2. Create an object of recordset :

Set rs=createobject("adodb.recordset")

3. Open the Connection using the connection string

con.open"Driver={SQL Server};server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs"

4. Run the query using the connection object.

rs.open "select * from emp",con

This will store the data into the recordset object rs.

This page has all the code needed for connecting and reading data from popular database types using VBscript.

SQL Database – Connect database using VBScript

Option Explicit
Dim con,rs

Set con=createobject("adodb.connection")
Set rs=createobject("adodb.recordset")

con.open"Driver={SQL Server};server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs"
rs.open "select * from emp",con

Do while not rs.eof
  VbWindow("Form1").VbEdit("val1").Set rs.fields("v1")
  VbWindow("Form1").VbEdit("val2").Set rs.fields("v2")
  VbWindow("Form1").VbButton("ADD").Click
  rs.movenext
Loop

'Release objects'Release objects
Set rs= nothing
Set con= nothing

Oracle Database – Connect database using VBScript

Option Explicit
Dim con,rs

Set con=createobject("adodb.connection")
Set rs=createobject("adodb.recordset")

con.open "Driver={Microsoft ODBC for Oracle};Server=QTPWorld; Uid=your_username;Pwd=your_password;"
rs.open "select * from emp",con

Do while not rs.eof
  VbWindow("Form1").VbEdit("val1").Set rs.fields("v1")
  VbWindow("Form1").VbEdit("val2").Set rs.fields("v2")
  VbWindow("Form1").VbButton("ADD").Click
  rs.movenext
Loop

'Release objects
Set rs= nothing
Set con= nothing

MySQL Database – Connect database using VBScript

Option Explicit
Dim con,rs

Set con=createobject("adodb.connection")
Set rs=createobject("adodb.recordset")

con.open"Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=myDB;User=Uname;Password=Pwd;Option=3;"
rs.open "select * from emp",con

Do while not rs.eof
  VbWindow("Form1").VbEdit("val1").Set rs.fields("v1")
  VbWindow("Form1").VbEdit("val2").Set rs.fields("v2")
  VbWindow("Form1").VbButton("ADD").Click
  rs.movenext
Loop

'Release objects
Set rs= nothing
Set con= nothing

Excel file as Database- Connect database using VBScript

Option Explicit
Dim con,rs

Set con=createobject("adodb.connection")
Set rs=createobject("adodb.recordset")

con.open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=C:TestStatus.xls;Readonly=True"
rs.open "SELECT count(*) FROM [Status$] where Status = 'Failed' ",con

Msgbox rs(0)

'Release objects
Set rs= nothing
Set con= nothing

Sybase Database- Connect database using VBScript

Option Explicit
Dim con,rs

Set con=createobject("adodb.connection")
Set rs=createobject("adodb.recordset")

' Open a session to the database
con.open"Driver={SYBASE SYSTEM 11};Srvr=myServerAddress;Uid=Uname;Pwd=Pwd;Database=myDataBase;"
rs.open "select * from emp",con

Do while not rs.eof
  VbWindow("Form1").VbEdit("val1").Set rs.fields("v1")
  VbWindow("Form1").VbEdit("val2").Set rs.fields("v2")
  VbWindow("Form1").VbButton("ADD").Click
  rs.movenext
Loop

'Release objects
Set rs= nothing
Set con= nothing

MS Access Database- Connect database using VBScript

Option Explicit
Dim con,rs

Set con=createobject("adodb.connection")
Set rs=createobject("adodb.recordset")

con.open "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:mydatabase.mdb;Uid=Admin;Pwd=;"

rs.open "select * from emp",con

'Data can used as per requirement
Do while not rs.eof
  VbWindow("Form1").VbEdit("val1").Set rs.fields("v1")
  VbWindow("Form1").VbEdit("val2").Set rs.fields("v2")
  VbWindow("Form1").VbButton("ADD").Click
  rs.movenext
Loop

'Release objects
Set rs= nothing
Set con= nothing

How to get data from Excel as Database

' Get data from Excel as a Database
' Get Data from excel by using Database connection

Function GetDataFromDB(strFileName, strSQLStatement)
  Dim objAdCon, objAdRs
  Set objAdCon = CreateObject("ADODB.Connection")
  objAdCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="&strFileName&";Extended Properties='Excel 12.0 Xml;HDR=YES';"

  Set objAdRs =objAdCon.Execute (strSQLStatement)
  GetDataFromDB= objAdRs.Fields.Item(1)
  objAdRs.Close
  Set objAdRs.ActiveConnection = Nothing
  objAdCon.Close
  Set objAdCon = Nothing
End Function