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. |

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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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 |
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. |