‘Declare a connection variable

Dim strConnection         

‘Assign connection details to connection variable

strConnection= "Driver= {Microsoft ODBC for Oracle}; " &_

                  "ConnectString=(DESCRIPTION=" &_

                                                                  "(ADDRESS=(PROTOCOL=Protocol Type)" &_

                                                                  "(HOST=host name) (PORT=port number))" &_

                                                                  "(CONNECT_DATA=(SERVICE_NAME=service name)));” &_

         uid=userid; pwd=password;"

 

‘Create Connection and Recordset objects

Dim obConnect

Dim obRecset

Set obConnect =CreateObject("ADODB.Connection")

Set obRecset = CreateObject("ADODB.Recordset")

 

‘Establish the connection

obConnect.Open strConnection

‘Define a variable to hold the database query

Dim queryStr

‘Assign the query to the variable

queryStr = "SELECT emp_id FROM employee WHERE name LIKE 'S%' ”

‘Execute the query

Set obRecset = obConnect.Execute(queryStr)

'Loop untill all the records are fetched            

While obRecset.EOF = False 

getEmployeeId = obRecset.Fields(0).Value

Msgbox getEmployeeId

Wend