Solved

Read Access 2007 Records in Visual Basic Web Developer 2010

Posted on 2011-03-01
6
911 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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 …
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

744 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

13 Experts available now in Live!

Get 1:1 Help Now