Need help with ASP, ODBC connection

ASP,ODBC,database

[sanblasena]

I created a database using access on my c drive and went ahead and set up an ODBC connection. Everything seemed to work fine. Then I copied the database over to my website. I want to access the database from an ASP file. Is it already connected or do I have to re-connect it out on my server? I tried setting up an ODBC connection to my file at my site but couldnt get it to work? Any suggestions? I'm on windows XP. Sorry this question is so basic - I'm new at this. PHP, MySql was alot easier! Thanks, Pat

[edited by: DaveAtIFG at 10:10 am (utc) on Jan. 30, 2003]
[edit reason] URL removed. Please avoid specifics whenever possible. [/edit]

[IanTurner]

You will need to set up an ODBC connection on the server to the database file. However if the DB is MSAccess and the webserver is running linux/unix I don't think that this will work.

You need to ensure that the server has an appropriate data access component for you DB type.

[sun818]

The connection string for queries against the AccessDB is set to your local file. Once you upload it to the web site, you will need to add a new connection string for the web enviornment. Comment out the local connect string as the need to work locally will arise eventually. In FrontPage, these files are stored in a hidden folder called _private in the root.

Performance is also better with a DSN-less connection. The merits of DSN and DSN-less are discussed in 4guysfromrolla [4guysfromrolla.com] article. The advantage of DSN-less connection is that no ODBC setup is required.

[sanblasena]

Hi, I decided to try the Dsn-less approach and am using this code - but now my problem is I don't think I am using the right file name. My database is loaded out there under this elmanglar/webshare directory, but does it need to be loaded into a special "webshare" directory? Can I locate it using the url, or do I need to specify the location by drive - and if so, how do I know where it is on the server? Thanks for your help. Pat

<%

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")

objConn.ConnectionString = "DBQ=http://www.elmanglar.com/webshare/dbcounter.mdb;DRIVER={MS Access (*.mdb)}"

objConn.Open
%>

[IanTurner]

If you are writing to the DB you will need to set write permissions on the DB and directory to get the connection to work.

[DaveN]

you can't use http:// in the objConn.ConnectionString = "DBQ=http://www.elmanglar.com/webshare/dbcounter.mdb;DRIVER={MS Access (*.mdb)}"

you need to use the full path c:/webs/data...

or use the server.MapPath

Set MyConn = Server.CreateObject("ADODB.Connection")
conString = "DBQ=" & Server.MapPath("YourDatabase.mdb")
Myconn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & conString

DaveN

[sanblasena]

Well, right now I'm getting a message - Data Source name not found. Is it OK to use a URL name, or do I have to use the name on the drive c:\ or whatever?

Pat

[sun818]

use the name on the drive c:\ or whatever?

You need to contact your web host or look through their FAQ. But the file location should look like a disk path (example - d:\inetpub\wwwroot\database\yourdatabase.mdb) and not a web address. In fact, your database should be placed in a directory that is not web accessible for security purposes. You don't want some random person downloading the database.

[sanblasena]

Thanks, I used the server.mappath approach and it worked fine. Pat

[DaveN]

the Server.MapPath is very useful when you don't NO the Really path.

the Server.MapPath can also be used to find the currect path by
:

<%Response.Write Server.MapPath(Request.ServerVariables("PATH_INFO"))%>

DaveN