Need example of web.config ConnectionString for Access mdb

Hello - I'm trying to set up an Object Data Source against an Access (2003) mdb; I've tried:
  <connectionStrings>
    <add name="AppConStr1" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\MyProject\App_Data\MyMDB.mdb;" />
  </connectionStrings>

and got an error message about the "Provider" argument.

Could somebody post an example with the right yik-yak words, please?

Thanks

Mark
mlagrangeAsked:
Who is Participating?
 
Jojo1771Connect With a Mentor Commented:
Are you running 2.0?
 If so I would use admin web site to setup your config stuff. A web based GUI for the web config.

To be honest I only use SQL, so Im not going to be much help. Maybe theres somthing in this doc that can give you some help.

http://www.codeproject.com/cs/database/Connection_Strings.asp
0
 
Jojo1771Commented:
A good site for connection strings. One of my favs.

http://www.connectionstrings.com/
0
 
SammyConnect With a Mentor Commented:
You need to have your DB in App_Data directory

<connectionStrings>
  <add name="MyConnectionString"
    connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
      Data Source=|DataDirectory|MyDB.mdb"
    providerName="System.Data.OleDb" />
</connectionStrings>

to retrieve it
Dim strConnString As String = ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString

HTH
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
mlagrangeAuthor Commented:
Thanks jojo1771, but that's essentially what I had before; I got the same error:
Keyword not supported: 'provider'.

Thanks Sammy1971, but that got a different error message:
Format of the initialization string does not conform to specification starting at index 0.

Both of these error messages point to the line:
Dim cmd As SqlCommand = New SqlCommand(sel, New SqlConnection(GetConnectionString))

I took a wild quess and added a line in the class code for "Imports System.Data.OleDb", but no help.

I'm using code examples that are going against a SQL Server database; obviously, something in these examples is not appropriate for going against an Access database. Here's the whole thing...

In my web.config:

<connectionStrings>
      <remove name="AccessFileName"/>
      <add name="AccessFileName" connectionString="~/App_Data/MyMDB.mdb" providerName="System.Data.OleDb"/>
</connectionStrings>
 

In my Object Data Source class code:

Imports Microsoft.VisualBasic
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb

Public Class clsCensusData

    <DataObjectMethod(DataObjectMethodType.Select)> _
    Public Shared Function TestCensusDataList() As IEnumerable
        Dim sel As String = "SELECT * FROM tblCensusData ORDER BY EmpName"
        Dim cmd As SqlCommand = New SqlCommand(sel, New SqlConnection(GetConnectionString))
        cmd.Connection.Open()
        Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        Return rdr
    End Function

    Private Shared Function GetConnectionString() As String
        Return ConfigurationManager.ConnectionStrings("AccessFileName").ConnectionString
    End Function

End Class

Thanks again for all your responses
0
 
mlagrangeAuthor Commented:
Thanks for all your suggestions. I have since realized:

1) The data retrieval command yik-yak would have to be OleDB yik-yak, not SQL yik-yak, and I can't find any examples of that out there (the things you pointed me to might start out with connnection strings for mdb's, but no related command statements for going against an mdb)  

2) I was trying to postpone the inevitable by using an Access mdb "for prototyping"; I need to swim out of the shallow end of Access and into the deep end of SQL Server

Thanks again for your responses  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.