Generate Access Report in Microsoft Azure

I an absolute newbie in Web based programs in Microsoft Azure.
I now have a Web based program.  It was developed for me in India, and is based on PHP.
The program has just been moved from the test environment in India, to my account in Microsoft Azure in Sydney.  It generates reports (Both PDF files and Excel Files), which all work fine except one.
This is a generated Microsoft Access report.  It comes up with the error report not available.
I was told that you need a virtual machine running in Azure to generate this report, so I managed to get a virtual machine running, in my account in Azure.
But the report still does not work.  I would be grateful for any ideas
PolyviewAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PolyviewAuthor Commented:
Just to clarify:  The report in question is not really an access report.  It is an access database.  There is a blank database stored in the web program.  The web program provisions one of the tables in the access database, and allows the user to download the provisioned database.
PolyviewAuthor Commented:
One further comment:  The error message has now changed to "Could not find driver"
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Glad you added the additional comments, as it changed the answer significantly.

Even with that, I am not sure I understand the setup fully, but from the error message it sounds like your missing the ODBC or OLEDB driver for ACE (Access's default database engine).

To get that driver, you can install this:

Microsoft Access Database Engine 2010 Redistributable
https://www.microsoft.com/en-us/download/details.aspx?id=13255

Note that this comes in a 32 or 64 bit version.  The version of the OS does not matter, but what program is making the call.

 A 32 bit program even though it may be running under a 64 bit OS, will need 32 bit drivers.  Likewise a 64 bit program needs 64 bit drivers.

Jim.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PolyviewAuthor Commented:
Hi Jim,
I Think you are correct, however:
1.  The program designers in India have offered to fix it for me. so I will wait to see how they go,
2.  Your download link, as I understand it, only downloads the driver into my computer.  I think I need it downloaded into Azure.   So I would need more information to be able to do it myself.
Thank you for your input.
I will be back in touch in a week or so regardless of the outcome.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
1.  That's good!

2. You would download that driver onto the machine that is running the web program.   Azure (SQL Server in the cloud) and a JET/ACE database are simply data stores; you need the driver to talk to them.   It's:

   Program ----> driver -----> data store.

 The data store may or may not be on a different server, but the driver must be installed where the program is running.

Jim.
PolyviewAuthor Commented:
Hi Jim,
The program designers in India have tried to fix it, but have run into problems.  They have just sent me the following email:

“For the MS Access file to work we need to have ODBC driver. In our visibility environment (the environment we shared with you), we have the ODBC server in our environment.

But in the virtual machine we created in MS Azure we could not find one  Usually ODBC will be present in Virtual machines . We tried to contact the Azure team but we cannot contact their technical support team using the current “Pay Per Go” subscription model
So please ask Azure support team why ODBC server is not available and how to enable it.”

I have attached a screen shot of my Azure account. I would be grateful if you could give me some information re the following questions:
1.       Why ODBC server is not available and how to enable it?
2.      Do I need the virtual machine, or just the ODBC Driver?
3.      I have no database listed in the Azure account, so the database the website runs on must be in the cloud service or the storage account?
Sorry to be a bother, I really am a newbie, and I am very confused!
Azure-.jpg
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Well let's sort through some issues:

1. Azure is simply SQL Server running in the cloud and is a service on it's own.  There is no "Virtual Machine" attached to it.   It's something you simply access.

2. Examplanner is where the web site (written in PHP) is running correct?   That is the machine that needs the ODBC driver.

 As I mentioned in this comment:

http://www.experts-exchange.com/Networking/Cloud_Computing/Azure/Q_28686075.html#a40814325

 You'll need to install the Microsoft Access Database Engine 2010 Redistributable in that virtual machine and this will give you the OLEDB and ODBC drivers for ACE.

 If you do not have control over that virtual machine yourself, then you will need to contact Microsoft.

 They may however tell you simply that they do not support ACE db's on the virtual machine.

 You would have two choices at that point:

1. Move the data into SQL (Azure) - Not sure why that wasn't done anyway.   Way it is now just complicates things.

2. Use someone else to host the web site that will allow (or has) the drivers installed so you can use your app as is.

Jim.
PolyviewAuthor Commented:
This issue has now been resolved.
We have been in contact with Scott Zhou from Microsoft.   His summary is as follows:

Request definition:
ODBC connection to Office Access file is not able to be created in ODBC manager.

Cause:
,ODBC and OLEDB drivers are installed for application developers to use in developing their applications with connectivity to Office file formats when you install ACE provider. But ODBC provider of ACE is not visible in ODBC manager.

Resolution:
Create your DSN from within Access 2013 instead of using the ODBC Administrator.

Earlier Comment from Scott Zhou
I have done some internal investigation, when you have installed ACE provider, ODBC and OLEDB drivers are installed for application developers to use in developing their applications with connectivity to Office file formats. However, it turns out that for ODBC connections, you are able to work around the issue by creating your DSN (database source name) from within Access 2013 instead of using the ODBC Administrator. In Access 2013 go to External Data > ODBC Database, select Import or Link and click OK,  select the "File Data Source" or "Machine Data Source" tab and then click "New" to create the DSN.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Just to be clear for future readers, I'd like to point out that creating a DSN within Access is not the fix per say for the original problem despite what the last comment implies.

While you certainly can create a DSN connection in Access or even make ODBC connections without a DSN, to use ODBC and establish a connection to a database, a driver must be installed.  Without Access itself being installed on the machine, one way to get those drivers is via the Microsoft Access Database Engine 2010 Redistributable package.

Jim.
PolyviewAuthor Commented:
The program designers were able to talk directly to Microsoft and the issue has been resolved.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Azure

From novice to tech pro — start learning today.