Solved

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

Posted on 2013-01-09
5
618 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Javascript in a user control not working 17 63
ASP.net VB.net Email  a webpage 3 30
Error in query expression 3 30
Adware on IIS hosted asp.net website 1 9
This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

943 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

10 Experts available now in Live!

Get 1:1 Help Now