This article describes how to use the MSRemote provider to
connect to and retrieve data from a remote database. The MSRemote provider
allows the user to access OLE DB providers running on remote computers or in
separate processes on the same computer. By using the MSRemote provider, the
user can get access to live data by going through a Web server. For more
information on Remote Data Service (RDS) and the MSRemote provider, please see
the topic "Remote Data Service in MDAC 2.0" in the Microsoft Developer Network
(MSDN).
When using the MSRemote provider, the connection string used
to open the ADO connection object states that it wants the OLE DB remote
provider to be used. ADO treats this tag in a special manner by using the
RDS.DataSpace component to send an HTTP post request to the specified Web
server. The server where the OLE DB provider is installed is identified by the
tag "Remote Server=". This tag is used to specify the OLE DB provider to be
used to create the recordset at the specified server. If this tag is not
specified, the OLE DB Provider for ODBC (MSDASQL) is used by default. The rest
of the connection string contains all the information needed by the provider to
create the recordset.
Here are some sample connection strings that can be used to
access a remote database through the MSRemote provider:
| • | SQL Server connection string (OLEDB):
Provider=MS Remote;Remote Provider=SQLOLEDB;Data Source=your_SQL_Server;Remote Server=http://your_web_server;Initial Catalog=your_database;User Id=user_id;Password=password;
|
| • | MS Access connection string (Jet OLEDB):
Provider=MS Remote;Remote Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\path_to_your_MDB;Remote Server=http://your_web_server
|
| • | Oracle connection string (OLEDB):
Provider=MS Remote;Remote Provider=MSDAORA;Data Source=your_Oracle_server;Remote Server=http://your_web_server;User Id=user_id;Password=password
|
The following are a Microsoft Visual Basic Script and
JavaScript samples that demonstrate how to use this remote provider. These
samples assume that you have installed the Microsoft Data Access Components and
that they are configured correctly, specifically the RDS components. For more
information on installing and using RDS, please see the "References" section of
this article.
To run the scripts, follow these steps:
| 1. | Open a text editor or create a new HTML page using
Microsoft Visual InterDev. |
| 2. | Cut and paste the appropriate sample code (either VBScript
or JavaScript) provided below into the new file. |
| 3. | The following samples use the Pubs database that ships with
Microsoft SQL Server. You must modify the connection and query strings so that
they are appropriate for your database. |
| 4. | Save the file with an .htm extension. |
| 5. | Double-click the file so that it is loaded in Internet
Explorer. Click on the Command button to submit the query. |
JavaScript Sample
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY>
<INPUT id=button1 name=button1 type=button value="Create Recordset With JavaScript" onClick="go()">
<SCRIPT LANGUAGE=javascript>
<!--
function go ()
{
//---- CursorTypeEnum Values ----
var adOpenForwardOnly = 0;
var adOpenKeyset = 1;
var adOpenDynamic = 2;
var adOpenStatic = 3;
//---- LockTypeEnum Values ----
var adLockReadOnly = 1;
var adLockPessimistic = 2;
var adLockOptimistic = 3;
var adLockBatchOptimistic = 4;
//---- CursorLocationEnum Values ----
var adUseServer = 2;
var adUseClient = 3;
//Variable Declarations
var i;
var REMOTECONNECTOLEDB = "Provider=MS Remote;Remote Provider=SQLOLEDB;Data Source=your_sql_server;Remote Server=http://localhost;Initial Catalog=pubs;User Id=<username>;Password=<strong password>;";
var SQL = "SELECT * FROM Authors";
//Get the records from the database
var rs = new ActiveXObject("ADOR.Recordset");
rs.Open(SQL, REMOTECONNECTOLEDB, adOpenStatic, adLockBatchOptimistic);
window.document.writeln("<BR><B>Query issued:</B> " + SQL + "<BR>");
window.document.writeln("<BR><B>DATA:</B><BR>");
rs.MoveFirst();
while(!rs.EOF)
{
for (i = 0; i < (rs.Fields.Count - 1); i++) {
window.document.write ( rs(i) + " | " );
}
window.document.write ( "<BR>" );
rs.MoveNext();
}
window.document.writeln("<BR><B>Done</B>");
}
//-->
</SCRIPT>
</BODY>
</HTML>
VBScript Sample
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>
</HEAD>
<BODY>
<INPUT id=button2 name=button2 type=button value="Create Recordset With VBScript">
<P> </P>
<SCRIPT LANGUAGE=vbscript>
<!--
Sub Button2_onClick()
'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4
'---- CursorLocationEnum Values ----
Const adUseServer = 2
Const adUseClient = 3
'Variable Declarations
Dim i
Const REMOTECONNECTOLEDB = "Provider=MS Remote;Remote Provider=SQLOLEDB;Data Source=your_sql_server;Remote Server=http://localhost;Initial Catalog=pubs;User Id=<username>;Password=<strong password>;"
Const SQL = "SELECT * FROM AUTHORS"
'Get the records from the database
Set rs = CreateObject("ADOR.Recordset")
rs.Open SQL, REMOTECONNECTOLEDB,adOpenStatic , adLockBatchOptimistic
window.document.writeln("<BR><B>Query issued:</B> " + SQL + "<BR><BR>")
window.document.writeln("<BR><B>DATA:</B><BR>")
rs.MoveFirst
while not rs.EOF
for i = 0 to (rs.Fields.Count - 1)
window.document.write ( rs(i).value & " | " )
next
window.document.write ( "<BR>" )
rs.MoveNext
wend
window.document.writeln("<BR><B>Done</B>")
End Sub
</SCRIPT>
</BODY>
</HTML>
Help and Support
Back to the top
