Change my database connection in visual web developer from access mdb to accdb or sql database

Hi Experts:

I am working with my companies job request system, which was built for us and i am attempting to upgrade our system to have a login/registration page and a page that shows everyone's submitted jobs.

My first step is to upgrade our database, it was built using an access.mdb database. Running on jet 4.0. I would like to move the database which was just located in the local folder to the app_data folder and i would also like to either upgrade the database to access 2010 or to an sql database...and sql database would be ideal

here is our code

Dim connectionstr As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("order_db.mdb") & ";"
    Dim objcn As New Data.OleDb.OleDbConnection(connectionstr)
    Dim mySQL As String
    Dim objcm As New Data.OleDb.OleDbCommand(mySQL, objcn)
    Dim objdr As Data.OleDb.OleDbDataReader
    Dim linked As Boolean

if i just change order_db.mdb to order_db.accdb and try to configure the data it gives me the error
Who is Participating?
Robert SchuttConnect With a Mentor Software EngineerCommented:
Have a look here: 

So for a quick solution install the ACE software and change the provider in your connection string to Microsoft.ACE.OLEDB.12.0

Note that the overview section on that page specifically mentions you shouldn't use it like that... and links to SQL Server Express. That should work way more smoothly in the end but does require a bit more work installing and changing your code.
jmarintzerksuAuthor Commented:
Okay this is exactly what i was looking for...however when i make all of these changes i get errors with my coding. Probably why microsoft wants us to use SQL and not access 2010. Any suggestions where to start if i want to upgrade my database to an SQL database, I know how to upgrade it to SQL but with my asp coding is it best to just rewrite all the code or is there a way to update my code to point to the SQL database?

Robert SchuttSoftware EngineerCommented:
Ok, so the first thing to do would be to make a copy of your access database to a fresh SQL (Express) installation. You said no problem there so I'll skip that.

Depending on a number of things, yes it might be possible to start with only a small change to your connection string and keep using most of your existing code at first. Starting point: 

I started typing some more but thought I'd better wait first to see if that helps you on your way already.

EDIT: on my system I was able to connect (and use your code to read data) using:
Dim connectionstr As String = "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=ee;Integrated Security=SSPI;"

Open in new window

jmarintzerksuAuthor Commented:
Wow ya that is great...i don't know what that code all means however it works so that is great.

Now here is my next issue and maybe it is best to put this in a new thread but:

I was able to convert the data from my original mdb data source to an sql data sources that works great however when i create a new page an drag a some, not all but some of the tables from the sql database into my document it only gives me options to enable paging, sorting, and selection there isn't any deleting or editing available. Why is that is that some setting from the origanl mdb document or is that a setting within the sql database.

Robert SchuttSoftware EngineerCommented:
i don't know what that code all means
Well I will explain and maybe that will help understanding why you can only select from the database. (if not, then yes probably you will need to post in the SQL Server topic)

* Provider=SQLOLEDB;

you need another provider for SQL server database but you need one that uses the OleDb protocol to be able to use your current code. This one is perfect for those 2 needs.

* Data Source=.\SQLEXPRESS;

means to connect to the SQLEXPRESS instance on the local server.

* Initial Catalog=ee;

on my system the 'ee' database contains all tables and coding related to my posts on this website. I assume you already changed this to your own database name.

* Integrated Security=SSPI;

This means use the current user (running the website) to log into the database. It could be the source of your problem as the IUSR that is normally running the website process may have readonly access to the database. 3 ways to resolve that:
- give IUSR more rights to your database (not recommended)
- configure another user account to run the website (or application pool) which has more access rights to the database (just using a high permission administrator account is not recommended, a custom account can be more precisely configured and secured)
- replace that last part by "User Id=xxx;Password=yyy;" which is often the easiest solution but also not really recommended because now your password is more or less in plain sight for anybody who can read the files on your webserver.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.