ADO Connections over the Internet

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

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



Comments

  • Great

    Posted by j11_ambriez on 07/16/2009 11:08pm

    Thanks for the help

    Reply
  • I CHALENGE YOU!!!

    Posted by Legacy on 01/29/2004 12:00am

    Originally posted by: Dr. VB

    IF DOES CODE ARE WORKING GIVE ME A SAMPLE PROJECT TO PROVE IT. IF IT WORKS I LL GIVE YOU PROJECTS IN A RIGHT PRICE. ONLY IF YOU ARE INTERESTED.

    Reply
  • Error

    Posted by Legacy on 07/07/2003 12:00am

    Originally posted by: Matthew

    When i run this , it gives me an error that the object/module was not found.
    
    


    Dim rsGeneric As Recordset
    Dim cnnGeneric As Connection
    Dim strquery As String
    strquery = "select * from Notebook_Specs where SeriesNum='1950'"
    '
    Set cnnGeneric = New Connection
    '
    cnnGeneric.Open "Provider=MS Remote;" _
    & "Remote Provider=MSDataShape;" _
    & "Remote Server=http://www.heptagon.ca;" _
    & "Data Source=webdata_heptagon_ca;" _
    & "User Id=;Password=;"
    '
    Set rsGeneric = New Recordset
    '
    With rsGeneric
    .CursorLocation = adUseClient
    .Open strquery, cnnGeneric, adOpenStatic, adLockReadOnly
    .ActiveConnection = Nothing
    End With
    '


    With rsGeneric
    Text1.Text = !Price
    End With

    Reply
  • Online Data Updation

    Posted by Legacy on 01/02/2003 12:00am

    Originally posted by: Biju

    We are planning to develope a vb program for a firm which have a central office and 4 work sites. We have to update the central database throuch internet. The database is Oracle 9i.

    How can we update the database which is located at central office from the worksite through internet?

    Which of the following internet connectivity can be used for the updation:

    64/128 Kbps Leased Line
    64/128 Kbps ISDN
    Ordinary DialUp.

    The amount of data is not too big.

    Reply
  • Same Internet Server Error: no Ms Access Dsn connectivity

    Posted by Legacy on 07/19/2002 12:00am

    Originally posted by: Somdutt

    I am able to connect to ms sql server....but not with ms Access 2000 DSN....i don't know why?..i keeps on giving the error internet sever error...i don't know about it..it's not able to open the records i guess!!!

    Dim rsGeneric As Recordset
    Dim cnnGeneric As Connection
    Set cnnGeneric = New Connection
    '
    cnnGeneric.Open "Provider=MS Remote;" _
    & "Remote Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Remote Server=http://www.alkatravel.com;" _
    & "Data Source=success;" _
    & "User Id=alka;Password=yagnik;"
    '
    Set rsGeneric = New Recordset
    strQuery = "select * from security"
    With rsGeneric
    .CursorLocation = adUseClient
    .Open strQuery, cnnGeneric, , , adCmdText
    .ActiveConnection = Nothing

    Reply
  • Problem

    Posted by Legacy on 07/15/2002 12:00am

    Originally posted by: amit

    U didnt told much about the shape object> as i am also getting the same error "Business object cant be created".
    I also checked the semicolon, but no use.

    Reply
  • Run-time error '8447': Internet Server Error.

    Posted by Legacy on 01/19/2002 12:00am

    Originally posted by: quelhas

    Hi,
    
    I have tried the code you have written. But an error occurred.It displays "Run-time error '8447': Internet Server Error.".Will you pls. help me in getting the connection.

    No DNS.

    The code I wrote is

    Dim Con As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    Con.open = "Provider=MS Remote;" & _
    "Remote Server=http://myServerName;" & _
    "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=c:\path\db.mdb;" & _
    "admin"

    rs.Open strsql, Con 'Error is this line

    'OR THIS............

    Con.open = "Provider=MS Remote;" & _
    "Remote Server=http://myServerName; & _
    "Remote Provider=MSDataShape;" & _
    "DBQ=c:\path\db.mdb;DRIVER={MS Access (*.mdb)};"&_
    "Uid=Login;" & _
    "Pwd=password;"

    rs.Open strsql, Con 'Error is this line


    'OR THIS............

    Con.open = "Provider=MS Remote;" & _
    "Remote Server=http://myServerName; & _
    "Remote Provider=MSDASQL;" & _
    "DBQ=c:\path\db.mdb;DRIVER={MS Access (*.mdb)};"&_
    "Uid=Login;" & _
    "Pwd=password;"

    rs.Open strsql, Con 'Error is this line

    Regards
    Quelhas

    Reply
  • How can i do it on Oracle DB?

    Posted by Legacy on 01/17/2002 12:00am

    Originally posted by: jucy

    Ms sql connect string is that you use,
    but what's the connect string in case of Oracle?

    Reply
  • Error " Business objects cannot be created"

    Posted by Legacy on 08/07/2001 12:00am

    Originally posted by: Jibu Samuel

    Hi,
    
    I have tried the code you have written. But an error occurred.It displays "Business objects cannot be created".Will you pls. help me in getting the connection.

    The code I wrote is

    oconn.Open "Provider=MS Remote;" & _
    "Remote Server=Server name" & _
    "Remote Provider=SQLOLEDB;" & _
    "Data Source=data Source name;" & _
    "Initial Catalog=Database name;" & _
    "User ID=Name;" & _
    "Password=pwd;"
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    strQuery = "select SiteName from ABD_sitemaster"
    With rs
    .CursorLocation = adUseClient
    .Open strQuery, oconn, adOpenStatic, adLockReadOnly
    End With

    Error is in line ".Open strQuery, oconn, adOpenStatic,
    adLockReadOnly"

    Regards
    Jibu

    Reply
  • RDS Handler Error Solution

    Posted by Legacy on 03/24/2000 12:00am

    Originally posted by: Mike Korzeniowski

    Some of you have reported problems implementing this solution. The errors dealt with the default handler denying access to database.

    To solve this problem:

    If you're running this on a standalone machine, you may want to try the following:

    Execute "C:\Program Files\Common Files\System\msadc\Handunsf.reg"
    then reboot your system.

    Note this has to be executed on the machine your're running your web services on.
    To return to previous state:
    Execute "C:\Program Files\Common Files\System\msadc\Handsafe.reg"
    then reboot your system.

    This command modifes the registry to disable the remote data handler security. Not a recomennded solution for a
    network server, but ok for standalone testing.

    To implement security for the remote handler, you must modify the following file:
    c:\windows\Msdfmap.ini (for Win95/98) or c:\winnt\Msdfmap.ini (for NT)

    This file contains permissions to access specific databases via the DSNs.
    For more info see: <http://support.microsoft.com/support/kb/articles/Q230/6/80.ASP>;
    Hope this helps.
    Mike

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • As all sorts of data becomes available for storage, analysis and retrieval - so called 'Big Data' - there are potentially huge benefits, but equally huge challenges...
  • The agile organization needs knowledge to act on, quickly and effectively. Though many organizations are clamouring for "Big Data", not nearly as many know what to do with it...
  • Cloud-based integration solutions can be confusing. Adding to the confusion are the multiple ways IT departments can deliver such integration...

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date