Problem with automation access recordset object in outlook

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

Answer 1

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

 

Answer 2

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

Answer 3

       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

Answer 4

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

 

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

>>>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%'

Answer 11

Bingo

Thanks

---

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