By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 924 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Attachment Fields - How to Query Them

100+
P: 124
I (foolishly) made a database with several attachment fields. Now, I want to get the values from those fields as a comma separated list, like the one that shows up in a query window under the name of the field. Unfortunately Access won't let you do anything with the results of the query. You cannot make a table, you cannot make a recordset. What I've been doing is creating another table and using this code to pull the values for these multi-valued fields into a comma separated list.
Expand|Select|Wrap|Line Numbers
  1. Set DB = CurrentDb
  2. strSQL = "SELECT tblMain.MainID From tblMain Order by MainID"
  3.  
  4. Set RS = DB.OpenRecordset(strSQL)
  5.  
  6.  
  7. RS.MoveFirst
  8. Do Until RS.EOF
  9.  
  10.     'MediaReceivedReason Multi-value field*******************************************************************
  11.     strField = "MediaReceivedReason"
  12.     strMediaReceivedReason = vbNullString
  13.     strMediaTypeInSQL = "Select " & strField & ".Value AS Val From tblMain Where MainID = " & RS!MainID
  14.     Set MultiRS = DB.OpenRecordset(strMediaTypeInSQL)
  15.     Set valFLD = MultiRS.Fields("Val")
  16.     intCount = DCount(strField, "tblMain", "MainID =" & RS!MainID)
  17.     If intCount > 1 Then
  18.         MultiRS.MoveFirst
  19.         Do Until MultiRS.EOF
  20.             strMediaReceivedReason = strMediaReceivedReason & ", " & valFLD
  21.             MultiRS.MoveNext
  22.         Loop
  23.     Else
  24.         strMediaReceivedReason = Nz(valFLD, vbNullString)
  25.     End If
  26.  
  27.     If Left(strMediaReceivedReason, 2) = ", " Then
  28.         strMediaReceivedReason = Right(strMediaReceivedReason, Len(strMediaReceivedReason) - 2)
  29.     End If
  30.     '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  31.  
  32.     'MediaReturnedReason Multi-value field*******************************************************************
  33.     strField = "MediaReturnedReason"
  34.     strMediaReturnedReason = vbNullString
  35.     strMediaTypeInSQL = "Select " & strField & ".Value AS Val From tblMain Where MainID = " & RS!MainID
  36.     Set MultiRS = DB.OpenRecordset(strMediaTypeInSQL)
  37.     Set valFLD = MultiRS.Fields("Val")
  38.     intCount = DCount(strField, "tblMain", "MainID =" & RS!MainID)
  39.     If intCount > 1 Then
  40.         MultiRS.MoveFirst
  41.         Do Until MultiRS.EOF
  42.             strMediaReturnedReason = strMediaReturnedReason & ", " & valFLD
  43.             MultiRS.MoveNext
  44.         Loop
  45.     Else
  46.         strMediaReturnedReason = Nz(valFLD, vbNullString)
  47.     End If
  48.  
  49.     If Left(strMediaReturnedReason, 2) = ", " Then
  50.         strMediaReturnedReason = Right(strMediaReturnedReason, Len(strMediaReturnedReason) - 2)
  51.     End If
  52.     '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  53.  
  54.     'MediaTypeIn Multi-value field*********************************************************************
  55.     strField = "MediaTypeIn"
  56.     strMediaTypeIn = ""
  57.     strMediaTypeInSQL = "Select " & strField & ".Value AS Val From tblMain Where MainID = " & RS!MainID
  58.     Set MultiRS = DB.OpenRecordset(strMediaTypeInSQL)
  59.     Set valFLD = MultiRS.Fields("Val")
  60.     intCount = DCount(strField, "tblMain", "MainID =" & RS!MainID)
  61.     If intCount > 1 Then
  62.         MultiRS.MoveFirst
  63.         Do Until MultiRS.EOF
  64.             strMediaTypeIn = strMediaTypeIn & ", " & valFLD
  65.             MultiRS.MoveNext
  66.         Loop
  67.     Else
  68.         strMediaTypeIn = Nz(valFLD, vbNullString)
  69.     End If
  70.  
  71.     If Left(strMediaTypeIn, 2) = ", " Then
  72.         strMediaTypeIn = Right(strMediaTypeIn, Len(strMediaTypeIn) - 2)
  73.     End If
  74.     '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  75.  
  76.     'MediaTypeOut Multi-value field***********************************************************************
  77.     strField = "MediaTypeOut"
  78.     strMediaTypeOut = vbNullString
  79.     strMediaTypeInSQL = "Select " & strField & ".Value AS Val From tblMain Where MainID = " & RS!MainID
  80.     Set MultiRS = DB.OpenRecordset(strMediaTypeInSQL)
  81.     Set valFLD = MultiRS.Fields("Val")
  82.     intCount = DCount(strField, "tblMain", "MainID =" & RS!MainID)
  83.     If intCount > 1 Then
  84.         MultiRS.MoveFirst
  85.         Do Until MultiRS.EOF
  86.             strMediaTypeOut = strMediaTypeOut & ", " & valFLD
  87.             MultiRS.MoveNext
  88.         Loop
  89.     Else
  90.         strMediaTypeOut = Nz(valFLD, vbNullString)
  91.     End If
  92.  
  93.     If Left(strMediaTypeOut, 2) = ", " Then
  94.         strMediaTypeOut = Right(strMediaTypeOut, Len(strMediaTypeOut) - 2)
  95.     End If
  96.     '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  97.  
  98.     'TaskRequested Multi-value field**********************************************************************
  99.     strField = "TaskRequested"
  100.     strTaskRequested = vbNullString
  101.     strMediaTypeInSQL = "Select " & strField & ".Value AS Val From tblMain Where MainID = " & RS!MainID
  102.     Set MultiRS = DB.OpenRecordset(strMediaTypeInSQL)
  103.     Set valFLD = MultiRS.Fields("Val")
  104.     intCount = DCount(strField, "tblMain", "MainID =" & RS!MainID)
  105.     If intCount > 1 Then
  106.         MultiRS.MoveFirst
  107.         Do Until MultiRS.EOF
  108.             strTaskRequested = strTaskRequested & ", " & valFLD
  109.             MultiRS.MoveNext
  110.         Loop
  111.     Else
  112.         strTaskRequested = Nz(valFLD, vbNullString)
  113.     End If
  114.  
  115.     If Left(strTaskRequested, 2) = ", " Then
  116.         strTaskRequested = Right(strTaskRequested, Len(strTaskRequested) - 2)
  117.     End If
  118.     '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
There are more fields. This is just a sample of how I get the values. This procedure runs forever. There must be an easier way to get the values from an attachment field in a comma separated list into a spreadsheet, which is all I want to do. Any help is much appreciated.
Aug 11 '16 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,619
  1. I created a relatively compact Code Segment that will Output the Values in a Multi-Value Field ([MediaReceivedReason]) to a CSV Format for each Record. The Table Name containing the Multi-Value Field is tblTest.
  2. Code Definition:
    Expand|Select|Wrap|Line Numbers
    1. Dim db As DAO.Database
    2. Dim rst As DAO.Recordset
    3. Dim rstComplex As DAO.Recordset2
    4. Dim strBuild As String
    5.  
    6. Set db = CurrentDb
    7.  
    8. Set rst = db.OpenRecordset("SELECT [MediaReceivedReason] FROM tblTest", dbOpenDynaset)
    9.   Do Until rst.EOF
    10.     'Get the MediaReceivedReason Complex Field
    11.     Set rstComplex = rst![MediaReceivedReason].Value
    12.       'Loop through all MediaReceivedReason Complex Data Type Values
    13.       Do Until rstComplex.EOF
    14.         strBuild = strBuild & rstComplex.Fields(0) & ","
    15.           rstComplex.MoveNext
    16.       Loop
    17.         Debug.Print Left$(strBuild, Len(strBuild) - 1)
    18.         strBuild = ""         'RESET for next Record
    19.           rst.MoveNext
    20.   Loop
    21.  
    22. Me.Requery
    23.  
    24. 'Clean up chores
    25. rst.Close
    26. Set rst = Nothing
    27. Set rstComplex = Nothing
    28. Set db = Nothing
    29.  
  3. Sample OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. B,E,G,K,M,O
    2. O,P,Q,R,S
    3. A,R,S
    4. L,M,N,O
    5. K,P
    6. A,B,C,D,E,F,G,H
Aug 11 '16 #2

ADezii
Expert 5K+
P: 8,619
Sorry BikeToWork, just realized that I posted the Code for a Multi-Value Field and NOT for an Attachment Field. The Code is actually simplier - assume a Table named Table1 with an Attachment Field named [Attach]:
Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next        'For Record(s) with No Attachments
  2. Dim db As DAO.Database
  3. Dim rst As DAO.Recordset
  4. Dim rstAttach As DAO.Recordset2
  5. Dim strBuild As String
  6.  
  7. Set db = CurrentDb
  8.  
  9. Set rst = db.OpenRecordset("SELECT * FROM Table1")
  10.   Do Until rst.EOF
  11.     Set rstAttach = rst![Attach].Value
  12.       With rstAttach
  13.         Do Until .EOF
  14.           strBuild = strBuild & !Filename & ","
  15.             rstAttach.MoveNext
  16.         Loop
  17.           Debug.Print Left$(strBuild, Len(strBuild) - 1)
  18.           strBuild = ""         'RESET for Next Record
  19.       End With
  20.         rst.MoveNext
  21.     Loop
Aug 12 '16 #3

P: 1
Thank you ADezii for this. I don't think it run well
Aug 17 '16 #4

Post your reply

Sign in to post your reply or Sign up for a free account.