Access database tables from a different server

[shanuragu]

Hi

MsAccess/Sql Server is not installed on my system. Using ASP can I create/access databases from other server?? If so How can I do it.

shara

[lillu]

Here's two way to set up a connection to a remote server:
(Provided you have authorized access to it and know the correct full path)

The types of parameters in the connection statement parameters depend on which driver you're using.

To connect to a database on a different server, use the full path. A full path can either be http://... or ftp:// or file:// or even \\your-machine-name\your-file-name

This one uses a universal driver that will figure out itself what kind of database file you're connection to.

Set Con = Server.CreateObject("ADODB.Connection")

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath("myFile.mdb")

Con.Open strConn

This connection addresses the Access driver directly:

strConn = "driver={Microsoft Access Driver (*.mdb)};dbq=" & server.MapPath("myFile.mdb")

Hope, this helped

[shanuragu]

What about dsn name?? Where can I give it??

shara

[lillu]

The code I posted uses DSN-less connection.

Why do you want to go into soooo much trouble when setting up a dns-less connection is a lot easier and works better, too...?

I explain why.

The problem with DSN is that Users can (and will) modify or delete them by mistake, then your program won't work so well. So it's better to use a DSN-Less or OLE DB Provider connection string - with a Trusted Connection if possible!

Anyway, using an ODBC DSN (Data Source Name) is a two step process.

1) You must first create the DSN via the "ODBC Data Source Administrator" program found in your computer's Control Panel (or Administrative Tools menu in Windows 2000). Make sure to create a SYSTEM DSN (not a USER DSN) when using ASP. You can also create the DSN via Visual Basic code.

2) Then use the following connection string - with your own DSN
name of course.

DSN
oConn.Open "DSN=mySystemDSN;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"

File DSN
oConn.Open "FILEDSN=c:\somepath\mydb.dsn;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"

[shanuragu]

Thanks lillu for all ur help & being patient with me.

It is working fine.

shara

:D