Solved

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

Posted on 2013-01-09
5
609 Views
Last Modified: 2013-01-11
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
0
Comment
Question by:jmarintzerksu
  • 3
  • 2
5 Comments
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
ID: 38761129
Have a look here: http://www.microsoft.com/en-us/download/details.aspx?id=13255

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.
0
 

Author Closing Comment

by:jmarintzerksu
ID: 38762871
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?

thanks
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38762995
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: http://www.connectionstrings.com/sql-server-2012#net-framework-data-provider-for-ole-db

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

0
 

Author Comment

by:jmarintzerksu
ID: 38764622
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.

thanks
0
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 38764835
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.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now