Retrieve & update data against remote database

[tchya]

I am developing an Access 2000 database application. In my application, I need to connect to a remote Access 2000 database to retrieve and update data. I tried to use RDS (Remote Data Service) as mentioned in MSDN but failed (it raised a runtime error 8447: Internet Server Error).

Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset

cn.Open "Provider=MS Remote; Remote Server=http://myServer; Data Source=sample.mdb"
Set rs = cn.Execute(mySelect)

Do I need any configurations on the server to make the above code work? Or are there any way else? Please help me, thanks.

[olivthill]

Is your error raised when you are trying to establish a connection, or when you are trying to execute your query?

It is rarely easily to find the correct connection string for a database, because there are many possible variants.
For more information about connection strings see http://www.connectionstrings.com/ or http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vccore/html/_core_data_source.3a_.managing_connections_.28.odbc.29.asp

If the problem is with the query, can you post it here, please?

[tchya]

My query is very simple, just like "select * from myTable". And I can execute it against the local database. So I think the problem is about the connection. I used the connection string to connect to the remote database on a different computer (the server) and in a different network as many developer guide said but it failed. Do I need to configure anything on the server?

For example, the database is located at http://vespa:8083/bsg/databases/sample.mdb. Here are the connection strings I tried:

"Provider=MS Remote; Remote Server=http://vespa:8083; Data Source=bsg\databases\sample.mdb"

"Provider=MS Remote; Remote Server=http://vespa:8083/bsg; Data Source=databases/sample.mdb"

"Provider=MS Remote; Remote Server=http://vespa:8083/bsg/databases; Data Source=sample.mdb"

All of the above were failed.