none
how to use select * from where tblFileImport.bulletin_imp.FileData Like "tracking*"

    General discussion

  • Hi All,

    I don't know how to do this search. I have bellow this query based on 2 tables where the second table has an attachment field. The searching should be based on the content of the attachment, for ex: if the files(that are stored in the attachment field) contain a word like "tracking" then I want to pull all the attachments and their data.

     

    SELECT tblBulletinTrack.orig_corp_b_number, tblBulletinTrack.serv_categ_type, tblBulletinTrack.serv_categ, tblBulletinTrack.author_name, tblBulletinTrack.division, tblBulletinTrack.status, tblFileImport.bulletin_imp.FileData
    FROM tblBulletinTrack INNER JOIN tblFileImport ON tblBulletinTrack.bulletin_id = tblFileImport.bulletin_id
    WHERE (((tblFileImport.bulletin_imp.FileData) Like "tracking*"));

    tblFileImport.bulletin_imp.FileData - this is the File Data of the attachment field

    Please, I really need your help, I was searching on the web for couple of days now and I couldn't find anything. It should be something to retrieve the data from an attachment field.

     Thanks a lot,

    Klaus

    Monday, June 25, 2012 4:41 PM

All replies

  • Hi,

    first of all, think of a situation when you have an image in your attachemnet rather than a text file. Where are you going to search for a word "tracking" in this case? 


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Monday, June 25, 2012 5:11 PM
  • Hi,

    yes, because I'll have only pdf files, so I want to track a specific word in the pdf file, of course these files can contain pictures, but for sure they contain text, and my search will be based on the text in the file.

    Any idea?

    Monday, June 25, 2012 9:41 PM
  • Oh, they are pdf. The answer may make unhappy but I think it is impossible. The only thing that can help a little is to make a tagging system. Just add some valuable tags when you attach a file to a record and then make a search within tags. Otherwise I don't even know the way to parse a pdf file via VBA. It definitely cannot be done via pure SQL and built-in attachment field's properties/subfields.

    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    Monday, June 25, 2012 9:56 PM
  • Well, it is not that easy, I need something else. Because these files I stored them as attachment into my db, but later the user wants to access these file by searching by a specific name, which can be anything. This is my huge dilema, I don't know how I can do a search in this file by whatever text and bring all the info that this file can have it from the db into a form using DAO.

    Maybe I don't need to use DAO for this, but I have no clue, and this thing is killing me for a couple of weeks now.

     

    Any ideas?!

    Tuesday, June 26, 2012 9:20 PM
  • File Server with Metadata?

    pdf's can have keywords in their metadata, can you search for metadata in Access?

    I mentioned File Server because I have heard that used in database conversations. I have an interest in it but don't know much about it. File Net and Bluebird are a couple of companies I've heard of. Seems to me since you can search through a db for files on a computer or server you should be able to search for metadata in attachments somehow.


    Chris Ward

    Tuesday, June 26, 2012 9:39 PM
  • Is there someone who can help me with a code, please ?
    Wednesday, June 27, 2012 1:12 PM
  • Access is not going to search the contents of a PDF file.  You would need to get a 3rd party control that can open a PDF then search the PDF for the words your user is searching for.  Obviously this could take an extremely long time.

    To provide an acceptable response for your users you would need to parse the PDF document (using the 3rd party control that you have downloaded) and build a table of all of the words in the document (excluding common words such as "a", "if", "but", "and", etc).  Finally you must have a table that has a many-to-many relationship linking the words in the document to the ID of the document record.

    I wrote some code to accomplish exactly this with Lotus Notes many years ago and as long as you can a control to parse the PDF document it is too difficult.


    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. "Don't confuse fame with success. Paris Hilton is one; Helen Keller is the other." - with apologies to Erma Bombeck for paraphrasing her words.

    Wednesday, June 27, 2012 1:35 PM
  • I know what are you suggesting, but unfortunatly I can't do that. You see, my pdf can have thousands of key words, it is an engineering file, so, imagine that the user wants to search by any word.

    I need something else.

    Wednesday, June 27, 2012 4:00 PM
  • But that's what databases are for.  While each document may have thousands of key words the total number of words across all of your PDF documents would only be a factor of that.  Google does exactly this - locate key words across billions of documents.  They don't search every document every time someone enters a search.

    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. "Don't confuse fame with success. Paris Hilton is one; Helen Keller is the other." - with apologies to Erma Bombeck for paraphrasing her words.

    Wednesday, June 27, 2012 10:40 PM