Solved

Read Access 2007 Records in Visual Basic Web Developer 2010

Posted on 2011-03-01
6
915 Views
Last Modified: 2012-05-11
Hello Experts,

I am a hobbyist and am new to MS Visual Web Developer 2010.  
After 3 days of searching the web for a solution and tried a bunch of things, I have decided it is time to bring in super(wo)man.  
This is so fundamental that I am almost ashamed to ask for help. Anyway let me start by telling you what I want to do.  
I will then give you the code that I have put together after days of research.

Here are some facts:
MS Visual Web Developer 2010
Access 2007 db (accdb)
Table in Access db = Web_Neighborhoods
The SQL will result in one record.  

There are 6 elements in that record.  I want to use 4 of those 6 elements to concatenate to create a session variable.  That’s it.

The error I am getting is

"ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

when it hits the "cnDb.Open()" line.

This code could be all crap because I cobbled it together after 3 days of research. That is why I told you wanted to do, not just give you code.
Thank you very much for your help.

Cgee


Here is what I have from the VB Web Developer 2010 files


==================================================================================================================
Aspx file
==================================================================================================================

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="test_3.aspx.vb" Inherits="WebApplication1.test_3" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   
    </div>
    <p>
        &nbsp;</p>
    <p>
        <asp:Button ID="Button1" runat="server"
            style="z-index: 1; left: 1166px; top: 98px; position: absolute; width: 73px"
            Text="Button" />
    </p>
    <p>
        &nbsp;</p>
    </form>
</body>
</html>

==================================================================================================================
Aspx.VB file
==================================================================================================================
Public Class test_3
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub





    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        ReadData()

    End Sub





    Public Sub ReadData()

        Dim squery As String
        Dim sconn As String
       
        'This is the connecton string from the MS Visual Web Developer 2010 Properties for the database
        sconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Dropbox\Class2\Web_Dev\Name_Your_Own_Price_s\App_Data\web_solds.mdb;Persist Security Info=True"

        Dim cnDb As New Odbc.OdbcConnection(sConn)


        squery = "SELECT Web_Neighborhoods.Z_N, Web_Neighborhoods.Y_N, Web_Neighborhoods.m1, Web_Neighborhoods.m2, " & _
            "Web_Neighborhoods.m3, Web_Neighborhoods.m4" & _
            "FROM(Web_Neighborhoods)" & _
            "WHERE (((Web_Neighborhoods.Z_N)=19) AND ((Web_Neighborhoods.Y_N)=11));"

        Dim cmd As New Odbc.OdbcCommand(squery, cnDb)
        Dim dr As Odbc.OdbcDataReader


        cnDb.Open()

        dr = cmd.ExecuteReader()

        While dr.Read() = True

            Session("map_to_show") = dr("m1") & dr("m2") & dr("m3") & dr("m4")


        End While

        MsgBox("//" & Session("map_to_show"))


        dr.Close()
        Return

    End Sub


End Class

==================================================================================================================
0
Comment
Question by:williecg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 35014471
The provider you specified (Provider=Microsoft.Jet.OLEDB.4.0) can not read .ACCDB formated files.  You need to use the ACE provider ...

Provider=Microsoft.ACE.OLEDB.12.0

Or ...

Provider=Microsoft.ACE.OLEDB.14.0

A redistributable version of 14.0 can be found here:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&displaylang=en
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 35014477
Or ...

If your database does not require the features of ACE, then convert your datafile to an .MDB and continue to use Microsoft.Jet.OLEDB.4.0
0
 

Author Comment

by:williecg
ID: 35017398
Thank you for you comments.  I changed the provider and it looks like it connected but it says it can't find the input table.

The error happens at cmd.ExecuteReader()

I know the table is there because I can see it in the database explorer window in visual web dev.
 
Here is the connection string and the error message.

sconn = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Dropbox\Class2\Web_Dev\Name_Your_Own_Price_s\App_Data\web_solds.mdb;Persist Security Info=True"

ERROR [42S02] [Microsoft][ODBC Microsoft Access Driver] The Microsoft Office Access database engine cannot find the input table or query 'Web_Neighborhoods'.  Make sure it exists and that its name is spelled correctly.

======================================================

thanks,

Cgee

0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 11

Accepted Solution

by:
datAdrenaline earned 500 total points
ID: 35018238
I missed the fact that you are using ODBC and not OLEDB.  Also, in your connection string, you are using an MDB extension, however in your question you indicate an ACCDB, did you convert your file to an MDB?

I am assuming that your code sets the sconn variable correctly? (ie: you have a preceeding "@" or double up your "\" chars)

Basically the database you are connecting to does not have 'Web_Neighborhoods' otherwise you would not be getting the error.  Your error message would be different if you did not have permission to read the data.  When this ocurrs for me, I often discover that I am pointing to the wrong database, typically one with the same name as my target, but a different path.

I suggest you open the database named web_solds.mdb (in the path you have in the connection string) using access and verify the existance and spelling of the table.
0
 

Author Closing Comment

by:williecg
ID: 35022266
Your questions and comments put together a little test form and in so doing I found I had mixed Oledb and ODBC items.  Since I had been hunting the web and trying various approached, and I did not understand about Oledb and ODBC I ended up with a stuff and did not work together.  You get full credit for helping me find the solution.

Thanks
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 35023659
Glad the information helped out!  Good luck on your project!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

734 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