Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Read Access 2007 Records in Visual Basic Web Developer 2010

Posted on 2011-03-01
6
Medium Priority
?
928 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

Accepted Solution

by:
datAdrenaline earned 2000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

580 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