I have an Access Database that currently gets user information from our legacy SQL Server database which we have now replaced with Epicor 10. I would like to keep the database the way it is and perhaps use an ODBC connection to get the user data I need from Epicor. I would prefer not to use REST since I am not processing a single query - I am getting data from several different tables. No updates just reading Epicor data.
Is there a way to do this without buying a 3rd party ODBC driver?
Hi Jeanne,
Its been along time sine I’ve used Access but as your Epicor is SQL Access has native SQL drivers for ODBC connections. No 3rd party software required.
Regards
Matt
I am very familiar with ODBC connections. I have been using them for years. However, when I try to add a new connection Epicor does not show up as a database and it says it isn’t a valid database if I type it in.
Are you picking SQL Server native client?
The next step should bring you to a dialog where you can select the server from a drop down box. If your sql server doesn’t show up in that drop down box you may not have sql browser turned on or there may be another reason why the machine you are on can’t “see” the sql server.
REST is great for queries only, especially the BAQ Service.
And I would be remiss to not to mention that REST is more secure than ODBC and doesn’t suffer from all of the other issues surrounding direct database access.
As a former Access developer, I understand the the ease of creating applications in it. We have a ton of Access databases here too. However, I’ve found that the effort of having to have to maintain the client at every workstation a pain - not to mention patching moderate security vulnerabilities. This sounds like a simple web-app. You wouldn’t have to maintain an Access client on every workstation, and you’d be able to get the information available on phones and tablets too.
Just a thought from a recovering Access addict.
I can see the server. I have many connections still out there. The problem is that Epicor doesn’t show in the databases list.
As far as a simple web-app, it isn’t. The access database only uses certain data from the old system but has tons of data in it with a variety of forms and reports. I am not changing the application if I can help it, I just want to get the current list of projects, employees, email addresses, etc out of Epicor instead of the legacy system.
It seems like that isn’t the SQL server instance that contains your Epicor database?
I would use SSMS to locate the database first.
It could be the user you are authenticating with on the SQL server doesn’t have access to your Epicor database?
Permissions were my first thought too.
ODBC has usually been straightforward when I’m logged on locally as admin.
but more opportunities to fail whenever I try connecting over the network, as a non-admin user.
Are you able to log on to the SQL server with admin credentials and or launch/connect with SQL Mgmt Studio?
I usually verify SQL ODBC locally before I try to connect from remote clients.
And is the SQL Server Browser service running on the server?
I have to start the SQL Browser service on my server before I can see the database listed in DSN Mgmt on my remote client PC’s.
First, we only have one SQL Server instance and all our databases including Epicor are housed there, so that is not the issue. Second, I have all of the proper permissions for performing all SQL Server actions as best as I can tell since I can connect all of my other databases. Yes, the SQL Server Browser is running on the server. I can see all of the other databases in the instance but just not Epicor.
