Uploading to web for search, parsing results- automating it all

[hairwormman]

I have a database containing DNA sequences. One sequence per record. A website allows for a comparison of YOUR DNA sequence to all other DNA sequences ever derived. I would like (love!) to click a button in Access, which would take the DNA sequence in that database record, uploaded it to that website-which does a comparison search and returns a rather complex txt file with similarity information, etc,- have Access parse this out, and insert the relevant information into fields within that record. It would be ideal to automate this, so that I click, Access does its thing, and “bing” the info appears.
I know this will be complicated. If anyone could even point me in the right direction where to start gathering information on how to do this, I would greatly appreciated it.

Cheers...Ben

 

[jhall01]

Complex....yes!

I have a few questions:

How does the data get sent to the site manually? ie do you copy into a text box? upload a file? etc...

How is the info outputed back to you? HTML? txt? inputbox?

Do you own the website or have admin access to the website?

Depending on how you get the info to the site determines that segment of your automation. One suggestion is to segment your planning out into modules.

Module1 - outbound to web
Module2 - inbound from web
Module3 - parsing output from Module2
Module4 - outputing or inserting data from Module3

The plausability of each Module depends on the above questions. Hopefully this helps you on planning. I might be able to help further if you answer the above questions.

Thanks,
Jon

PS did you ever get the parameter query working?

 

[hairwormman]

Thanks for your input. Here is some more info:

The information gets to the DNA comparison server via URLAPI; a standardized application program interface (API). It uses direct HTTP-encoded requests to the DNA comparison web server. So, I do not own the website, but I think by using URLAPI it allows broad access. They claim that "Any HTTP browser or application capable to make HTTP requests can be utilized to use URLAPI". I think that Access is capable to make these kind of requests.
The returning file is an html file and looks something like this:

______
Query = P002B10 915 bases, 9090 checksum. (915 letters)

Sequences producing significant alignments: (bits) Value
gb|AAF91388.1| SocE [Myxococcus xanthus] 92 1e-017
emb|CAD59768.1| putative reverse transcriptase [Cicer ariet... 75 2e-012

Score = 92.0 bits (227), Expect = 1e-017
Identities = 51/95 (53%), Positives = 60/95 (62%)
Frame = +3
_____

All I am interested in is parsing out the query ID (P002B10) and uploading the two lines starting with “gb|” and “emb|” and adding them to the db entry for P002B10.

Thanks again!!

P.S. I did get the parameter query working after 30+ trials. I do not know what I did to make it work or what I did to keep it from working. But now it works like a charm!! Thanks!!

 

[jhall01]

As for sending the data. I am not quite sure on this but you might have to get with the URLAPI tech support on what is needed to send data to the system. Since it sounds like a custom software it is quite difficult to figure that aspect.

As for getting the HTML page back...I am not sure exactly how to do that unless it is stored in a specific place or if it is emailed to you etc.

I am starting to wander on what I can actually help you with!!

As for parsing you have several choices:

Regular Expressions
or
String functions in VBA
ie left(), right(), mid(), split(), etc

Regular Expressions tend to be cleaner and faster code

I tend to use the string functions (mostly because i am familiar with them from pre-regEx days) and I haven't taken the time to really learn RegEx in VBA.

I have in the past used the combination of len(), right(), left(), mid() and inStr() to parse out text.

I hope you are familiar with modules in Access cause this is where you will need to start. I would start with the parsing module and sending parsed data to an insert query.

Here is a good link to learn more about parsing using these functions:

http://www.minwar.com/learn/ASP/General/StringParsing.asp

Once you have the data parsed, you can save it to variables in your module and pass those variables to an insert query which will then insert the parse data into your specific table.

Once this is complete you have 50% mechanized. The next steps will be the toughest and I think your best bet is to get with the tech support of the URLAPI system or a programmer with the system to help.

Good Luck!!!

ttfn,
Jon