[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


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

Posted on 2004-10-21
Medium Priority
Last Modified: 2013-12-18
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
      End If
      con.ConnectionString = "uid=sa;pwd=sas;DSN=Warehouse;RO=True;FBS=50;"    
      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
'      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

Question by:rbend
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 31

Expert Comment

ID: 12372678
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?

Author Comment

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

LVL 31

Accepted Solution

qwaletee earned 200 total points
ID: 12390850
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
While Not res.IsEndOfData
    tval = res.GetValue(1) 'not GetValue(0) -- equivalent to res(0).value in your code
    res.nextRow 'equivalent to res.MoveNext in your code

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question