none
Problem with automation access recordset object in outlook RRS feed

  • Question

  • I need to automate the use of access in Outlook vba. I am trying to return a recordset which I do, but it returns inconsistently from when I paste the same sql into a query window, which returns rows, while the automation object does not.  

    Private Sub InboxItems_ItemAdd(ByVal Item As Object)
        Dim rstAuto As ADODB.Recordset, ConnAuto As ADODB.Connection
        Dim Subj$, County$, HomePath$, Sndr$, Pth$, FileBase$, Ext$, sFile$, BT$, AutoPth$
        Const CONN_STR$ = "Provider=Microsoft.Jet.OLEDB.4.0;User Id=admin;Password=;Data Source=C:\Working\Access\All County\Outlook Autosaver.mdb;"
    
        Set Msg = Item
        Subj = Msg.Subject
        Sndr = Msg.SenderEmailAddress
        BT = Msg.Body
        Cnt = Msg.Attachments.Count
        On Error Resume Next
        
        If Cnt > 0 Then
            BT = Replace(BT, "'", "")
            BT = Replace(BT, """", "")
            Set ConnAuto = AccAuto.CurrentProject.Connection
            AccAuto.DoCmd.RunSQL "DELETE * FROM [Body Text]"
            AccAuto.DoCmd.RunSQL "INSERT INTO [Body Text]([Body Text]) VALUES('" & BT & "')"
            
            On Error GoTo ErrorHandler
            
            Set rstAuto = ConnAuto.Execute("SELECT [Description], [Search], [Path], [Search By], [Match] " _
                & "FROM [Emails] " _
                & "WHERE [Search By] = 1 And '" & Subj & "' Like [Search]")
            
            DoEvents
            Debug.Print Not (rstAuto.BOF And rstAuto.EOF)
        End If
    End Sub
    

    Any help on this matter is appreciated

    50% of programming is coding. The other 90% is debugging

    Sunday, July 31, 2016 12:38 AM

Answers

  • >>>If I change the SQL to "SELECT * FROM [Emails]" or "SELECT * FROM [Emails] WHERE [Search By] = 1" it works fine. Making the criteria a little more complex fails. It returns a recordset but it has no pointer<<<

    According to your description, I suggest that you could change your * to % as % is the wildcard search when using OLE DB, like below:
    SELECT * FROM Emails WHERE [Search] LIKE '%XXXX%'

    • Proposed as answer by David_JunFeng Sunday, August 7, 2016 2:15 PM
    • Marked as answer by David_JunFeng Monday, August 8, 2016 1:40 AM
    Monday, August 1, 2016 5:23 AM

All replies

  • The variable AccAuto is not declared. You should insert an "Option Explicit" at the top of the module (of every module), and you should compile the application.

    Furthermore, comment out the "On Error Resume Next" for debugging. This statement should be only used together with a check of possible expected errors.

    Also the label "ErrorHandler" is not defined.

    Matthias Kläy, Kläy Computing AG

     
    Sunday, July 31, 2016 12:01 PM
  • AccAuto is a module level variable. I do have Option Explicit in effect. I cut out the parts irrelevant to the question so some of the declarations and assignments aren't shown in my code snippet. I did get away with using Dlookup instead of creating a recordset object but I am still not clear why a perfectly good query would always return an empty recordset

    50% of programming is coding. The other 90% is debugging

    Sunday, July 31, 2016 2:36 PM
  •        Set rstAuto = ConnAuto.Execute("SELECT [Description], [Search], [Path], [Search By], [Match] " _
                & "FROM [Emails] " _
                & "WHERE [Search By] = 1 And '" & Subj & "' Like [Search]")
            
    

    Hi Jon,

    In the above query you use a quoted fieldname and an unquoted Search value as literal.

    You could try:

                & "WHERE [Search By] = 1 And " & Subj & " Like '" & [Search] & "'"
    

    Imb.

    h

    Sunday, July 31, 2016 5:14 PM
  • Like Imb says, the syntax of the Like clause is unusual, although not wrong. But perhaps it does not evaluate to what you expect. Should it be

    "WHERE [Search By] = 1 And " & [Search] & " Like '" & Subj & "'"
     

    Also there seems to be no wildcard in the search expression, thus Like is the same as =.

    Matthias Kläy, Kläy Computing AG

     
    Sunday, July 31, 2016 7:23 PM
  • That is by design. The structure of the table 'Email' is:

         Description
         Search
         Path
         Search By

    The value in the [Search] field may be a wildcard like "This is the * email subject text*", the value in the variable Subj is compared to if [Search By] = 1. At the end it reads like "SELECT * FROM [Emails] WHERE [Search By] = 1 And 'New email subject' Like [Search];      ' where the value in [Search] may look something like '*email subj*'

    50% of programming is coding. The other 90% is debugging

    Sunday, July 31, 2016 7:56 PM
  • I paste this exact sql in the query grid and it gives me exactly what I want. I have tried all kinds of ways to setup a connection and recordset pair each raising some error or another

    50% of programming is coding. The other 90% is debugging

    Sunday, July 31, 2016 7:57 PM
  • Then the problem is not the query but the connection. You should show us what you tried and what errors are raised.

    Matthias Kläy, Kläy Computing AG

    Sunday, July 31, 2016 9:10 PM
  • here is a sample of what I've got. The sql is good only when entered directly in access, not good in automation

    'Module Level

    Dim AccAuto As Access.Application

    Sub SetupConnection()
        Dim rst As ADODB.Recordset, con As ADODB.Connection
        Const AcDbPth$ = "c:\working\access\all county\outlook autosaver.mdb"
       
        On Error GoTo ErrorHandler
        Set AccAuto = GetObject(AcDbPth, "Access.Application")
       
        If AccAuto Is Nothing Then
            Set AccAuto = CreateObject("Access.Application")
            AccAuto.OpenAccessProject AcDbPth
        End If
       
    '    AccAuto.Visible = True
        AccAuto.DoCmd.RunSQL "DELETE * FROM [Subjects]"
        AccAuto.DoCmd.RunSQL "INSERT INTO [Subjects]([Subject]) VALUES('FW: Traffic_DailyDispositions was executed at 7/31/2016 5:17:01 AM')"
        Set con = AccAuto.CurrentProject.Connection
        Set rst = con.Execute("SELECT * FROM [Emails], [Subjects] WHERE [Search By] = 1 And [Subject] Like [Search]")
        If Not (rst.BOF And rst.EOF) Then rst.MoveFirst
       
        Do Until rst.EOF
            Debug.Print rst![Path]
            rst.MoveNext
        Loop
       
    Egress:
        rst.Close
        Set rst = Nothing
        Set con = Nothing
        Exit Sub
       
    ErrorHandler:
        Debug.Print Err.Description
        Stop
        Resume
    End Sub


    50% of programming is coding. The other 90% is debugging

    Sunday, July 31, 2016 9:52 PM
  • If I change the SQL to "SELECT * FROM [Emails]" or "SELECT * FROM [Emails] WHERE [Search By] = 1" it works fine. Making the criteria a little more complex fails. It returns a recordset but it has no pointer

    50% of programming is coding. The other 90% is debugging

    Sunday, July 31, 2016 9:54 PM
  • >>>If I change the SQL to "SELECT * FROM [Emails]" or "SELECT * FROM [Emails] WHERE [Search By] = 1" it works fine. Making the criteria a little more complex fails. It returns a recordset but it has no pointer<<<

    According to your description, I suggest that you could change your * to % as % is the wildcard search when using OLE DB, like below:
    SELECT * FROM Emails WHERE [Search] LIKE '%XXXX%'

    • Proposed as answer by David_JunFeng Sunday, August 7, 2016 2:15 PM
    • Marked as answer by David_JunFeng Monday, August 8, 2016 1:40 AM
    Monday, August 1, 2016 5:23 AM
  • Bingo

    Thanks


    50% of programming is coding. The other 90% is debugging

    Monday, August 1, 2016 6:48 AM