Home » Applications » Access » Classic ASP Connection String

Classic ASP Connection String

December 6, 2012 - Posted in Access, Applications, Classic ASP, MS SQL

Sql connection
************************

Dim connStr
connStr = "Provider=SQLOLEDB;Data Source=my-sql-server;UID=MyUsernmame;
           PWD=MyPassword; database=MyDatabase;"

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionTimeOut = 30
objConn.ConnectionString = connStr
objConn.Open
SET objRS = Server.CreateObject("ADODB.RecordSet")
objRS.Open "SELECT * FROM my_table ORDER BY type asc",objConn,1,2

'do some sql work

     objRS.Close
SET objRS = Nothing


Access 2007/2010 connection
************************

DIM objConn, objRS, dsn, dbPath
dsn = "Driver={Microsoft Access Driver (*.)};Dbq=C:\MyWebsite\databases
       \MyDatabase2007.accdb;"

Set objConn = Server.CreateObject("ADODB.Connection")
    dbPath = Server.MapPath("MyDatabase2007.accdb")
    objConn.Open "Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath, "", ""
SET objRS = Server.CreateObject("ADODB.RecordSet")
    objRS.Open "SELECT * FROM my_table ORDER BY id asc",objConn,1,2

'do some sql work

     objRS.Close
SET objRS = Nothing


Access 2003 connection
************************

DIM objConn, objRS, dsn, dbPath
dsn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\MyWebsite\databases
       \MyDatabase2003.mdb;"

Set objConn = Server.CreateObject("ADODB.Connection")
    dbPath = Server.MapPath("MyDatabase.mdb")
    objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath, "", ""
SET objRS = Server.CreateObject("ADODB.RecordSet")
    objRS.Open "SELECT * FROM my_table ORDER BY id asc",objConn,1,2

'do some sql work

     objRS.Close
SET objRS = Nothing

Tags: Access, Classic ASP, connection string, SQL