VBA Macro to Import Access Data from Web Server into Excel

[carefreewebguy]

I need to write a macro to import data from an access database stored on our web server into an excel sheet that will be on a users desktop. I thought I was on the right track with the code below, but I am getting an invalid file name error on the connection open command. Does anyone know what I am missing to access this remote database, or any suggestions for a different approach?

Sub GetSurveyData()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Dim TargetRange
TargetRange = Range("A1")
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=

http://www.ourwebsite.com/datafolder/thedatabase.mdb"

Set rs = New ADODB.Recordset
rs.Open "survey", cn, adOpenStatic, adLockOptimistic, adCmdTable
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next
TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Thanks,

Neil

 

[SkipVought]

Hi,

You might try the MS Query route -- Data/Get External Data...

Once you can get that connection to return results, just turn on your macro recorder and record Editing the query and returning the resultset to Excel, but you really don't need the VBA at all unless you're gonna change the SQL or CONNECT STRING on the fly.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}