Connecting to an MS Access DB over WEBDAV in excel | Excel forum

Hi Steven,

I'm trying to access a MS Access database which I have saved on a WEBDAV (internet hosted server), by using the same kind of code we used during the training in Aug. Here is an example of me proposed script
__________________________________________
Sub SourceDataProd()

Const CString As String = _
"Provider=MS Remote;Remote Server=https://rob7208.dailydrive.co.uk/dav;Remote Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ConscienceSandpit\ProviderKey\DBTest.mdb;Persist Security Info=False"

Const SQL As String = _
"SELECT URN, Contact, Company, ContactTel FROM TestTable1"

Dim cnConnection As ADODB.Connection
Dim rstRecordset As ADODB.Recordset

Set cnConnection = New ADODB.Connection
cnConnection.ConnectionString = CString

cnConnection.Open

Set rstRecordset = New ADODB.Recordset

Call rstRecordset.Open(SQL, cnConnection)

With rstRecordset

If .BOF And .EOF Then
MsgBox "There are no records"
Exit Sub
End If

.MoveFirst

Call ClearCollection

Do While Not .EOF

Set aDataProd = New SourceDataProd

aDataProd.dbURN = .Fields("URN")
aDataProd.dbContact = .Fields("Contact")
aDataProd.dbCompany = .Fields("Company")
aDataProd.dbTel = .Fields("ContactTel")

Call theDataProd.Add(aDataProd)

.MoveNext

Loop

End With

cnConnection.Close

End Sub
_______________________-

I'm not getting anywhere with this as the report gets stuck at Call rstRecordset.Open(SQL, cnConnection).

However, I'm not if this is related to the problem but when I saved the Access mdb file in the WEBDAV file and then tried to open it from that location I get a message which reads...
"Microsoft Access is unable to load the data access page; The file you attempted to load was not recognised as HTML; You may have selected the wrong file, or tried to open a database file off of a web server"

When I usually access this WEBDAV file I have to provide a username and password, so I'm not sure if this is what is creating the problem...can you help?

CHEERS

ROB