Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 40
  1. #16
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518

    Colin may know, I was thinking more along the lines of importing the csv data into an Access table that had the appropriate fields plus a "handled" field, be it yes/no, date/time, etc. Then you set that field appropriately as you handle responses. I assume you can limit the csv in some way to only pick up new activity.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  2. #17
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    As my client schools did want to receive relevant messages from parents, I created code to import only new messages i.e. those received at Twilio number after the last import. We had a list of authorised parent numbers. Any not on the list were filtered out.
    Received SMS messages were easy to handle and to assign to relevant students.
    Incoming voice mail was a pain as someone had to listen to each message. We discouraged voice messages!
    If you don't want to manage any incoming messages, just ask for a Twilio number that can only be used to send messages

    You may be interested in the attached user guide that I wrote to accompany the messaging feature.
    This forms a small part of my commercial app: School Data Analyser.
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #18
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    If my high-school would have had an app like yours I would have been "screwed"

    Do you know how to "DELETE" or otherwise "Empty" the Twilio SMS log?

  4. #19
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Me too!

    Not sure I understand why you want to delete old messages from the Twilio log?
    Anyway, as I haven't looked at my code in a long time, you'd be better off asking Twilio direct.
    IIRC they were very helpful and prompt in replying
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #20
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I had the idea that if I could purge the log after processing an app command that it would eliminate possible duplicate app commands. I had already sent a note to Twilio Tech Support, got a reply the same time you posted, and I'm now armed with how to stipulate criteria when I form the MessageURL. With that, I can control what's returned based on date and time plus the originating phone number.

    I can also get CSV directly from Twilio and I ran test code to look at both the CSV and XML. VBA wise, it's a little easier to search the XML as it's easier to recognize the "end" of the body, (</Body>). CSV would be even easier if I could control what data comes into an Access table. As it is, there are almost 20 discrete fields returned in the Twilio message log, only 4 or 5 of which I need in addition to a couple of fields that the app needs. Your code example doesn't reveal the structure of the table you use. Does it inherently select specific fields when you load from the CSV temporary file? I'm not familiar with the statement you use to read the file.

    Thanks,
    Bill

  6. #21
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can link to the csv file and run an append query that selects the appropriate fields from it, and could include criteria to only import desired records. The relevant lines:

    DoCmd.TransferText acLinkDelim, , "CNGTransactions", "C:\AccessAp\CNGTransactions.csv", True
    db.Execute "qryAppendCNG", dbFailOnError

    That links a file received in an email and then imports data from it into a table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #22
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I think I'll stick with the filtered "Get" directly from Twilio. Colin's code will create an CSV file having received the message log from Twilio. With that, I could use your approach to selectively load an Access table. I know my users will balk at the thought of emails rather than text messages. As it is at the moment, "Get"ing a filtered XML log and finding the app commands is really easy so I'll leave it at that for now while I put together the module that runs at specific intervals and see it that experience suggests a change in the log method.

    Thanks,
    Bill

  8. #23
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I wasn't suggesting users getting email rather than text, just showing how you can take the csv file and get the data into Access. In other words:

    'Your code to get csv file of new activity from Twilio
    DoCmd.TransferText acLinkDelim, , "CNGTransactions", "C:\AccessAp\CNGTransactions.csv", True
    db.Execute "qryAppendCNG", dbFailOnError
    'Your code to do whatever with that data, using the table

    Replacing the various table/file/query names/path. Again I would have a "handled" field of some sort in that table, so I could differentiate between new stuff I needed to work with and old stuff I could ignore (but keep to have a record of it).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #24
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I ran this code:

    Code:
    Debug.Print http.responseText
    TWLog = http.responseText
    DoCmd.TransferText acLinkDelim, , "TWLog", "C:\GCC\TWLog.csv", True
    But the resulting csv file was empty. If the DoCmd.TransferText is expecting the first record to be header names then that might be the problem, as Twilio's first record is the filter expression being used.

    "Sid" is the first field of a message, so I suppose I can position to that point as I set the variable "TWLog".

    While I really like the possibility of working with the results of the query, how am I going to sort out the fields properly when the text within the "Body" contains comas?

  10. #25
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No, that was code cut from an import routine that imports...CNG transactions.

    The 3 items that contain "CNG" would be changed to your names.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #26
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    As you can see from the updated post #24 I figured that out all by myself

  12. #27
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not sure what you're asking. The third line will create a linked table of the CSV file. You can run a query against it just like any other table. Here's what that one looks like after the third line runs.
    Attached Thumbnails Attached Thumbnails LinkedCSV.jpg  
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #28
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    The csv file was empty, so the "third" line had nothing to work with. Immediate window clearly showed a valid return from Twilio. But again, the first portion of the "http.responseText" contained the "GET" Criteria NOT the list of field names. I'll step over the filter portion in setting TWLog and see if the DoCmd gets the right stuff.


    MY MISTAKE!!!!! There is/was a debug.print of the criteria string in the code so that obscured the situation. The variable string "TWLog" in fact begins with the field names as one would expect with a CSV file. With that, I don't know why the file resulting from the DoCmd.TransferText is empty.

  14. #29
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I haven't looked at Colin's code, but I assume you're adapting it. I don't understand this line:

    TWLog = http.responseText

    in this context. You've filled a variable presumably with the desired data, but you haven't done anything with it. It would have to be put into a csv file for the remaining lines to be relevant. Or if the response text contains the comma delimited data, I might look at parsing it with the Split() function and importing it with a recordset AddNew. In this case, two Split() functions. An outer one splitting on the new line, and inner one splitting on the comma.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #30
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Oh, and the array/recordset method would be more tedious to set up than a simple append from a linked table, so that would still be my first option.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Refreshing one object, and leave the rest alone!
    By Calego in forum Programming
    Replies: 6
    Last Post: 05-01-2017, 06:52 AM
  2. REST Endpoints
    By foo in forum Programming
    Replies: 1
    Last Post: 12-04-2016, 07:53 PM
  3. REST api POST method
    By irade92 in forum Programming
    Replies: 7
    Last Post: 02-03-2015, 03:21 PM
  4. Help with subforms (and the rest)
    By Franco27 in forum Reports
    Replies: 0
    Last Post: 03-14-2011, 09:43 AM
  5. Combox not filling the rest of the form
    By britt britt in forum Forms
    Replies: 2
    Last Post: 10-27-2009, 04:55 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums