Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I want to convert the database from SQL Server to Access.

I have a web site with SQL Server, ASP.NET and C#, and I want to convert the database from SQL Server to Access without losing anything on the website.

How can I do that ?


What I have tried:

I tried make connection string with ole db but the code of insert and update is different from SQL server

I just want to change the connection only
and the code remain as it is
Posted
Updated 30-Apr-19 5:41am
Comments
Maciej Los 30-Apr-19 4:33am    
Why? Is there any particular reason to do that?
Zainab Mostafa 30-Apr-19 4:41am    
the customer device possibilities are weak
phil.o 30-Apr-19 5:24am    
If your website has not been architectured with an abstracted data source in the first place, this is not a trivial task. You have to either rewrite existing code to account for the change of DBMS, or you have to rewrite it by abstracting data layer. There is nothing a single connection string can do for this issue.
RedDk 30-Apr-19 13:52pm    
Basically, this is quite easy. But then there's the fact that Access comes in many versions ... which makes getting anything librarilly 64-bit to communicate with something else 32-bit driven a great big no-go.

Anyway, getting SQL Server database that is running under 64-bit can be done through Excel AND Access (32-bit) as long as you target the MSSMLBZ instance. Use the simple SQL tools available on the ribbon and make sure the 32-bit MSSMLBZ (or equivalent I'd imagine) is running and connected (you'll be able to see that in the Excel or Access database explorer).

I'd suggest that is probably a mistake.
This is a website: which always implies multiuser access to the DB is required, sometimes massively multiuser. SQL Server is good at that.

Access is ... not good at that. In fact, the only reason I don't use the word "terrible" is that swear words are available.

Access is a single user database that can be used - in a limited way - for multiuser access; but it will always be a PITA, and will always give you intermittent problems that are vary hard to solve. And performance problems you won't believe once the number of users starts to rise.

Even then, it isn't just a case of changing the connection string, unless you app has been written from scratch with multiuser access to a single file in mind. Some of the syntax is different (SQL Server uses CASE, Access uses IIF for example), Access DB's are smaller than SQL Server, ...

Seriously, I wouldn't even consider an Access DB for a website. SQL Server or MYSql only!
 
Share this answer
 
Comments
Zainab Mostafa 30-Apr-19 5:00am    
this web site is for one user (doctor) only
W Balboos, GHB 30-Apr-19 9:00am    
There's available free version of SQL Server - limited number of connections.

I suppose you could dig up a copy of FoxPro, somewhere - although far better than Access, it hasn't been supported in years.

If his equipment is so old it cannot support any of this - he's long passed the point of saving money by not updating.
Maciej Los 30-Apr-19 5:21am    
5ed!
Quote:
I just want to change the connection only


You can't. If your code is using objects like SqlConnection etc then your site can only work with SQL Server. If you want to use Oledb instead you'll need to change all of your SqlConnection to OleDbConnection and so on. If you are using hard-coded SQL then you'll need to change that too to be compliant with ole db. If you are using Stored Procedures you'll need to convert those to Access Queries and unless your stored procs are incredibly basic, you'll struggle with that too and will maybe have to re-write parts of your code to accommodate.

If you're using Entity Framework then it gets even worse for you.

The short answer is that what you want to do is impossible. How much effort it will involve depends on how you've written the site which we don't know. If you are indeed going to take on the effort of converting this to Access then as suggested you shouldn't change the site from being tightly coupled to SQL Server to being tightly coupled to Access, instead you should write an abstraction layer that allows you to pretty much switch between SQL and Access, or even easily switch to MySql if that becomes a requirement.
 
Share this answer
 
Quote:
I just want to change the connection only
and the code remain as it is

Impossible.

You've written your code against SQL Server using objects that are specific to SQL Server, SqlConnection, SqlCommand, SqlDataAdapter, ... Sqlxxxx, whatever.

You have to change ALL of that code to use Access instead, which uses OleDbConnection, OleDbCommand, OleDbDataAdapter, ..., OleDbxxxx.

Oh, and don't get the idea to just search and replace "Sql" with "OleDb". That's not going to save you any work either. The SQL statements themselves may also have to be rewritten, and parameter types changed.


Now, if you've used Entity Framework to handle all of your database activity, you cannot use Access at all. It's not supported.


Access is not a full implementation of SQL. It's a subset of TSQL, with extensions specific to Access.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900