Help on using Ms access and DSN

[joaotsetsemoita]

Hi everybody,

Im trying to access a Ms Access database outside my root website
folder, so Im using ODBC DSN. I have already the DSN create and
working (this same DSN is used by many classic ASP websites I have
running on my remote server). Im also have latest ODBC and JET drivers
installed.

My code is something like this:

Dim objConn As New OdbcConnection
objConn.ConnectionString = "DSN=MyDSN;"

objConn.Open()

Const strSQL As String = "SELECT * FROM customers"
Dim objCmd As New OdbcCommand(strSQL, objConn)

However when I try to run this I always get the error "ERROR [IM002]
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified" on the line that opens the connection ->
objConn.Open()

Any sugestion why is this happening? Or perhaps some turnaround on how
to access a MS Access database that is outside my root webfolder.

Any help is higly appreciated

Thanks in advance

Joao

 

[Mark Rae [MVP]]

Any help is higly appreciated

Use OleDb.

objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=F:\MyDatabase.mdb;User Id=admin;Password=;"

The Jet database doesn't have to be in your site's virtual directory, but it
does have to be in a folder which is "visible" to ASP.NET, and on which the
user under which ASP.NET is running has write access...

 

[joaotsetsemoita]

Hi Mark

actually that was my initialy attempt to achieve my goal, however when
im using oledb I got a diferent error

My code was something like this

Dim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" & Server.MapPath("../../WebDBs/websitex/
myDatabase.mdb"))
objConn.Open()

And I got this error: Cannot use a leading .. to exit above the top
directory.

Then after some research I read someone advising to use a DSN for data
bases outside our website folder.

Probably im getting ythat error because, as you said the folder where
I have the DB is not visible to the ASP.NET.

My folder structure is like this:
DB-> D:\webdbs\website\myDatabase.mdb
Website -> D:\premierweb\websitex\default.aspx

I have a browseable virtual directory to D:\premierweb where I have
all my sites.

So, I want to access myDatase.mdb from default.aspx. How can I make
the db folder visible to my website in a securily way? Any sugestion?

Thanks in advance

Joao

 

[Mark Rae [MVP]]

Dim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" & Server.MapPath("../../WebDBs/websitex/
myDatabase.mdb"))
objConn.Open()

And I got this error: Cannot use a leading .. to exit above the top
directory.

Yes, that would be correct. There are several tricks you could do to resolve
virtual and relative directories to absolute filespecs, but by far the
simplest solution is to specify the full path to your Jet database in the
connection string, as I demonstrated.

Then after some research I read someone advising to use a DSN for data
bases outside our website folder.

Can't imagine why anyone would have said that...

So, I want to access myDatase.mdb from default.aspx. How can I make
the db folder visible to my website in a secure way? Any sugestion?

Dim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=D:\webdbs\website\myDatabase.mdb;"))

You may need to specify other parameters in your connection string...

Finally, the user account that ASP.NET is running under will need to have
write permission on the D:\webdbs\website folder.

 

[joaotsetsemoita]

Just to let you know that problem is solved using the full path
solution.

I also found another way, probably one of the several tricks you said.

System.IO.FileInfo(System.IO.Path.Combine(Server.MapPath("."),
"../../../WebDBs/websitex/mydatabase.mdb")).FullName.

One way or another it's working

Many thanks for your help.

Joao