C# migrate from SQL to access database

Question

0.00/5 (No votes)

See more:

Hi

I have a winforms app in c# witch uses an SQL database.
As the sql database needs sqlserver express installed in the client machine to run i decided to change to access.
I created the database and changed the connection strings and datasource accordingly.
But i get an error now:

C#

An unhandled exception of type 'System.TypeInitializationException' occurred in app

details:
keyword not supported "provider"

I dont know as i used sql if i have to change any more code.
Do you know what could be wrong?

Thanks in advance

What I have tried:

my connstring is:

C#

public static SqlConnection con = new SqlConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\db.mdb;");

Posted 25-Nov-16 5:40am

Marc-IT

Updated 25-Nov-16 5:56am

Add a Solution

Comments

PIEBALDconsult 25-Nov-16 11:55am

It's easier if you program to the ADO.net Interfaces rather than specific classes.
I strongly advise against Access, but if you use it, use the ACE engine, not the JET engine.
Have you considered SQL Server CE?

2 solutions

Solution 2

DO NOT migrate backwards. Access has more issues than the Hillary Clinton does with the FBI.

All you have to do is make SqlServer (in one for or another) a requirement of using your software. Your installer can install it for them.

Check this link that compares the different sql server incarnations:

Everything SQL Server Compact: Comparison of SQL Server Compact, SQLite, SQL Server Express and LocalDB[^]

Posted 25-Nov-16 5:56am

#realJSOP

Comments

Marc-IT 25-Nov-16 11:58am

Believe me i tried, but the problem is i have a report witch works with crystal reports and could not figure out how to get them to work together

#realJSOP 25-Nov-16 12:14pm

I would do pretty much anything to avoid using Access... and Crystal Reports. Crystal Reports will connect to Sql Server (dont know if it cares WHICH sql server).

Have you considered MySql? It's query syntax is very similar to Sql Server, and I think it comes in various sizes as well - AND it's free.

kmoorevs 26-Nov-16 12:13pm

For CR, try using field definitions/ttx for your datasource.

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2016-11-25T05:47:00

Solution 1

When you change from SQL Server to Access, you don't just have to change the connection string: you also need to use OleDbConnection instead of SqlConnection, OleDbCommand instead of SqlCommand, and so forth as well.
After that, generally speaking most things will work without changes provided you haven;t used anything too exotic - they are pretty compatible in terms of the actual SQL to pass them.

Posted 25-Nov-16 5:47am

OriginalGriff

Comments

Marc-IT 25-Nov-16 11:54am

Well that makes perfect sense.
How didnt i see that :o
thanks for the help. no i have another related error "The provider 'Microsoft.ACE.OLEDB.12.0' is not registered on the local computer."

OriginalGriff 25-Nov-16 12:05pm

Have you installed the ACE DB engine? And have you installed the right version?
If you app is built for 64bit, then you need the 64 bit ACE engine. And you need to check that you have installed version 12.0 to match your connection string.
But your original connection string is looking for the JET engine, which is only available in 32 bit.

C# migrate from SQL to access database

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

C# migrate from SQL to access database

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us