Database connection

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: 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 "Provider=msdaora;Data Source=OracleDB; Uid=username;Pwd=password;" "select * from emp",objcon

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

'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.
Avatar photo

Shekhar Sharma

Shekhar Sharma is founder of This website is his window to the world. He believes that ,"Knowledge increases by sharing but not by saving".

You may also like...