ADO Connections over the Internet

Tuesday Jan 27th 2004 by Mike Korzeniowski

This short code shows how to establish an ADO connection over the Internet. (Added: 1-Mar-2000)

I started investigating this as part of a project for a customer who wanted to collect/upload data from vendors to a central server. On the other side, the end users (distributors) are able to update the product database on their laptops using the same process.

This is actually very simple, once you dig out the right info.

To fully test this out you will need a server with IIS. This server must have a Data Source Name (DSN) pointing to a database, in my case SQL Server, but you could use Access or any other ODBC source. You can also try this out on your own machine, by installing Personal Web Server and creating a DSN to point to your database.

Here is a sample code:

Dim rsGeneric as Recordset 
Dim cnnGeneric as Connection 
set cnnGeneric = new Connection 
cnnGeneric.Open "Provider=MS Remote;" _ 
    & "Remote Provider=MSDataShape;" _ 
    & "Remote Server=;" _ 
    & "Data Source=Products;" _ 
    & "User Id=Distributor;Password=trythis1;" 
set rsGeneric = new Recordset 
With rsGeneric 
    .CursorLocation = adUseClient 
    .Open strQuery, cnnGeneric, adOpenStatic, adLockReadOnly 
    .ActiveConnection = nothing 
End With 

The crux of the connection lies in the connect string (duh!). You must
specify, the provider as "MS Remote" - notice the space in the keyword.
I then must provide the address to the "Remote Server". Note that I
could just as well have said "";. "Data Source" is the
DSN you are trying to open on the server. User Id and Password are self

Notice that I am also using data shaping and disconnecting the read-only recordset from the database, a good thing to do when dealing with remote databases. In order to utilize data shaping you must specify MSDataShape as the "Remote Provider".

Final note: This code will not work as is. There is no DSN named Products at, no database. This is just a shamless plug... :)

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved