remote connection to access via ADO

[kleinma]

I am trying to connect via ADO to an access database. The database however is on a web host's webserver. So I am not sure if this is possible.
Able Consulting gave an example doing this via asp pages that return XML data that ADO can read as a recordset. I tried this and it worked running it on my IIS here. but when I uploaded the pages and the DB to the webhost and tried to connect in VB to the database to return a recordset it failed saying the connection was invalid.

Anyone have any idea why this doesn't work on the webserver?

[Shawn N]

I think this might help you.

http://www.roksteady.net/database/remotedata.asp

[kleinma]

Originally posted by Spetnik
Are you just selecting data? Why not just use WinHTTP or the URLDownloadToFile API to download the mdb first?

i would need to open recordsets and update them

[kleinma]

Originally posted by Shawn N
I think this might help you.

http://www.roksteady.net/database/remotedata.asp

I have no control over the webhosts server... so i can't set up anything on their end

[kleinma]

Originally posted by Spetnik
I don't think it is possible then, unless you were to ftp the filese back when you were done.

I just tried doing it on my free brinkster account and it worked. So that means it is a server limitation on the side of my webhost i guess....

[kleinma]

Originally posted by Spetnik
Really? What code did you use to do that?

http://www.able-consulting.com/MDAC/...oUrlConnection

[kleinma]

i figure if i could use this method.. i could build a VB app.. and for it to access the database, i could just pass SQL statements as querystrings to the asp page.. .and have it return the recordset i pass... it would be almost the same as using it local... but in my case it needs to be on the net because our users interface with the DB as well...

I think I will end up having to build an ASP application.. i was hoping to avoid that because I can build a VB application much faster and make it better using controls etc...

[kleinma]

Originally posted by Spetnik
Yeah, that's pretty cool. I don't know what kind of server limitation would prevent that from working. Is there ADO on the server?

yes ado.. but it seems to fail when it tries to write the data back to the response object... so I think maybe they don't have XML installed???

[kleinma]

Originally posted by Spetnik
that could be...

oh well im going to do it local for now... and email the webhost asking them to make it work.. if they cant... at least i know i can move the site to a brinkster payed account and it will work.. its only like 3 bucks more a month

[Shawn N]

You can get SQL server service for an extra 5 bucks on Brinkster, something like that.

[mendhak]

That's an excellent link. I was blissfully unaware of connecting to an Access database on a remote server using this particular method. The whole while I've been giving the following method to just some users, knowing the whole while it wouldn't work.

Perhaps you should try this too, but with your luck....

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
 
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
 
Debug.Print cnn.State
cnn.Open "Provider=MS Remote;Remote Server=http://myServer;" _
            & "Remote Provider=Microsoft.Jet.OLEDB.4.0;" _
            & "Data Source=c:\inetpub\wwwroot\biblio.mdb;admin,"
Debug.Print cnn.State
 
rs.Open "SELECT * From Authors", cnn, adOpenKeyset, adLockOptimistic
 
Debug.Print rs.RecordCount

C:\inetpub\wwwroot\biblio.mdb is the path to the database on the remote server.

But, if you could get them to create a DSN for you, your chances to make this code work might improve. Just replace the file path with the DSN name.

Also, take a look at this link:

this link


IMEO, if you're going to pay Brinksters anyways, go for their SQL Server Option. Access is, put politely, stupid.

[Mike Trader]

Does anyone have an update for these links please:
http://www.able-consulting.com/MDAC/...oUrlConnection
http://www.roksteady.net/database/remotedata.asp

I would like to do allmost the same thing and open a persistent HTTP connection with VB using WinHTTP