I have created an application using Microsoft Access as the database.
When I distribute it I get an error:
"ACE.OLEDB.12.0" provider is not registered on the local machine"
After searching I found a standard fix installing both
2007 Office System Driver: Data Connectivity Components and Microsoft Access Database Engine 2010 Redistribute
When installing the fix the error changes to unrecognized database. I found another fix that said use Jet 4 as the data connection which when I created a quick application (as a test) it gave the same error.
So does anyone know a way of creating an application that uses a database that is easier to distribute.
Microsoft Access Database Engine 2010 (ACE) comes in two versions. 32bit and 64bit. You cannot install both versions on the same machine. The 32bit version could be installed on PC with 64bit OS. Office 64/32 installs ACE with the same bitness .
This is the scenario that you could find on your customers machine.
Now your application should adapt to this scenario and use a TargetCPU property that allows you to match the majority of your customers (I assume that you can't tell to your user to deinstall its copy of Office to adapt to your app requirements)
If your customer has a 64bit OS and Office 64bit installed then you have only one solution. Create a 64bit app using TargetCPU = AnyCPU without Prefer 32bit or a pure x64 application. Any other option will fail.
Instead if your customer has no Office installed or the 32bit version (regardless of the OS bitness) then you could target its environment building your app with TargetCPU=x86 or AnyCPU with Prefer 32bit option. Of course if there is no Office installed then you need to distribute the 32bit version of ACE.
Other database options to consider are SQLite, MS Sql Server Compact Edition or SQL Server LocalDB option. In any case you need to distribute the appropriate libraries to allow your application use these database systems.
For better compatibility with the code already written you should probably choose one of the Microsoft solutions, but if the application is not big (in term of data access) then you could explore the SQLite path with confidence.