Saturday, June 21, 2014

VB Scirpt || ADODB Connection Code

PUBLIC Records_Count
PUBLIC Ret_Val

DB_ServerConn_String = "HOST=< Server IP addr or name>;PORT=<server prot num>;SERVICE_NAME=<servicename or sid>;uid=<Username>;pwd=<password>"
OR
DB_ServerConn_String = "DSN=<predefinedDSNCOnnectionName>;USERID=<Username>;Password='<password>'"
Click here To find Examples for Connection string

Function Execute_Query(DB_ServerConn_String ,QueryString)
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
'Const adOpenStatic = 3
'Const adLockOptimistic = 1 ''adLockReadOnly
'Const adUseClient = 2 ''adUseServer

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

objConnection.Open DB_ServerConn_String
If objConnection.state=1 Then
objConnection.CommandTimeout=0
objRecordset.CursorLocation = adUseClient
objRecordset.Open QueryString , objConnection, adOpenStatic, adLockOptimistic
If objRecordset.BOF<>true and objRecordset.EOF<>true Then
If objRecordset.RecordCount<>0 Then
                                                Records_Count=objRecordset.RecordCount
Ret_Val=objRecordset.GetRows
else
Ret_Val="No Records Found"
End If
else
Ret_Val="No Records Found"
End If
objRecordset.Close
else
Ret_Val="Not Connected"
End If

objConnection.Close
Execute_Query= Ret_Val
       
End Function