HTTP connections in VBA for Access
Posted: Tue Nov 18, 2003 3:08 pm
I have what at first seemed to be a straightforward contract, but which increasingly seems difficult or impossible to complete. The client has an existing A2k database which includes a table of websites related to their own. The client wishes to be able to automatically generate a report which shows which links are either inactive, or have changed so that they no longer contain certain keywords. They also want to be able to list those which are still valid, as well.
My planned design was to use a VBA script which would go something like this:
Since I need to get the HTTP error codes, and since I mean to read in the pages without parsing the HTML, I cannot see any feasible way to do this except by connecting to the TCP port directly. However, as far as I can determine, VBA/Access simply doesn't have any facility for that; AFAICT, the closest thing that it supports is opening a web browser from a given hyperlink, without any way of reading the results in Access.
I think it would be trivial to use Perl or Java to handle the connections and store the results back to the database, but since the client wants the reports done in Access, it would mean a two step process which may not be acceptable. I don't see any way to do it in VB 6.0, either, though I have not pursued that line of inquiry too deeply. I am certain it could be done in VB.NET, but neither I nor the client have been using it to date, and we would rather avoid adding any new development tools to those already in use. In any case, using VB would mean using Crystal Reports instead of the Access Report Generator, which may not be acceptable to the client.
I keep thinking I have overlooked something important. I have already taken longer with this assignment than I should have, and the client is understandably displeased. Even a negative answer, if definitive, would allow me to proceed, even if it means discontinuing the contract.
My planned design was to use a VBA script which would go something like this:
Code: Select all
for each record in the table,
extract the URL (currently stored in a memo)
connect to the website's TCP port 80
if server returns an error message (404, 501, etc.)
store the URL and error message in a temporary table
else
scan the page for the keyword
if the keyword is not found
store the URL and missed word in a temp table
generate report from the temp table(s)I think it would be trivial to use Perl or Java to handle the connections and store the results back to the database, but since the client wants the reports done in Access, it would mean a two step process which may not be acceptable. I don't see any way to do it in VB 6.0, either, though I have not pursued that line of inquiry too deeply. I am certain it could be done in VB.NET, but neither I nor the client have been using it to date, and we would rather avoid adding any new development tools to those already in use. In any case, using VB would mean using Crystal Reports instead of the Access Report Generator, which may not be acceptable to the client.
I keep thinking I have overlooked something important. I have already taken longer with this assignment than I should have, and the client is understandably displeased. Even a negative answer, if definitive, would allow me to proceed, even if it means discontinuing the contract.