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?
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.
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.
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).
Thanks Martin but I do need to initiate the queries from MS Access.
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.
I am looking to execute Access queries against AX data. Is your suggestion in this same area?
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:
There is also another way to this:
You can execute your query in Enterprise Portal with using Proxies to access your AX data.
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?
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.
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.