Solved

Read Access 2007 Records in Visual Basic Web Developer 2010

Posted on 2011-03-01
6
914 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
  • 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

830 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