capturing web data thru access...

[sparks]

They have a new data collection station. The people here are using
access 97....
This third party site will only post data back to us when we do this.
http://www.datastuff.com/cgi-bin/ocs...=7-5&Select=CC

this is posted back to us as a file called data.
There is no extension to is so the first thing that IE does is ask to
save and where.

I want to be able to open this file in access but the first problem is
no way to auto save.might be because it has no file extension

this is the code I was trying
================================================== =
Dim fileName As String

fileName =
http://www.datastuff.com/cgi-bin/ocs...=7-5&Select=CC

Set tdf = CurrentDb.CreateTableDef(NAME:="AMIONtemp",
connect:="Text;" & fileName)

Call DoCmd.TransferText(TransferType:=acImportDelim,
tableName:="TestTemp", fileName:=fileName)
================================================== ==

I was trying to just capture the data instead of dl the data.

=====================================

so then I tried
==========
Dim logon As String
Dim rpt As Integer
Dim fileURL As String
Dim rstLog As Recordset
Dim rstSchedule As Recordset

logon = "yourname"
rpt = 703
fileURL = "http://www.datastuff.com/cgi-bin/ocs?" & _
"Lo=" & logon & _
"&Rpt=" & rpt & _
"&Month=" & month & "-" & year & _
"&Select=" & clinic

=========
thinking that the ? would have to be parsed into the name...but I am
not even sure if this is a problem or exactly what the problem is.
but even when I go to the link and put in the data it prompts for a
save.
But its not like a normal open/save/cancel its just save/cancel
so does this file exist at the time?

is there a way to have access get the tab delimited data from this
link and import it directly into access table?

if not is there a way to automate the save so there is no prompt,
allow me to pass the save parameters and have it save automatically so
the user sees nothing. and then open the file locally and import is no
problem.
this is my first experience with this type of thing and I have no idea
exactly what I am trying to do.

I just need to get the data into a table without the user doing
anything but picking a month and a year from a pulldown. Has anyone
tried to import text in this type of situation?

thanks for any ideas

Jerry

[sparks]

Ok I got this far and then ouch.

filename isn't a valid path
http://www.datastuff.com//cgi-bin/
how is it getting only the first part and not saying that its a valid
path for the entire string?
http://www.datastuff.com/cgi-bin/ocs...=7-5&Select=CC

is the arguement parameters that are not passed after it tries to
connect.
the string works in ie and I am asked where to save the file

===================================
===================================

On Tue, 16 Aug 2005 14:55:38 GMT, sparks <js******@swbell.net> wrote:

They have a new data collection station. The people here are using
access 97....
This third party site will only post data back to us when we do this.
http://www.datastuff.com/cgi-bin/ocs...=7-5&Select=CC

this is posted back to us as a file called data.
There is no extension to is so the first thing that IE does is ask to
save and where.

I want to be able to open this file in access but the first problem is
no way to auto save.might be because it has no file extension

this is the code I was trying
================================================= ==
Dim fileName As String

fileName =
http://www.datastuff.com/cgi-bin/ocs...=7-5&Select=CC

Set tdf = CurrentDb.CreateTableDef(NAME:="AMIONtemp",
connect:="Text;" & fileName)

Call DoCmd.TransferText(TransferType:=acImportDelim,
tableName:="TestTemp", fileName:=fileName)
================================================= ===

I was trying to just capture the data instead of dl the data.

=====================================

so then I tried
==========
Dim logon As String
Dim rpt As Integer
Dim fileURL As String
Dim rstLog As Recordset
Dim rstSchedule As Recordset

logon = "yourname"
rpt = 703
fileURL = "http://www.datastuff.com/cgi-bin/ocs?" & _
"Lo=" & logon & _
"&Rpt=" & rpt & _
"&Month=" & month & "-" & year & _
"&Select=" & clinic

=========
thinking that the ? would have to be parsed into the name...but I am
not even sure if this is a problem or exactly what the problem is.
but even when I go to the link and put in the data it prompts for a
save.
But its not like a normal open/save/cancel its just save/cancel
so does this file exist at the time?

is there a way to have access get the tab delimited data from this
link and import it directly into access table?

if not is there a way to automate the save so there is no prompt,
allow me to pass the save parameters and have it save automatically so
the user sees nothing. and then open the file locally and import is no
problem.
this is my first experience with this type of thing and I have no idea
exactly what I am trying to do.

I just need to get the data into a table without the user doing
anything but picking a month and a year from a pulldown. Has anyone
tried to import text in this type of situation?

thanks for any ideas

Jerry

[rkc]

sparks wrote:

They have a new data collection station. The people here are using
access 97....
This third party site will only post data back to us when we do this.
http://www.datastuff.com/cgi-bin/ocs...=7-5&Select=CC

this is posted back to us as a file called data.
There is no extension to is so the first thing that IE does is ask to
save and where.

I want to be able to open this file in access but the first problem is
no way to auto save.might be because it has no file extension

this is the code I was trying
================================================== =
Dim fileName As String

fileName =
http://www.datastuff.com/cgi-bin/ocs...=7-5&Select=CC

This is modified from something I use to post data to an .Asp page.
Maybe it will work for you.
Private Function PageContentFromPost _
(webURl as string, PostData as String) as String

On Error GoTo errHandler

Dim msXml As Object
Set msXml = CreateObject("Microsoft.XMLHTTP")
msXml.Open "POST", WebUrl, False 'False=wait for response
msXml.setRequestHeader "Content-Type",
"application/x-www-form-urlencoded"

msXml.send (PostData)

PageContentFromPost = msXml.ResponseText

exitHere:

If not msXML is Nothing Then
Set msXml = Nothing
End If
Exit Function
errHandler:
Err.Raise Err.Number, , _
"Error retrieving data from " & WebUrl & vbCrLf & Err.Description
Resume exitHere
End Function
Sub TestPageContentFromPost
Dim s as string
s = PageContentFromPost ("http://www.datastuff.com/cgi-bin/ocs" _
"Lo=yourname&Rpt=703&Month=7-5&Select=CC")

Debug.Print s
'Or write it to a file
'Open "c:\PostTest.txt" for Output as #1
'Print #1, s
'Close #1
End Sub

[sparks]

Thanks big time for the pointer
I basically did this and it worked fine..
Public Function test2(mon As Integer, yr As Integer)
Dim dater As String
dater = mon & "-" & yr
Debug.Print dater
Dim tempXML As Object
Dim strURL As String
Dim tempstr As String
Set tempXML = CreateObject("Microsoft.XMLHTTP")
strURL =
http://www.datastuff.com/cgi-bin/ocs...=7-5&Select=CC
tempXML.Open "GET", strURL, False, "", ""
tempXML.send
tempstr = tempXML.responseText
Call ReadString(tempstr, "DATATemp")
Debug.Print tempXML.responseText
Set tempXML = Nothing
End Function
again thanks for pointing me in the right direction

Jerry

On Tue, 16 Aug 2005 22:08:28 GMT, rkc
<rk*@rochester.yabba.dabba.do.rr.bomb> wrote:

sparks wrote:

They have a new data collection station. The people here are using
access 97....
This third party site will only post data back to us when we do this.
http://www.datastuff.com/cgi-bin/ocs...=7-5&Select=CC

this is posted back to us as a file called data.
There is no extension to is so the first thing that IE does is ask to
save and where.

I want to be able to open this file in access but the first problem is
no way to auto save.might be because it has no file extension

this is the code I was trying
================================================== =
Dim fileName As String

fileName =
http://www.datastuff.com/cgi-bin/ocs...=7-5&Select=CC

This is modified from something I use to post data to an .Asp page.
Maybe it will work for you.
Private Function PageContentFromPost _
(webURl as string, PostData as String) as String

On Error GoTo errHandler

Dim msXml As Object
Set msXml = CreateObject("Microsoft.XMLHTTP")
msXml.Open "POST", WebUrl, False 'False=wait for response
msXml.setRequestHeader "Content-Type",
"application/x-www-form-urlencoded"

msXml.send (PostData)

PageContentFromPost = msXml.ResponseText

exitHere:

If not msXML is Nothing Then
Set msXml = Nothing
End If
Exit Function
errHandler:
Err.Raise Err.Number, , _
"Error retrieving data from " & WebUrl & vbCrLf & Err.Description
Resume exitHere
End Function
Sub TestPageContentFromPost
Dim s as string
s = PageContentFromPost ("http://www.datastuff.com/cgi-bin/ocs" _
"Lo=yourname&Rpt=703&Month=7-5&Select=CC")

Debug.Print s
'Or write it to a file
'Open "c:\PostTest.txt" for Output as #1
'Print #1, s
'Close #1
End Sub