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