Can I connect to Dynamics AX 2012 from MS Access?

Question Status

AXatak asked a question on 3 Jul 2015 1:10 AM

I have a requirement to use MS Access and to use it to retrieve data from an AX database. Is this feasible and what do I need to do?

André Arnaud de Calavon responded on 3 Jul 2015 1:22 AM

Hi AXatak,

This is possible. You have to create an ODBC datasource connection to the SQL server and AX database. I have not used MS Access for about 6 years, so I cannot provide all details how to do it exactly.

AXatak responded on 3 Jul 2015 1:36 AM

Thanks André. We have a locked down Citrix environment to work in so it looks like I will need to contact the sys admins. I thought that because we already have access to AX then I should be able to set up a DNS to point to the AX SQL server but it isn't that easy.

Martin Dráb responded on 3 Jul 2015 3:00 AM

Accessing AX database directly is discouraged - think about all things that AOS handles for you - security, caching, relations, table inheritance, date-effective tables and so on and so on.

You could reverse the direction and create an ODBC connection from AX to Access.

It also seems that it's possible to call web services from Access (but I've never tried it).

AXatak responded on 5 Jul 2015 8:59 PM

Thanks Martin but I do need to initiate the queries from MS Access.

Ashkan Mehregan responded on 5 Jul 2015 11:07 PM

Dear AXatak,

One way you can do this is using AIF and a simple Windows application for handling both AX and MS Access, but i'm not sure this solution is a best practice or not.

AXatak responded on 6 Jul 2015 1:07 AM

Hi Ashkan,

I am looking to execute Access queries against AX data. Is your suggestion in this same area?

Ashkan Mehregan responded on 6 Jul 2015 5:00 AM

Where do you want to show the results??

If you want to show the results on windows application, i must say 'Yes' it's in the same area.

You should do as following overall steps:

  • Create a document service in AX [with this service, you windows/web application can access to AX data (you can Insert, Update, Delete and run custom queries through this service)].
  • Add reference to your created service in windows/web application.
  • Execute your MS Access queries against through your created web service.
  • finally show the results as you need.

There is also another way to this:

You can execute your query in Enterprise Portal with using Proxies to access your AX data.

AXatak responded on 7 Jul 2015 12:01 AM

Thanks Ashkan.

I am trying to get use of a test VM running AX for testing.

You say "Add reference to your created service in windows/web application" - would you be referring to MS Access or another application that sits between Access and AX?

Ashkan Mehregan responded on 7 Jul 2015 12:21 AM

Dear AXatak,

What i mean by "Add reference to your created service in windows/web application" is when you want to use AX data in outer application, you should create  a AIF service in AX to provide data you need, and then use this service in outer application to access the data in AX.

IECDjones responded on 16 Jan 2017 4:53 PM

Hi AXatak,

I recommend using Pass-through queries to connect to the SQL ODBC data source. 

I would also advise you to stay away from linking tables.

That way you can write efficient queries that only pull the data you need using recommended best practice.