Below this section of the program can be said to be an universal database connection program that can also connect to all MS databases. <% '----------------------------------------------- ---- Function GETMDBCONNECTION (FILENAME) DIM Provider, DBPath
Provider = "provider = microsoft.jet.Oledb.4.0;" dbpath = "data source =" & server.mappath (filename) set getMDbconnection = getConnection (provider & dbpath) end function
'------------------------------------- - Function GetSecuredMDbconnection (FileName, Password) Dim Provider, DBPath
Provider = "Provider = Microsoft.Jet.OLEDB.4.0;" DBPath = "Data Source =" & Server.MapPath (FileName) Set GetSecuredMdbConnection = GetConnection (Provider & DBPath & "; Jet OLEDB: Database Password =" & Password) End FUNCTION
'------------------------------------- - Function GetDbcconnection (FileName) DIM Driver, SourceType, DBPath
Driver = "Driver = {Microsoft Visual FoxPro Driver};" SourceType = "SourceType = DBC;" DBPath = "SourceDB =" & Server.MapPath (FileName) Set GetDbcConnection = GetConnection (Driver & SourceType & DBPath) End Function
'------------------------------------- - Function GetDbfconnection (Directory) DIM Driver, SourceType, DBPath
Driver = "Driver = {Microsoft Visual FoxPro Driver};" SourceType = "SourceType = DBF;" DBPath = "SourceDB =" & Server.MapPath (Directory) Set GetDbfConnection = GetConnection (Driver & SourceType & DBPath) End Function
'------------------------------------- - Function getExcelConnection (filename) DIM driver, dbpath
Driver = "driver = {Microsoft Excel Driver (* .xls)};" dbpath = "dbq =" & server.mappath (filename) set getExcelConnection = getConnection (Driver & "readonly = 0;" & dbpath) End function'- -------------------------------------------------- Function GetTextConnection (Directory) DIM Driver, DBPath
Driver = "driver = {Microsoft text driver (* .txt; * .csv)};" dbpath = "dbq =" & server.mappath (directory) set getTextConnection = getConnection (driver & dbpath) End Function
'------------------------------------- - Function GetsqlserverConnection (Computer, Userid, Password, DB) DIM Params, Conn
Set GetSQLServerConnection = Nothing Params = "Provider = SQLOLEDB.1" Params = Params & "; Data Source =" & Computer Params = Params & "; User ID =" & UserID Params = Params & "; Password =" & Password Params = Params & "; Initial Catalog =" & db set conn = server.createObject ("adodb.connection") conn.open params set getsqlserverConnection = conn end function
'------------------------------------- - Function getMdbRecordset (filename, source) set getmdbrecordset = getMDBRS (filename, source, 2, ") End Function
'------------------------------------- - Function GetMdbStaticRecordset (Filename, Source) Set getMdbStaticRecordset = GetMDBRS (FileName, Source, 3, ") End Function
'------------------------------------- - Function GetSecuredMdbRecordset (FileName, Source, Password) Set getsecuredMdbRecordset = GetMdbrs (FileName, Source, 2, Password) End function '--------------------- --------------------------- Function GetSecuredMdbStaticRecordset (Filename, Source, Password) Set getsecuredMdbStaticRecordset = GetMDBRS (FileName, Source, 3, Password) END FUNCTION
'------------------------------------- - Function GetDbfrecordSet (Directory, SQL) Set getdbfrecordset = GetoTherrs ("DBF", Directory, SQL, 2) End Function
'------------------------------------- - Function GetDbfStaticRecordset (Directory, SQL) Set getDBfStaticRecordset = GetotherRS ("DBF", Directory, SQL, 3) End Function
'------------------------------------- - Function GetDbcRecordset (FileName, SQL) Set getDbcRecordset = Get Portoerrs ("DBC", FileName, SQL, 2) End Function
'------------------------------------- - Function GetdbcStaticRecordset (FileName, SQL) Set getdbcstaticRecordset = GetoTherrs ("DBC", FileName, SQL, 3) End Function
'------------------------------------- - Function getExcelRecordset (Filename, SQL) set getExcelRecordset = GetoTherrs ("Excel", FileName, SQL, 2) End Function
'------------------------------------- - Function getExcelStaticRecordset (FileName, SQL) set getExcelStaticRecordset = GetoTherrs ("Excel", FileName, SQL, 3) End Function
'------------------------------------- - Function GetTextRecordset (Directory, SQL) Set GetTextRecordset = Get PortTherrs ("Text", Directory, SQL, 2) End Function '----------------------- --------------------------- Function GetTextStaticRecordset (Directory, SQL) Set getTextStaticRecordset = GetoTherrs ("Text", Directory, SQL, 3) END FUNCTION
'------------------------------------- - Function GetsqlserverRecordset (CONN, SOURCE) DIM RS
SET RS = Server.createObject ("AdoDb.Recordset") RS.Open Source, CONN, 2, 2 set getsqlserverRecordset = rs End function
'------------------------------------- - Function GetsqlServersTaticRecordset (CONN, SOURCE) DIM RS
SET RS = Server.createObject ("AdoDb.Recordset") RS.Open Source, Conn, 3, 2 set getsqlserverstaticRecordset = rs End Function
'------------------------------------- - Function GetConnection (Param) DIM Conn
On Error ResMe Next Set GetConnection = Nothing SET CONN = Server.createObject ("AdoDb.Connection") if Err.Number <> 0 THEN EXIT FUNCTION
Conn.open param if err.number <> 0 THEN EXIT FUNCTION SET GETCONNECTION = Conn End Function
'------------------------------------- - Function GETMDBRS (FileName, Source, Cursor, Password) DIM CONN, RS
On Error Resume Next Set GetMdbRs = Nothing If Len (Password) = 0 Then Set conn = GetMdbConnection (FileName) Else Set conn = GetSecuredMdbConnection (FileName, Password) End If If conn Is Nothing Then Exit Function
SET RS = Server.createObject ("AdoDb.Recordset") if Err.Number <> 0 THEN EXIT FUNCTION
Rs.open Source, Conn, Cursor, 2 if Err.Number <> 0 Then EXIT FUNCTION SET GETMDBRS = RS End Function '----------------------- ---------------------------- Function Getotherrs (DataType, Path, SQL, CURSOR) DIM CONN, RS ON Error Resume Next Set Get Porto 2HERRS = Nothing
Select Case DataType Case "Dbf" Set conn = GetDbfConnection (Path) Case "Dbc" Set conn = GetDbcConnection (Path) Case "Excel" Set conn = GetExcelConnection (Path) Case "Text" Set conn = GetTextConnection (Path) End Select If CONN is nothing the exit function
SET RS = Server.createObject ("AdoDb.Recordset") if Err.Number <> 0 THEN EXIT FUNCTION
RS.Open SQL, CONN, CURSOR, 2 IF Err.Number <> 0 THEN EXIT FUNCTION SETETHERRS = rs End Function
'------------------------------------- - Function GetSqlserverrs (Computer, Userid, Password, DB, Source, Cursor) DIM CONN, RS
ON Error ResMe next set getsqlserverrs = nothing set conn = getsqlserverConnection (Computer, Userid, Password, DB) IF conn is nothing kiln
SET RS = Server.createObject ("AdoDb.Recordset") if Err.Number <> 0 THEN EXIT FUNCTION
Rs.open Source, Conn, Cursor, 2 if Err.Number <> 0 THEN EXIT FUNCTION SETSQLSERVERRS = RS End Function%> How to use - copy to copy a file, then use the #include "file name" Call the subroutine inside.