Display Attachment from another table

[illini85]

I have split my data and attachments into 2 tables because my VBA code that runs queries fails when the Attachment field is in the data table
Unfortunately I can't get the Attachments to display on the form. I assume I need to use a subform, but I can't see the Attachment field in the list of fields to add. I only see the ID that links the tables.
Any help is appreciated. The realization that Attachment fields can break query code even though that filed is not referenced is a huge problem.

 

[MajP]

Attachment fields can break query code even though that filed is not referenced is a huge problem

I know of no issues with attachment fields and queries. What version of Access. No problem on my end.
Since you do not have multiple child records no need for a subform. Can you take a screen capture of the issue. If you have two tables and they are linked by ID then you can select the fields from either table. Not sure of the issue you describe.

 

[illini85]

Access 2010.
The query runs in Access, but this VBA fails with the error "Invalid Operation run-time error -2147467259 (80004005)" if the final table field is an Attachment. It works otherwise.
Attached is the Main table from which the query runs. It retrieves records from a Type of inspection that is still open and was opened between 2 dates.

xlWB.Sheets("Volume").Select
With tmpCmd
For i = 1 To 2
.CommandText = "qryVolume"
.Parameters.Append .CreateParameter("param1", adInteger, adParamInput, adChar, i)
.Parameters.Append .CreateParameter("param2", adVarWChar, adParamInput, adChar, Me.Start_Date)
.Parameters.Append .CreateParameter("param3", adVarWChar, adParamInput, adChar, Me.End_Date)
Set rs = .Execute
xlWB.ActiveSheet.Range(IIf(i = 1, "A", "E") & 3).CopyFromRecordset rs
Call DeleteParameters(tmpCmd)
Next i
End With

qryVolume is

SELECT Main.Category AS Category, Count(Main.Category) AS [Count]
FROM Main
WHERE (((Main.Type)=[Type: 1=Safety; 2 = Maintenance]) AND ((Main.[End Date]) Is Null) AND ((Main.[Start Date]) Between [Start Date - Start] And [Start Date - End]))
GROUP BY Main.Category
HAVING (((Count(Main.Category))<>0));

 

[illini85]

Incredibly, in code, prior to that query failing, this query works in VBA (!)
I have no idea why this would work while the earlier (and simpler) query fails!

xlWB.Sheets("Aging").Select
With tmpCmd
For i = 1 To 2
.CommandText = "qryAgingClosed"
.Parameters.Append .CreateParameter("param1", adInteger, adParamInput, adChar, i)
.Parameters.Append .CreateParameter("param2", adVarWChar, adParamInput, adChar, Me.Start_Date)
.Parameters.Append .CreateParameter("param3", adVarWChar, adParamInput, adChar, Me.End_Date)
Set rs = .Execute
xlWB.ActiveSheet.Range(IIf(i = 1, "A", "E") & 3).CopyFromRecordset rs
Call DeleteParameters(tmpCmd)
Next i
End With

where qryAgingClosed is this (it places Closed inspections into buckets of Aging days ranges):

SELECT IIf(DateDiff("d",[Start Date],[End Date])<=1,"0-1 Days",IIf(DateDiff("d",[Start Date],[End Date]) Between 2 And 5,"2-5",IIf(DateDiff("d",[Start Date],[End Date]) Between 6 And 10,"6-10",IIf(DateDiff("d",[Start Date],[End Date]) Between 11 And 30,"11-30",IIf(DateDiff("d",[Start Date],[End Date])>=31,"31+"))))) AS Aging, Count([Aging]) AS [Count]
FROM Main
WHERE (((Main.Type)=[Type: 1=Safety; 2 = Maintenance]) AND ((Main.[End Date]) Between [Close Date - Start] And [Close Date - End]))
GROUP BY IIf(DateDiff("d",[Start Date],[End Date])<=1,"0-1 Days",IIf(DateDiff("d",[Start Date],[End Date]) Between 2 And 5,"2-5",IIf(DateDiff("d",[Start Date],[End Date]) Between 6 And 10,"6-10",IIf(DateDiff("d",[Start Date],[End Date]) Between 11 And 30,"11-30",IIf(DateDiff("d",[Start Date],[End Date])>=31,"31+")))));

 

[MajP]

To verify
This query on table Main does not work if the attachment field is in the table, but works if the attachment field is removed from the table?

SELECT Main.Category AS Category, Count(Main.Category) AS [Count]

FROM Main

WHERE (((Main.Type)=[Type: 1=Safety; 2 = Maintenance]) AND ((Main.[End 
Date]) Is Null) AND ((Main.[Start Date]) Between [Start Date - Start] 
And [Start Date - End]))

GROUP BY Main.Category

HAVING (((Count(Main.Category))<>0))

This query works if the attachment field is in the table?

SELECT IIf(DateDiff("d",[Start Date],[End Date])<=1,"0-1 
Days",IIf(DateDiff("d",[Start Date],[End Date]) Between 2 And 5,"2-
5",IIf(DateDiff("d",[Start Date],[End Date]) Between 6 And 10,"6-
10",IIf(DateDiff("d",[Start Date],[End Date]) Between 11 And 30,"11-
30",IIf(DateDiff("d",[Start Date],[End Date])>=31,"31+"))))) AS Aging, 
Count([Aging]) AS [Count]

FROM Main

WHERE (((Main.Type)=[Type: 1=Safety; 2 = Maintenance]) AND ((Main.[End 
Date]) Between [Close Date - Start] And [Close Date - End]))

GROUP BY IIf(DateDiff("d",[Start Date],[End Date])<=1,"0-1 
Days",IIf(DateDiff("d",[Start Date],[End Date]) Between 2 And 5,"2-
5",IIf(DateDiff("d",[Start Date],[End Date]) Between 6 And 10,"6-
10",IIf(DateDiff("d",[Start Date],[End Date]) Between 11 And 30,"11-
30",IIf(DateDiff("d",[Start Date],[End Date])>=31,"31+")))))

Also I still do not understand what this means

I can't see the Attachment field in the list of fields to add. I only see the ID that links the tables.

Screen shot would help. On a related note, I would shoot myself trying to get the second query corret. Could be radically simplified with a table

tblBuckets  
  BucketStart
  BucketEnd
  BucketLabel

 0  1  O-1
 2  5  2-5
 ...
 31 9999999 31+    (assuming you know a viable max number)

Since this is an attachment field I assume you are working with Access only. So I recommend doing this in DAO instead of ADODB. If the issue is really caused by the attachment field I have to assume DAO would handle it better since it is optimized for JET.

 

[illini85]

Thanks for helping with this crazy problem!

Both queries work in Access, with or without the Attachments field.
qryAgingClosed works in code, with or without the Attachments field.
qryVolume works in code without the Attachments field, but fails in code with the Attachments field. It fails at the statement in code below "Set rs = .Execute".

Attached is Main, a stripped down version of my table. If you want, please create this table without the Attachments field and add 2 records such as:

Start Date End Date Type Category
2/8 2/15 1 Storage
3/1 1 Fire Door [don't enter an End Date]

Then run this code after putting in a Module.
I removed the Excel references to keep it simple. You will need to add Tools->References to the 2 ADO 2.8 libraries.
It should run to completion. Then add the field Attachments and run the code. It will fail on qryVolume.

Sub Test_Attachment_Issue()

    Dim rs As Object, cn As Object, tmpCmd As New ADODB.Command
    Dim i As Integer
    
    Set cn = Application.CurrentProject.Connection
    Set rs = CreateObject("ADODB.Recordset")
    
    With tmpCmd
        .ActiveConnection = cn
        .CommandType = adCmdStoredProc
    End With
    
    With tmpCmd
        For i = 1 To 2
            .CommandText = "qryAgingClosed"
            .Parameters.Append .CreateParameter("param1", adInteger, adParamInput, adChar, i)
            .Parameters.Append .CreateParameter("param2", adVarWChar, adParamInput, adChar, Me.Start_Date)
            .Parameters.Append .CreateParameter("param3", adVarWChar, adParamInput, adChar, Me.End_Date)
            Set rs = .Execute
            Call DeleteParameters(tmpCmd)
        Next i
    End With
    
    With tmpCmd
        For i = 1 To 2
            .CommandText = "qryVolume"
            .Parameters.Append .CreateParameter("param1", adInteger, adParamInput, adChar, i)
            .Parameters.Append .CreateParameter("param2", adVarWChar, adParamInput, adChar, Me.Start_Date)
            .Parameters.Append .CreateParameter("param3", adVarWChar, adParamInput, adChar, Me.End_Date)
            Set rs = .Execute
            Call DeleteParameters(tmpCmd)
        Next i
    End With
    
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing

End Sub

Public Sub DeleteParameters(tmpCmd As ADODB.Command)

    Dim i As Integer
    
    For i = 0 To tmpCmd.Parameters.Count - 1
      tmpCmd.Parameters.Delete (0)
    Next i
    
End Sub

 

[MajP]

I ran this is DAO with out any problem and had an attachement field.

Sub Test_Attachment_Issue()
    'Test this with a table and attachement field
    Dim rs As DAO.recordset
    dim strSql as string
    dim start_Date as string
    dim End_Date  as string
   
    start_date = Format(me.Start_Date,"mm/dd/YYYY")
    start_date = "#" & start_Date & "#"
    end_Date = format(me.End_Date,"mm/dd/yyyy")    
    end_date = "#"& end_date & "#"

    strSql = "SELECT Category, Count(Category) AS [Count] from Main Where Type = 1  AND [End Date] is null AND [Start Date] "
    strSql = Strsql & "BETWEEN " & Start_date & " AND " & End_Date & " GROUP BY Category HAVING Count(Category) <> 0"
    debug.print strSql 'verify this is a good string
    'Mine: SELECT Category, Count(Category) AS [Count] from Main Where Type = 1  AND [End Date] is null AND [Start Date] 
    '       BETWEEN #06/03/2018# AND #07/09/2018# GROUP BY Category HAVING Count(Category) <> 0
    set rs = currentDb.openrecordset (strSql, dbopendynaset)
    Do while not rs.eof
      debug.print RS!category & " count " & rs![count]
      rs.movenext
    loop
End Sub

 

[illini85]

Thank you! It works perfectly with DAO!

 

[MajP]

I tested this and you are correct. I built something from scratch and works fine adding parameters and then running the code. When you add an attachment field to the table it then fails even if the attachment field is not part of the query. If you build the sql string instead of a parameter query it will work. Never heard of this issue nor can I find a reference to it.

 

[illini85]

DOA to the rescue!