If you are experiencing : "The 'Microsoft.Jet.Oldedb.4.0' provider is not registered on the local machine." kind of error due your 64 bit machine, HERE IS THE SOLUTION ... !
please convert your Target Framework from "Any CPU" to "x86" in project properties and make sure the same by right clicking solution and going to "Configuration Manager" OR
Installing 2007 / 2010 Office System Driver: Data Connectivity Components (2007 /2010 )
With the 2nd alternative you may want to change the Provider argument of the ConnectionString property to “Microsoft.ACE.OLEDB.12.0”. (I had to do this)
Resulting the Connection String to look like below:
this not my case. i have a web in asp.net with vb code. my database is in MS access. i want to write my web.config. my database is located at
C:\Access_db\wing.mdb
Just make your app.config file looking something like this:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="AccessConnectionStr" providerName="System.Data.ProviderName" connectionString="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Database\TestDB.mdb;"/>
<add name="Connection" providerName="System.Data.ProviderName" connectionString="here is just some dummy conenction string"/>
</connectionStrings>
<appSettings>
<add key="key1" value="this is the value 1 from app.config"/>
<add key="key2" value="this is the value 2 from app.config, just like that !"/>
</appSettings>
</configuration>
Here is the code that can iterate thru all the connection strings:
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Configuration
Namespace AccessingTheConfigurationsWithConfig
Class Program
Private Shared Sub Main(args As String())
Dim allConnectionStrings As ConnectionStringSettingsCollection = ConfigurationManager.ConnectionStrings
Dim connStr As String
If allConnectionStrings IsNot Nothing Then
For Each conn As ConnectionStringSettings In allConnectionStrings
connStr = conn.ConnectionString
Console.Write(conn.Name + vbTab)
Console.WriteLine(connStr & vbLf)
Next
End If
Console.ReadKey()
End Sub
End Class
End Namespace
Now obviously you do not need spoon-feeding and you've got all you wanted, after all quite sufficiant when somebody is asking for just the connection string, regardless of C# and VB.NET , the connection string remains the same in your case. If something stucks you up, here is the working demo and here is the C# to VB converter !
Herer you go, Below is how the converted vb.net may look like for the code I posted in my previous post:
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
'.NET Database Libraries
Imports System.Data
'MySQL Database Libraries
Imports MySql.Data
Imports MySql.Data.Common
Imports MySql.Data.Types
Imports MySql.Data.MySqlClient
'MS-Access-OLEDB Database Libraries
Imports System.Data.OleDb
Namespace MySQLToMsAccess
Class Program
Private Shared Sub Main(args As String())
'Source Database : MySQL Database
Dim strMySqlConnectionString As String = "SERVER=localhost;" & "DATABASE=dummy;" & "UID=aarsh;" & "PASSWORD=< password >"
Dim MSqlConnDummy As New MySqlConnection(strMySqlConnectionString)
Dim mSqlCmdSelectCustomers As MySqlCommand = MSqlConnDummy.CreateCommand()
mSqlCmdSelectCustomers.CommandText = "select customerNumber, customerName, country from customers limit 10"
Dim mSqlReader_Customers As MySqlDataReader
MSqlConnDummy.Open()
mSqlReader_Customers = mSqlCmdSelectCustomers.ExecuteReader()
Dim dtCustomers As New DataTable()
dtCustomers.Load(mSqlReader_Customers)
MSqlConnDummy.Close()
'Target Database : Ms-Access Database
Dim strAccessConnectionString As String = "Provider=Microsoft.Jet.Oldedb.4.0;" & "Data Source=C:\Database\TestDB.mdb;"
Dim accessConnection As New OleDbConnection(strAccessConnectionString)
accessConnection.Open()
Dim insertCommand As New OleDbCommand()
insertCommand.Connection = accessConnection
insertCommand.CommandText = "INSERT INTO Cust (CustomerID, CustomerName, Country) VALUES (@id, @name, @country)"
For Each row As DataRow In dtCustomers.Rows
Console.WriteLine("{0}" & vbTab & "{1}" & vbTab & vbTab & vbBack & "{2}", row("customerNumber").ToString(), row("customerName").ToString().PadRight(25), row("country").ToString())
insertCommand.Parameters.Clear()
insertCommand.Parameters.AddWithValue("@id", row("customerNumber").ToString())
insertCommand.Parameters.AddWithValue("@name", row("customerName").ToString())
insertCommand.Parameters.AddWithValue("@country", row("country").ToString())
insertCommand.ExecuteNonQuery()
Next
accessConnection.Close()
Console.ReadKey()
End Sub
End Class
End Namespace
this not my case. i have a web in asp.net with vb code. my database is in MS access. i want to write my web.config. my database is located at
C:\Access_db\wing.mdb
Well I posted in general as you did not, specified that you need to store it in web.config ... in your original posting. However my second post also exaplains the VB.net code to get the connection string from web.config
Baiju EP
Member
176 Points
424 Posts
Connection String for MS Access database
Dec 02, 2012 01:03 PM|LINK
i have a web in asp.net and vb code. thre is a existing MS Access database. i want to connect it to asp.net web.
the database is locate in C:\Access_db\wing.mdb
please help me to connect it to web
oned_gk
All-Star
31549 Points
6446 Posts
Re: Connection String for MS Access database
Dec 02, 2012 01:30 PM|LINK
aarsh
Participant
1543 Points
427 Posts
Re: Connection String for MS Access database
Dec 02, 2012 08:55 PM|LINK
Here is the sample code that reads from MySQL and inserts record into MS Access
using System; using System.Collections.Generic; using System.Linq; using System.Text; //.NET Database Libraries using System.Data; //MySQL Database Libraries using MySql.Data; using MySql.Data.Common; using MySql.Data.Types; using MySql.Data.MySqlClient; //MS-Access-OLEDB Database Libraries using System.Data.OleDb; namespace MySQLToMsAccess { class Program { static void Main(string[] args) { //Source Database : MySQL Database string strMySqlConnectionString = @"SERVER=localhost;" + @"DATABASE=dummy;" + @"UID=aarsh;" + @"PASSWORD=< password >"; MySqlConnection MSqlConnDummy = new MySqlConnection(strMySqlConnectionString); MySqlCommand mSqlCmdSelectCustomers = MSqlConnDummy.CreateCommand(); mSqlCmdSelectCustomers.CommandText = @"select customerNumber, customerName, country from customers limit 10"; MySqlDataReader mSqlReader_Customers; MSqlConnDummy.Open(); mSqlReader_Customers = mSqlCmdSelectCustomers.ExecuteReader(); DataTable dtCustomers = new DataTable(); dtCustomers.Load(mSqlReader_Customers); MSqlConnDummy.Close(); //Target Database : Ms-Access Database string strAccessConnectionString = @"Provider=Microsoft.Jet.Oldedb.4.0;" + @"Data Source=C:\Database\TestDB.mdb;"; OleDbConnection accessConnection = new OleDbConnection(strAccessConnectionString); accessConnection.Open(); OleDbCommand insertCommand = new OleDbCommand(); insertCommand.Connection = accessConnection; insertCommand.CommandText = @"INSERT INTO Cust (CustomerID, CustomerName, Country) VALUES (@id, @name, @country)"; foreach (DataRow row in dtCustomers.Rows) { Console.WriteLine("{0}\t{1}\t\t\b{2}", row["customerNumber"].ToString(), row["customerName"].ToString().PadRight(25), row["country"].ToString()); insertCommand.Parameters.Clear(); insertCommand.Parameters.AddWithValue("@id", row["customerNumber"].ToString()); insertCommand.Parameters.AddWithValue("@name", row["customerName"].ToString()); insertCommand.Parameters.AddWithValue("@country", row["country"].ToString()); insertCommand.ExecuteNonQuery(); } accessConnection.Close(); Console.ReadKey(); } } }Hope this helps you...
-------------------------------------------------------------------------------------------------------------------------
DOWNLOAD THIS AS ENTIRE SOLUTION (File Name: MySQLToMsAccess.zip )
string strAccessConnectionString = @"Provider=Microsoft.Jet.Oldedb.4.0;" + @"Data Source=C:\Database\TestDB.mdb;";If you are experiencing : "The 'Microsoft.Jet.Oldedb.4.0' provider is not registered on the local machine." kind of error due your 64 bit machine, HERE IS THE SOLUTION ... !
With the 2nd alternative you may want to change the Provider argument of the ConnectionString property to “Microsoft.ACE.OLEDB.12.0”. (I had to do this)
<div></div>Baiju EP
Member
176 Points
424 Posts
Re: Connection String for MS Access database
Dec 03, 2012 02:06 PM|LINK
this not my case. i have a web in asp.net with vb code. my database is in MS access. i want to write my web.config. my database is located at C:\Access_db\wing.mdb
DaBolander
Member
23 Points
17 Posts
Re: Connection String for MS Access database
Dec 03, 2012 06:06 PM|LINK
Try putting this into your XML in the Web.config file
<connectionStrings>
<add name="MyAccessDatabase"
connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Access_db\wing.mdb;Persist Security Info=True" providerName="System.Data.OleDb" />
</connectionStrings>
Hope that helps
hans_v
All-Star
35986 Points
6550 Posts
Re: Connection String for MS Access database
Dec 03, 2012 08:40 PM|LINK
http://www.mikesdotnetting.com/Article/78/AccessDataSource-SqlDataSource-and-connecting-to-Access-databases-in-ASP.NET
http://www.mikesdotnetting.com/Article/74/Solving-the-Operation-Must-Use-An-Updateable-Query-error
http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access
aarsh
Participant
1543 Points
427 Posts
Re: Connection String for MS Access database
Dec 03, 2012 09:25 PM|LINK
No problem Sir !!!
Just make your app.config file looking something like this:
<?xml version="1.0" encoding="utf-8" ?> <configuration> <connectionStrings> <add name="AccessConnectionStr" providerName="System.Data.ProviderName" connectionString="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Database\TestDB.mdb;"/> <add name="Connection" providerName="System.Data.ProviderName" connectionString="here is just some dummy conenction string"/> </connectionStrings> <appSettings> <add key="key1" value="this is the value 1 from app.config"/> <add key="key2" value="this is the value 2 from app.config, just like that !"/> </appSettings> </configuration>Here is the code that can iterate thru all the connection strings:
Now obviously you do not need spoon-feeding and you've got all you wanted, after all quite sufficiant when somebody is asking for just the connection string, regardless of C# and VB.NET , the connection string remains the same in your case. If something stucks you up, here is the working demo and here is the C# to VB converter !
Herer you go, Below is how the converted vb.net may look like for the code I posted in my previous post:
Imports System.Collections.Generic Imports System.Linq Imports System.Text '.NET Database Libraries Imports System.Data 'MySQL Database Libraries Imports MySql.Data Imports MySql.Data.Common Imports MySql.Data.Types Imports MySql.Data.MySqlClient 'MS-Access-OLEDB Database Libraries Imports System.Data.OleDb Namespace MySQLToMsAccess Class Program Private Shared Sub Main(args As String()) 'Source Database : MySQL Database Dim strMySqlConnectionString As String = "SERVER=localhost;" & "DATABASE=dummy;" & "UID=aarsh;" & "PASSWORD=< password >" Dim MSqlConnDummy As New MySqlConnection(strMySqlConnectionString) Dim mSqlCmdSelectCustomers As MySqlCommand = MSqlConnDummy.CreateCommand() mSqlCmdSelectCustomers.CommandText = "select customerNumber, customerName, country from customers limit 10" Dim mSqlReader_Customers As MySqlDataReader MSqlConnDummy.Open() mSqlReader_Customers = mSqlCmdSelectCustomers.ExecuteReader() Dim dtCustomers As New DataTable() dtCustomers.Load(mSqlReader_Customers) MSqlConnDummy.Close() 'Target Database : Ms-Access Database Dim strAccessConnectionString As String = "Provider=Microsoft.Jet.Oldedb.4.0;" & "Data Source=C:\Database\TestDB.mdb;" Dim accessConnection As New OleDbConnection(strAccessConnectionString) accessConnection.Open() Dim insertCommand As New OleDbCommand() insertCommand.Connection = accessConnection insertCommand.CommandText = "INSERT INTO Cust (CustomerID, CustomerName, Country) VALUES (@id, @name, @country)" For Each row As DataRow In dtCustomers.Rows Console.WriteLine("{0}" & vbTab & "{1}" & vbTab & vbTab & vbBack & "{2}", row("customerNumber").ToString(), row("customerName").ToString().PadRight(25), row("country").ToString()) insertCommand.Parameters.Clear() insertCommand.Parameters.AddWithValue("@id", row("customerNumber").ToString()) insertCommand.Parameters.AddWithValue("@name", row("customerName").ToString()) insertCommand.Parameters.AddWithValue("@country", row("country").ToString()) insertCommand.ExecuteNonQuery() Next accessConnection.Close() Console.ReadKey() End Sub End Class End Namespaceaarsh
Participant
1543 Points
427 Posts
Re: Connection String for MS Access database
Dec 04, 2012 01:43 AM|LINK
manjuby
Participant
1131 Points
251 Posts
Re: Connection String for MS Access database
Dec 04, 2012 03:57 AM|LINK
Hi, Use this resource
http://www.connectionstrings.com/access
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;