Has anyone attempt to build an Access to EDI translator?
Has anyone attempt to build an Access to EDI translator?
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.
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.
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.Your answer implies that you have a communication channel that is always open.
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
much the same for outlook but using a mail folder - might be the inbox for 'orders@mycompany.com' or 'enquiries@mycompany.com' for exampleCode:dim thisfile as string thisfile=dir("path...*" & docsuffix.doctype) while thisfile<>"" 'dosomething thisfile=dir() wend docmd.quit
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.
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.
good luck!
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.