ADO Connections over the Internet

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=http://www.eviciti.com;" _
    & "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 "http://192.168.1.1". "Data Source" is the

DSN you are trying to open on the server. User Id and Password are self

explanatory.

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 www.eviciti.com, no database. This is just a shamless plug… 🙂