How To Connect to MS Access Database in ASP.NET

[mavalon]

MY Code is not working. I'm getting an "Unspecified Error" when trying to connect to an MS Access database:

                Dim connectionString As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\[URL unfurl="true"]wwwroot\myapp\db2.mdb"[/URL]
                Dim con As OleDbConnection
                Dim cmd As OleDbCommand = New OleDbCommand
                Dim da As OleDbDataAdapter

                Dim ds As DataSet = New DataSet
                Dim sSQL As String = "SELECT * FROM Employees" 'EXECUTE Instance_Records"
                cmd.CommandText = sSQL
                con = New OleDbConnection(connectionString)
                con.Open() [COLOR=green]' <<<< THIS IS WHERE IT BOMBS[/color]
                da = New OleDbDataAdapter(cmd)

                da.Fill(ds)
                con.Close()

                dgResults.DataSource = ds
                dgResults.DataBind()

When I run in debug mode and break on con.Open() I see that the ServerVersion value is: <error: an exception of type: {System.InvalidOperationException} occurred>

Thanks in advance for your help.

 

[arznrchrd]

I think it is an order of your operations...the below works for me...

Dim cn As OleDb.OleDbConnection
Dim cmd As OleDb.OleDbCommand
Dim dr As OleDb.OleDbDataReader

Try
cn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\db2.mdb;")
cn.Open()
cmd = New OleDb.OleDbCommand("select * from employee", cn)
dr = cmd.ExecuteReader
While dr.Read
TextBox1.Text = dr(1)
End While

dr.Close()
cn.Close()
Catch ex As Exception
End Try

 

[mavalon]

Actually, I figured out that I needed to give write permission to the internet user account for the "\Documents and Settings\Machine_Name\ASPNET\Local Settings\Temp" directory.

NOW I'VE GOT A NEW ERROR:

Could not use ''; file already in use

Bombs in the same place as before, and when i break there, i see that the connection's database is blank (i.e., ''). The Provider is correct, and the data source is correct, but the database has no value. I can only guess that this is what the above error message is refering to.

Any ideas?

 

[arznrchrd]

Is a permission issue. Allow the network service permission to the database. I found this a ways back doing some online research.

 

[mavalon]

I gave Full Control to Everyone. I still get the same error.

 

[mavalon]

OK, apparently, there must have been some process that kept the connection open. I tried to connect to another database and it worked. After I restarted the computer, I was able to connect to the original database. However, I have one more major issue:

I'm able to connect to and query MS Access tables and execute queries based on normal tables. However, I cannot execute queries that query LINKED tables (linked to SQL Tables). Such queries produce the following error:

ODBC--connection to 'SQL ServerMACHINENAME' failed

The whole reason I am using Access in the first place is to generate Crosstab queries (which cannot be done in SQL) based on the tables in an SQL Database.

Please tell me how I can accomplish this...Pleeeeaase.

 

[arznrchrd]

Never done this, but I found the following which might help...

http://groups.google.com/group/micr...68b53ee8f25/56472cbdf3cb2547#56472cbdf3cb2547

 

[mavalon]

Thanks, but that article seems to be a discussion on Crosstab queries; when in fact my issue is still a connection issue.

Basically, my ASP.NET application connects to MS Access database. That database needs to connect to SQL Server (to get the linked tables). Now, I am able to connect to MS Access db (i.e., my original problem has been resolved...partially), but the queries that need to access Linked tables (from a SQL DB) are failing; hence the error:

ODBC--connection to 'SQL ServerMACHINENAME' failed.

Note, that opening up the Access DB from MS Access itself works when I try to open Linked tables.

 

[mavalon]

OK, The solution for anyone who might have the same problem was the following:

Even though I am linking tables using a User DSN that specifies the SQL user logon, when my ASP.NET page runs an MS Access Query that links to the SQL Database Tables, it runs as ASPNET user.

Therefore, I added that user to the SQL database and gave it the "DataReader" permission.

Now, it works. So, I wasted an entire day of work just to learn that I needed to do two simple things:

1. Give write permission to the internet user account for the "\Documents and Settings\Machine_Name\ASPNET\Local Settings\Temp" directory, and

2. Add the ASPNET user to the SQL database and give it the "DataReader" permission