0

I have the below Macro reading from a Database table stored as a txt file on the local C drive and returning an SQL query.

Public Function getData(fileName As String) As ADODB.Recordset

Dim cN As ADODB.Connection
Dim RS As ADODB.Recordset
Set cN = New ADODB.Connection
Set RS = New ADODB.Recordset
cN.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Cloud\;Persist 
Security Info=False;Extended Properties=""text; HDR=Yes; FMT=Delimited; 
IMEX=1;""")
RS.ActiveConnection = cN
RS.Source = "select top 10 * from " & fileName
Set getData = RS

End Function

I can then call the function and return data using the below, so far so good...

Sub Cloud()

Dim a As ADODB.Recordset
Set a = getData("file.txt")
a.Open
MsgBox (a.GetString())
a.Close

End Sub

BUT now I would like to move 'file.txt' from C:\Cloud\ to a HTTP location, ie http://it.wont.work/

How would I amend the above for this to work? I've searched and tested but nothing seems to work... I either get internet login failed or ISAM not found.

Many thanks

Aaron Leighfield
  • 1
  • 1
  • 1
    Download the file then open it as you currently are, file paths and URLs are not universally interchangeable. – Alex K. Aug 02 '17 at 15:42
  • I'm using this within an Excel document to run a SQL query and return information. Can anyone propose how I might do this given what I'm trying to achieve? – Aaron Leighfield Aug 02 '17 at 15:46
  • [How do i download a file using VBA](https://stackoverflow.com/questions/17877389/how-do-i-download-a-file-using-vba-without-internet-explorer)? – Alex K. Aug 02 '17 at 15:53
  • Thanks Alex - but the file is 70MB and growing, expecting all users to download is a bit of a tall order :o/ – Aaron Leighfield Aug 02 '17 at 15:56
  • Well even if OLEDB did support HTTP URLs it would *still* have to internally download the file in order to read it. A basic file at a URL is not the same thing as a file on a file system that can be randomly accessed. This sounds like a job for a remote database server. – Alex K. Aug 02 '17 at 16:03
  • If you're working on a company network then consider downloading the file to a central location that other people can access it from. There's no other way to do this (i.e it must be downloaded first). You can set up a scheduled task to update the file on a regular basis. – Tim Williams Aug 02 '17 at 16:14

0 Answers0