ConnectingAccess to Net for Downloads

[eehines]

I'm having trouble connecting my MS Access 2000 db to the Net so it can download data, and I hope you can help me with it.

The situation is this: I'm generating a db that will track my stock investments, including going onto the Net and downloading the current stock prices (among other things) and adding these data to fields in one of my tables. I'm using Access' VBA, and I've gotten everything to work, so far, except the actual connection to the Net.

The error message I'm getting is:

Run-time error: '-2147467259 (80004005)': Not a valid file name

and I get it when I encounter the part of the code that tries to connect me to a particular location on the Net.

The construction I'm using for this is:

Sub FetchCurrentYahoo(YahooSymbols As String, _
YahooControlStr As String, _
ScratchRange As ADODB.Recordset)

Dim conn As New ADODB.Connection
Dim strPath As String
strPath = "http://quote.yahoo.com/d/quotes.csv?s=" & FetchStr & "&f=" & YahooControlStr & "&e=.csv"

conn.Open "Provider = Microsoft.jet.OLEDB.4.0; Data Source =" & strPath

where the following definitions apply:

Sub FetchCurrentYahoo is the subroutine that actually goes out onto the Net to get the data
FetchStr is the stock symbol for which the data are being requested (correctly parsed, I believe, from a string of stock symbols collected out of one of the tables)
YahooControlStr is a string of characters that tells Yahoo! which particular data I want (e.g., "s" is the stock symbol, l1 is the last price, etc)
&f= simply concatenates the stock symbol and the requested data in the URL in a way that Yahoo! understands
&e=.csv causes the data to be downloaded in a csv format

I know the URL (the strPath variable) itself is valid because it works in the Excel spreadsheet that I'm currently using (perhaps I'm not building it correctly in Access?).

Any help you can provide would be very much appreciated.

E Hines

 

[Fuga]

If you´re ok with having it saved as - say - a .txt file and then format it in word or whatever and then import it to access, this is what I use. Put it in a module.

Option Compare Database
Option Explicit

Public Declare Function URLDownloadToFile Lib "urlmon" Alias _
"URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal _
szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long






Public Function lDownload(sUrl As String, sPath As String) As Long

lDownload = URLDownloadToFile(0, sUrl, sPath, 0, 0)

End Function

just use ldownload ("webadress","pathtoyourfile")

 

[mtairhead]

I just implemented the code that Fuga provided above in a loop, which downloads a very large number of seperate files from the Internet. Does anyone know why the code might halt after a certain number of loops?

After a bit, it just stops. I don't get an error, but I still have to kill the process (CTRL + Pause/Break) to do anything else.

Thanks,

~Andrew