Read/Write from web Database 2

[NJLDoc]

I have the following code to connect to an MS Access 2000 database held on an FTP. The connection state = 1 after open yet the recordset cannot be created. Please explain what is wrong?

Dim dbWeb As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim fld As Field

With dbWeb
.Open "Provider=MS Remote;" & _
"Remote Server=ftp://MyFTP.com;" & _
"Remote Provider=MSDASQL;" & _
"DSN=MyFTPDSN;" & _
"Uid=MyFTPUID;" & _
"Pwd=MyFTPPassword"
End With
Set dbWeb = New ADODB.Connection
rs.Open "SELECT * FROM tblOrders", dbWeb

For Each fld In rs.Fields
Debug.Print fld.Name
Next fld
dbWeb.Close

 

[hmckillop]

From my understanding you cant connect to an ftp server and then expect to use ADO. FTP (File Transfer Protocol) is a protocol for transferring files.
What I would suggest is you use a reference to the WinInet.dll to program some code to transfer your database file to your local server. (Alternatively use a 3rd party API to do this).
Then connect to your database locally, and replace the Server details with the local location of the file.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[NJLDoc]

The databse can be accessed using ASP. I would prefer not to use ASP page as a database server and read write through VB application code as I can do, without any issue, if the database is SQL Server resident on the same web host server.

I have seen also references such as the following with the same failed result.

.Open "Provider=MS Remote;" & _
"Remote Server=

http://MyDomain.com;"

& _
"Remote Provider=MSDASQL;" & _
"DSN=MyFTPDSN;" & _
"Uid=MyFTPUID;" & _
"Pwd=MyFTPPassword"
Does this change your position and my problem?

 

[Sheco]

I think you are talking about RDS.... Remote Data Service.

This was part of ADO and maybe it still is... it would allow you to use ADO over HTTP.

Microsoft considers RDS to be obsolete, I'm not even sure if it is included in MDAC 2.8.

 

[NJLDoc]

As I understand things, MDAC 2.8 does still support RDS despite being considered obsolete. If RDS is the only method to read/write to an Access database on a hosted web server, does this mean that there is ultimatley no way to dynamically interchange data with MS Access residing on a hosted web server?

 

[Sheco]

You might be able to do it with .NET remoting...

Perhaps there is a way to do it in the SOAP toolkit?

Another possibility is you could roll your own so to speak... make an ASP page... maybe call it SQL.asp... use the INET control in your VB app to pass your SQL statement to this page. Oh, this page is not intended for use by any real person, just for use by your program.

Anyway, on your SQL.asp page you take the SQL statement from the Request object and execute against an ADO connection on the server. Something like:
Set myRS = myCN.Execute Request.Form("SQLString")

Then you could PERSIST the resulting recordset using the .Save() method and send the persisted recordset back as the contents of your SQL.asp page.

Back in your VB app you receive this persisted recordset file and then, with a local recordset object, call the .Open() method and pass the filename so that it is re-expanded.

It is quite a hack but it would work...

 

[dilettante]

Jet/Access databases are file databases. This means Jet needs file I/O access to the MDB file, and furthermore every instance of Jet accessing the MDB needs access to a common LDB (lock) file.

RDS works by having a proxy back on the web server that connects to Jet where it does have file I/O access to the MDB. It can be a security hole as well, so typical IIS lockdown practices disable the capability.

True client/server databases often support connections using pipes, RPC, or other mechanisms that can operate over various network protocols such as TCP/IP. These can be accessed over the Internet, but it is generally considered a Bad Thing, primarily due to security concerns.

.Net Remoting is little help, because in effect it is an analog of DCOM RPC. Along with myriad other issues, a .Net Remoting client still needs to have a server application to talk to which in turn connects to Jet via ADO, etc. which finally must open the MDB as a file for I/O.


If you are able to write custom web applications that run on the web server you can do almost anything you want between the client and the web application. But an MDB file sitting in a folder on a web or FTP server by itself is a lifeless file, just like an image file or a HTML document. All you can do is download a copy or upload a new file over it.


More typical practice would be to partition your application into two tiers. One for the user interface, the other for the database access and general application logic, then glue the two together over the Internet or intranet by some mechanism like Sheco suggests, which then might well be .Net Remoting or any other technique (including streaming persisted Recordsets over a TCP socket). But... this means you need a Windows server supporting user-written applications of some form. If you are leasing space on some hosting service this may limit you to ASP and some subset of common components they choose to support - many permit ADO and Jet databases though.


Since you aren't relying on a web browser as the client almost anything goes. You can even have your client be a Telnet client from the server's point of view. One that connects, logs on, then runs a console-mode VB program or WSH script at the server to interchange data with. The console program at the server would have to have file I/O access to the MDB file via ADO, DAO, etc. to open it using Jet.

This is the same thing as using ASP at the server - just a bit more funky and less scalable because it is conversational rather than transactional. You don't see a lot of Telnet hosting on Windows servers.

 

[Sheco]

Wow that was an excellent response!

* 4 u

 

[dilettante]

Thanks.

But if anyone comes up with some other ideas I'm sure we'll all be happy to hear about them too. I've learned to never say "it can't be done.

 

[NJLDoc]

Holy cow. I don't like the answer but it is a thorough one which explains a lot. I guess the best bet is to stop using MS Access and focus exclusively on SQL Server. Thats too bad as SQL server adds about $25 month to the hosting cost.

Great answer. I really appreaciate the thought given and time taken to share it.

Thank you both for the advise and help.