Results 1 to 7 of 7
  1. #1
    ULMA is offline Novice
    Windows 7 64bit Access 2000
    Join Date
    Feb 2015
    Posts
    14

    EDI Translator


    Has anyone attempt to build an Access to EDI translator?

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not as such but the principle is simple enough and been around for ages - it is basically creating an exported file to an agreed format and standard. Method of transmission may be via the internet or direct connection, but that is outside the scope of your question. There are a number of different standards - I typically use xml but an ANSI text file is also popular and is probably how EDI differentiates itself.

    for incoming documents, I've developed a number of applications where access 'listens' on a folder for new documents and when detected loads them and then processes them depending on the business rules.

    For an ANSI text file, you would typically use VBA to create it since the normal standards of file creation would not apply.

    Principles are very similar to those used for transferring data across an ETL.

  3. #3
    ULMA is offline Novice
    Windows 7 64bit Access 2000
    Join Date
    Feb 2015
    Posts
    14
    Your answer implies that you have a communication channel that is always open. It would be very interesting to take a look at your VBA coding to understand how it is set up.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Your answer implies that you have a communication channel that is always open.
    No, not really. It's not really EDI in the accepted sense. And in my case it is effectively EDI to Access, not Access to EDI. And the coding is very specific to each application. My point is the translator translates to an agreed format - and if you know the format, it is very easy to create a routine to import or export the data using vba.

    I use windows scheduler to run a small db on a regular basis just to handle imports - how regular depends on the requirement, but perhaps every 30 mins during normal business hours. The small db checks a windows or outlook folder for files. If there are any, it processes them and moves them to an archive folder.

    For windows directories I use the dir function along these lines

    Code:
    dim thisfile as string
    
    thisfile=dir("path...*" & docsuffix.doctype)
    while thisfile<>""
        'dosomething
        thisfile=dir()
    wend
    docmd.quit
    much the same for outlook but using a mail folder - might be the inbox for 'orders@mycompany.com' or 'enquiries@mycompany.com' for example

    the 'dosomething varies from app to app - it might add an order into a process, ping off an acknowledgement email or sms text, run an algorithm to modify production scheduling, save a pdf document somewhere, could be anything.

    What I don't handle is direct machine to machine communication. If I was I guess it would be using a serial port set up with appropriate I/o or via the internet using an IP address, but I've never had to do that so just guessing.

  5. #5
    ULMA is offline Novice
    Windows 7 64bit Access 2000
    Join Date
    Feb 2015
    Posts
    14
    I got it, thanks. There are some relatively inexpensive EDI translator on the market that also allow communication in and out through a number of ports on your computer. I have not tried any of those but for what I have read they seem to be fully packed and ready to go.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    good luck!

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I parse EDI files all the time for health records. I also have procedures to sweep certain file locations for 'new' files but it relies on powershell not vba, you could conceivably have access do this but it would likely require a dedicated pc to handle the timer events in Access.

    If you are simply looking to parse a text file (that's really all EDI files are) that shouldn't be terribly complex.

    If you're looking for a file sweeper I'd look at powershell and just schedule a job to run every 20 minutes or so to do the file check.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dictionary/translator
    By crimson in forum Access
    Replies: 4
    Last Post: 11-26-2010, 05:42 AM
  2. Color Translator
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 08-31-2010, 01:47 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