DSN vs. Direct Connection

i have a question for all the DB gurus out there. I am trying to connect to a DB on my ISP's server. I recently created a DSN for a program that works fine on my personal web server. To avoid my ISP creating a virtual ditrectory and so on.... someone suggested that I can create a connection directly and skip all the DSN stuff. I need help on the code to do so. Heres what I have:
---------------------------
Dim SqlJunk


Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
dbGlobalWeb.Open("Employees")

SqlJunk = "SELECT * FROM Employees"

--------------------------------

I appreciate all help, thanks!!!!!!!!!!!!!!!

You will need to know the DB server's name and supply the provider:

cn.ConnectionString = "Provider=SQLOLEDB;Server=Servername;Database=DatabaseName;UID=UserId;Pwd=Password;


Fill in the right-side values above with yours (assuming your using SQL Server you'll need to change the provider if not)

Now I have:
-----------------

Dim SqlJunk

Set con = CreateObject("ADODB.Connection")
con.ConnectionString="PROVIDER='MICROSOFT.JET.OLEDB.4.0';Server=servername;Database=employees.mdb;UI D=id;Pwd=password;"
con.Open
--------------------------------

I am getting:

Microsoft JET Database Engine error '80004005'

Could not find installable ISAM.

line 14

-------------------

I think it's when I open it???

any ideas??? Or am I totally off on my code?????
Help anyone!!!!!!!!!!!

If you use Jet OLEDB provider, then you need to assign Data Source to a physical path to the database:

Code:

---

Dim cn

Set cn = Server.CreateObject("ADODB.Connection")
cn.ConnectionString="PROVIDER=MICROSOFT.JET.OLEDB.4.0;Data Source=C:\MyDatabase.mdb"
cn.Open

---

Notice C:\MyDatabase.mdb - it should be a physical path to the database on the server.

Probably a better idea would be to get the server path rather than hard code the path but Serge is right, for an access database you have to specify where it physically is.

Or better yet, create a DSN for an Access database on the server and then use DSN instead.

That would be ideal but it doesn't sound like he has direct access to the server. He is being hosted through an ISP. I doubt if they would be willing to set up a DSN on their server for him so a direct connection will be necessary.

He can create a file DSN that he can upload (since he probably has permissions to upload his files like ASP and HTML) to the server and use it almost the same as User/System DSN.

Code:

---

<%
    Dim cn

    Set cn = Server.CreateObject("ADODB.Connection")
    cn.Open "FileDSN=MyDSN", UserName, Password
%>

---

I just wana make sure you know this, a DSN is just a setting in the ODBC Registry, it allows you to use just a DSN name so that you dont have type out all that same information, but in both cases, you have the exact same information, just with a DSN , it's Pre-Saved in ODBC, and you can use it through out your program.

also if you are using a Access DB, your friend if viewing your ASP on a browser, will NEVER have direct access, but instead whatever you do with the Access will show out to the screen, you may use RDO (Remote Data Object) to truely have direct access through a browser, or you can setup a SQL Server, and have him log into a sql server using his own login tools, but using ADO, the user viewing the page never has direct access because it's a serverside process.

Here is an optimized vbscript of what you want to do, this should help you out...

Code:

---

Set DB = Server.CreateObject("ADODB.Connection")
DB.open "DBQ=" & SERVER.MapPath("your.mdb") & ";DRIVER={Microsoft Access Driver (*.mdb)};"

Set cmdTemp = Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")
cmDTemp.CommandText = "SELECT * FROM YourTable"
cmdTemp.CommandType = 1
Set cmdTemp.ActiveConnection = DB
rs.Open cmdTemp, , 3, 3

---

This will allow you to not have to worry about where the database is located physically on the server, and better yet, if it is moved, your data won't crap out on you...this is also available for SQL7 databases as well...

Code:

---

strconn = "Driver={SQL Server};Description=sqldemo;SERVER=127.0.0.1;UID=LoginID;PWD=Password;DATABASE=Database_Name
set conn = server.createobject("adodb.connection")
conn.open strconn

---

This should make you dangerous enough to continue on with your project...Good luck