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