ADO Connections over the Internet

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... :)