How do I rewite this code to connect to a Domino DECS connection vs an ADO connection?

Need help rewriting this.
I need to connect to a DECS connection already established. (same name).
-----------------------------------------------------------------------
Sub Initialize
      Dim session             As New NotesSession
      Dim db                         As NotesDatabase
      Dim EODMark As String, tempPONumb As String, currPO As String, povend As String, postat As String, sql As String
      Dim GetNextPO       As Integer
      Dim poline                   As Integer, cnt As Integer
      Dim tdoc                   As NotesDocument
      Dim ItemView            As NotesView
      Dim tempdate            As Variant
      Dim datediff             As Double
      Dim ret                         As Long
      Dim cmd                   As Variant
      Dim cols                   As Variant
      Dim tval                         As Variant
      Dim tDesc                  As Variant
      
      Set db = session.CurrentDatabase
      Set ItemView = db.GetView("(Items)")
      
'      ret = Messagebox("Use ADO (Yes) or RDO (No)", 4, "Question")
'      If ret = 6 Then
      
      Set con = CreateObject("ADODB.Connection.2.5")
      Set res = CreateObject("ADODB.Recordset.2.5")
      If con.State = adStateOpen Then
            res.Cancel
            con.Cancel
      End If
      con.ConnectionString = "uid=sa;pwd=sas;DSN=Warehouse;RO=True;FBS=50;"    
      con.Open
      
      SQL = "Select pt_part, pt_desc1 From PT_MSTR where pt_part like '1%'"
      
      Set res = con.Execute(sql, ret, adCmdTable)
      cnt = 0
      While res.EOF = False
            tval = res(0).Value
            If Instr(1, tval, "40477", 0) > 0 Then
                  tval = res(0).Value
            End If
            
            tDesc = res(1).Value
            Set tdoc = ItemView.getDocumentbyKey(tval)
            If tdoc Is Nothing Then
                  Set tdoc = db.CreateDocument
                  tdoc.Form = "(Items)"
                  tdoc.ItemNumber = tval
                  tdoc.ItemDesc = tDesc
                  Call tdoc.Save(True, True)
            End If
            cnt = cnt + 1
            res.MoveNext
      Wend
      res.Cancel
      con.Cancel
      con.Close
      
'      Else
'            Set con = CreateObject("MicrosoftRDO.RdoConnection2.0")
'            con.close
'            con.Connect = "uid=sa;pwd=sas;DSN=Warehouse;RO=True;FBS=50;"
'            con.cursordriver = 1
'            con.EstablishConnection 1
'            SQL = "Select pt_part, pt_desc1 From PT_MSTR where pt_part like '4%'"
'            Set rs = con.OpenResultSet(SQL,rdOpenDynamic,rdConcurReadOnly)
'            cnt = 0
'            rs.MoveNext
'            While rs.EOF = False
'                  tval = rs(0).Value
'                  tDesc = rs(1).Value
'                  Set tdoc = ItemView.getDocumentbyKey(tval)
'                  If tdoc Is Nothing Then
'                        Set tdoc = db.CreateDocument
'                        tdoc.Form = "(Items)"
'                        tdoc.ItemNumber = tval
'                        tdoc.ItemDesc = tDesc
'                        Call tdoc.Save(True, True)
'                  End If
'                  
'                  cnt = cnt + 1
'                  rs.MoveNext
'                  
'             Wend
'            rs.close
'            con.close
'      End If  
      
End Sub

----------------------------------------------------------------------
rbendAsked:
Who is Participating?
 
qwaleteeCommented:
You ask for a lot for 50 points!  Anyway...

In what way does it bomb?

Perhaps you do not have ADO installed, or the wrong version of ADO.

Don't forget, this code has to execute on a particular box. If this is a web application, then the code must be in an agent that runs on the server.  If it is a Notes client application, then it will execute on the Notes client, unless it is an agent that is set to run on the server.  Whichever box it executes on has to be a Widnows box (CreateObject uses COM/OLE/ActiveX, which only exists under Windows), and the component being loaded (ADODB.Connection.2.5) has to available on the same box.

It looks like LS:DO would be an easier conversion for you in this case, since it is less complex.  The following MIGHT do it:

In declarations, you need: Use "*lsxodbc"

In you code you need:

Dim con as New ODBConnection
Dim qry as ODBCQuery
Dim res as ODBCResultSet

Set qry.Connection = con
con.ConnectTo "Warehouse"
qry.SQL =  "Select pt_part, pt_desc1 From PT_MSTR where pt_part like '1%'"

Set res.Query = qry
res.Execute
While Not res.IsEndOfData
    tval = res.GetValue(1) 'not GetValue(0) -- equivalent to res(0).value in your code
etc.etc.etc.
    res.nextRow 'equivalent to res.MoveNext in your code
Wend
0
 
qwaleteeCommented:
You would nly use a DECS connection if you wanted Domino to automatically pull in the SQL results as the form opens, and place them intop the designated fields.

Also, DECS can't be scripted -- you basically tel it what you want tomatch up, and it either maps or it does not map.  You can't do much logic in it.  If you want logic, you have to go with LEI.  Or you can use LSXLC, which is the DECS connection but using LotusScript (similar to ADO).  However, DECS and LEI both run at the server, while LSXLC runs at the client.  (LSXLC can run on the server, but there is simple way to trigger it without LEI).

Why don't you explain WHY you need to switch ADO to DECS, and what the context ofthe bussiness case is?
0
 
rbendAuthor Commented:
OK...we havn't had this script running in a while and we have since changed boxes.
There wasn't a DSN set up on the box the parameters of which are not known.
All we know is it ran fine.
Now, there is a SQL DSN set up on the box and it checks out.
Not sure what we're missing to make th above script work, but when I invoke DEBUG, it bombs at the line " Set con = CreateObject("ADODB.Connection.2.5")"
I don't know enough at this point to be able to know why.
It was suggested that we use DECS, but again, I'm swirling and don't know how to employ it correctly.
I'd like to leave the script as is and get whatever I'm missing going on the other end.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.