Hey there,
I have an application where I need to query in a few records at a time from an Access table.
We’re running the Epicor application in a VM because it’s being run on a network that’s on a different domain than the Epicor instance. So my only connection to the table is on a share via the IP address on the local network.
Anyone done this? Is there maybe a primer on the subject somewhere?
Thanks,
Joe
I know I’m playing solution skeet shooting here, but maybe whip up a minimal REST API and access the data that way. Works across domains, allows you to move the source physically or into another store like SQL server some day without any code changes on the Epicor side, works with Kinetic.
You can define external data sources and external BAQ’s within ERP to connect to your access DB via ODBC.
I’ve personally never done that (I went out of my way to kill any Access DB’s and pull their functionality into the system), but it should work in theory.
I like this in theory, but since Access doesn’t have any out-of-the-box API, you have to build one. Personally, I think any/all Access applications should be the walking dead. Don’t enhance them beyond the barest minimum effort. The goal should always be to retire them and fold the functionality into a proper process/system. A custom API is more dev care than Access deserves.
IMO. YMMV.
Oh, we agree John. I’m suggesting a method to keep Epicor running while they get the shovel. Once abstracted away, it could be migrated to SQL Server or another enterprise database without changing the Epicor code. 
Is there a danger that it becomes an enabler of out-of-date technology? Sure. But if you don’t have the time now to migrate and need to keep the Kinetic upgrade moving, it’s a step in the right direction.
I actually use EFX to do some of this arbitrary stuff. A scaffolded REST implementation all ready for me to use.
Can you give a bit more detail on how you want to query and where it will be displayed or how it would be used?
Would Epicor the server be able to access the Access DB file over the network at all, even if the client could not?
Maybe if you elaborate on the business process Joe, you may have other options. Who generates these records? How many are "a few at a time?’ What is the nature of these records?
This Access DB is on an industrial PC where it stores results from a tensile test machine. Otherwise we’d sunset the database and bring it into Epicor.
I need to import a few records at a time, usually four, from each job the processed at intervals of from twenty minutes to an hour or so. Speed isn’t the main consideration.
After importing the records, we’ll validate the results against requirments to see if the tensile results were within tolerance.
Joe
Can relate our Ink mixer is the same deal.
Could the Epicor server reach the Access DB file directly if needed?
Where are the records being imported to?
We’re importing the records into a UD table for processing.
The PC where the Access DB is located is on a different domain than the Epicor instance. We’re having to run Epicor from a VM on that PC, or another PC in the same office, because all the PCs are still connected to an AS400, at 32 bits. They’ll be brought into the same domain at some point, but that’s some months away.
We’re talking about connecting the Access DB to a SQL view somehow, so that I can run a query in an external BAQ. That’s all out of my wheelhouse, though. Hop, skip, and a jump, maybe.
Thanks,
Joe
Are the domains in the same forest? Is the DNS in place that you can talk to the other domain from another? That’s all pretty standard setup. It really should be a non issue.
Could write a small app that syncs the Access DB data to a remote SQL server and pick it up from there pretty easily, but you would still fight domain auth if the DNS required isn’t in place.
I think you should rethink the approach.
I’d have a small program on the external system that either on a timer, or trigger, uploads the correct records to epicor, via function.
^^
That hinges on if the one domain can talk to the other but still waiting on a response for if it can, and if it can’t, why not.
Because yeah 100 lines of C# can solve this pretty quick. The direction I was going with this too.
We never got an answer on the nature of data in the MS Access database. Is someone entering data there on a custom form manually? If so, one could enter directly into the UD Table. Is it another commercial program built on MS Access?
Mark,
Yes, this is a commercial program using MS Access to store records.
In our evolving plan, we’re looking at periodically copying the Access DB file to a place where it’s available to the SQL server, and then creating a view where we can query it with an external BAQ.
They should one day wind up on the same domain, but that might take a while.
Thanks,
Joe
If you have the ability to do this and you’re not moving it with a USB stick then you can move them automatically through a standalone app of some description.
A few thoughts.
One is create a linked server in SQL Server to the Access database. Google it, it’s easy. You can set the linked server so that it’s read-only and has no ability to update the Access database. You can then use BAQ or stored proc or whatever to get to the data.
Second, write a PowerShell script to query the Access database periodically and export the records you want in some format. Schedule it. You can ask ChatGPT to literally write you the script and it will do a pretty good job. You can import it on your end using any number of tools. Depending on the size you could just export all the records each time and then merge into your SQL Server.
Lots of ways to accomplish things.