Knowledgebase: ASP .NET / ASP
|
How to connect to a MS Access database from an ASP Script
Posted by Arvand Sabetian (Import) on 25 August 2009 09:14 PM
|
|
|
Please use the following sample code to connect to a MS Access .mdb file: <% E:\path\to\file.mdb is where you've uploaded your mdb file and almost always starts off with E:\HostingSpaces\USERNAME where USERNAME is your username. While the above works for many of our older servers. The following works on our newer servers. You'll need to make sure that your application is running in a Dedicated App Pool (check box under the website inside your control panel) and that MDAC is installed on the server which all of our new servers already have installed (ask our support staff if it doesn't work). <% | |
 (127 vote(s)) Helpful Not helpful |
Comments (14)
Ravish Malhotra
12 May 2011 05:16 AM
I have tested the code on our server and used Classic App Pool and it is working there. I have checked MADC is also installed on your server. When I am working on your server then I am getting this following error “The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.”. I am capturing data from excel sheet with .xls extension. I am using following code.
Private Function ReadExcelOLEDB() As DataTable
Try
Dim MyConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & ~//ExcelFile//testing.xls & "; Extended Properties=Excel 8.0;")
Dim MyCommand As New System.Data.OleDb.OleDbDataAdapter("select * from [" & Sheet1 & "]", MyConnection)
Dim objDataTable As New DataTable
MyConnection.Open()
MyCommand.Fill(objDataTable)
MyConnection.Close()
Return objDataTable
Catch ex As Exception
End Try
End Function
Private Function ReadExcelOLEDB() As DataTable
Try
Dim MyConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & ~//ExcelFile//testing.xls & "; Extended Properties=Excel 8.0;")
Dim MyCommand As New System.Data.OleDb.OleDbDataAdapter("select * from [" & Sheet1 & "]", MyConnection)
Dim objDataTable As New DataTable
MyConnection.Open()
MyCommand.Fill(objDataTable)
MyConnection.Close()
Return objDataTable
Catch ex As Exception
End Try
End Function
Arvand Sabetian
02 June 2011 11:23 PM
It is possible that your app pool is in 64-bit mode and there is no 64-bit version of Jet.OLEDB.4.0 (per http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/45aa44dd-0e6f-42e5-a9d6-9be5c5c8fcd1/). We can look that up and let you know if it is and fix it if necessary.
Doug Leary
13 October 2012 12:43 PM
This article helped me solve my problem. Using the first pattern for older servers, if your site is "yoursite.com" and your access database is "yourDatabase.mdb" in a folder called "yourFolder" under your wwwroot, your string would be:
ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=C:\path\YourUserName\yoursite.com\wwwroot\yourFolder\yourDatabase.mdb;DefaultDir=;UID=;PWD=;"
ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=C:\path\YourUserName\yoursite.com\wwwroot\yourFolder\yourDatabase.mdb;DefaultDir=;UID=;PWD=;"
Michael Carr
15 October 2012 10:27 AM
Thanks for your feedback.
Ramon Smitherman
06 June 2013 07:16 AM
I am confused with the information to replace "path" and "to" in the connection string: Data Source=C:\path\to\file.mdb;User Id=;Password
Michael Carr
07 June 2013 08:29 AM
The Source is going to list the full path to your mdb file. so if your file is named db.mdb and the username on your hosting account is ramon, the path would look something like Source=E:\hostingspaces\ramon\domain.com\wwwroot\db.mdb If you need more help with this, feel free to create a support ticket and we can provide you with the exact path.
Jim Tribbey
12 March 2014 09:41 PM
The following opens an old Access 2.0 database. Much of it is taken from the first example above, but this code makes the "path" specification automatic. Note that the name of my database is "bitethis.mdb", and it resides in the "data" sub-directory of my "wwwroot" directory.
Dim ConnectionString, CurrentDatabase
ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" &_
"DBQ=" & Server.MapPath("data\bitethis.mdb") & ";DefaultDir=;UID=;PWD=;"
Set CurrentDatabase = Server.CreateObject("ADODB.Connection")
CurrentDatabase.ConnectionTimeout = 30
CurrentDatabase.CommandTimeout = 80
CurrentDatabase.Open ConnectionString
Dim ConnectionString, CurrentDatabase
ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" &_
"DBQ=" & Server.MapPath("data\bitethis.mdb") & ";DefaultDir=;UID=;PWD=;"
Set CurrentDatabase = Server.CreateObject("ADODB.Connection")
CurrentDatabase.ConnectionTimeout = 30
CurrentDatabase.CommandTimeout = 80
CurrentDatabase.Open ConnectionString
Michael Carr
19 March 2014 08:57 AM
Thanks for posting that
Jay O'Brien
19 March 2014 02:00 PM
I am a little concerned with your connection string. It is my understanding that Microsoft Access has been filename.ACCDB for at least the last six years. Your ConnectionString addresses *.mdb). All of my files are *.accdb.
Also, my connection string was defined in my web.config file. I went in and changed <add name="ConnectionString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Ranch.accdb;Persist Security Info=True" providerName="System.Data.OleDb" /> to
<add name="ConnectionString" ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Ranch.accdb;" />
Do I need to change the vb code from
Dim RanchConn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|Ranch.accdb")
to
Dim RanchConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Ranch.accdb")
Also, in your code above you have Set Connection = Server.CreateObject("ADODB.Connection") Do I need that instead of the way I have it.
Also, my connection string was defined in my web.config file. I went in and changed <add name="ConnectionString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Ranch.accdb;Persist Security Info=True" providerName="System.Data.OleDb" /> to
<add name="ConnectionString" ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Ranch.accdb;" />
Do I need to change the vb code from
Dim RanchConn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|Ranch.accdb")
to
Dim RanchConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Ranch.accdb")
Also, in your code above you have Set Connection = Server.CreateObject("ADODB.Connection") Do I need that instead of the way I have it.
Michael Carr
20 March 2014 10:50 AM
I would take a look at http://www.connectionstrings.com/access/ I'm sure there are many methods to set it up. Access is used so little currently that I am not sure what the side effect would be of the various methods to set it up.
Gregg Kerber
09 December 2014 11:21 AM
Not getting anything to work. I think it's the "Data Source" path. Here's my connection string:
PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydomain.com.sage.arvixe.com\wwwroot\folder\mydatabase.mdb;User Id=admin; Password=;
The \path\username\ part is not included. I tried \HostingSpaces\username\ but that didn't work.
Any help would be appreciated.
PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydomain.com.sage.arvixe.com\wwwroot\folder\mydatabase.mdb;User Id=admin; Password=;
The \path\username\ part is not included. I tried \HostingSpaces\username\ but that didn't work.
Any help would be appreciated.
Michael Carr
10 December 2014 10:43 AM
Most of our servers now have the hosting data on the E:\ drive, so try E:\HostingSpaces\username\domain\file.mdb
Karen Thompson
28 January 2015 04:06 PM
I am unable to find my IP address in order to set up my SQL. Can you help me?
Ryan C
28 January 2015 10:36 PM
I apologize, but this article is a connection string unrelated to MySQL. mdb is access. For the IP of your hosting service, please refer to the welcome email upon signup called "hosting account information". If you still need further assistance, please do not hesitate to contact us at support [at] arvixe.com
Thank you!
Thank you!