Database connection

by Shekhar Sharma · July 16, 2015

For database connection, mainly we need 4 components as mentioned in the table below.

Connection object	First step will be to establish the database connection.
Record set object	Record set is used to fetch the record values from the database.
Connection string	It helps to connect to a particular database. It contains server details,username and password.
SQL Query	The query which you want to execute.

Note:

Connection String may vary depending on the type of database you are going to use.
e.g.For oracle DB: “Provider=msdaora;Data Source=OracleDB; Uid=username;Pwd=password;”
For SQLserver: {SQLServer};server=MySqlServer;uid=UserName;pwd=Password;database=mydb”

We will take the example of oracle database. For working with any other database you just need to change the connection string.

Option Explicit
Dim objcon,objrs

'Create a connection object and record set object
Set objcon=createobject("adodb.connection")
Set objrs=createobject("adodb.recordset")

'Open the connection with a particular connection string
objcon.open "Provider=msdaora;Data Source=OracleDB; Uid=username;Pwd=password;"
objrs.open "select * from emp",objcon

Do While Not objrs.eof
	Print objrs.fields("firstcol")
	Print objrs.fields("secondcol")
	objrs.movenext
Loop
'close the connection after use
objcon.close

'Releasing the objects
Set objrs= nothing
Set objcon= nothing

Option Explicit

Dim objcon,objrs

'Create a connection object and record set object

Set objcon=createobject("adodb.connection")

Set objrs=createobject("adodb.recordset")

'Open the connection with a particular connection string

objcon.open "Provider=msdaora;Data Source=OracleDB; Uid=username;Pwd=password;"

objrs.open "select * from emp",objcon

Do While Not objrs.eof

Print objrs.fields("firstcol")

Print objrs.fields("secondcol")

objrs.movenext

Loop

'close the connection after use

objcon.close

'Releasing the objects

Set objrs= nothing

Set objcon= nothing

You can check the state of the connection as well to handle any error,if required.
syntax: objcon.state

Constant	Value	Description
StateClosed	0	The object is closed.
StateOpen	1	The object is open.
StateConnecting	2	The object is connecting.
StateExecuting	4	The object is executing a query.
StateFetching	8	It is fetching the rows.

For record set ,there are some useful operation which you can use depending on your requirement.

movenext	Move to the next record.
moveprevious	Move to the previous record.
movefirst	Move to the first record.
movelast	Move to the last record.
objrs.field.item(i)	Returns the specified item from field.
objrs.fields.count	returns the no. of items in field collection.