remote connection to access via ADO

[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.