Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to get data from website to desktop? (Access 2003)

    Dear Loungers,
    I developed a desktop Access program and now my client wants to have it updated automatically with information entered onto a form on their public website. Can anyone conceive of a way for this to happen? Porting the db to the website is not an option as its in continuous use on the desktop.

    The only architecture I can think of would be that the web site sends an email to the database operator and then Outlook VBA runs to extract the data and append it to the proper table in the db.

    Hopefully, someone has a better idea than this. <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to get data from website to desktop? (Access 2003)

    Hi Gwenda

    Your clients don't want much do they <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    I am unsure of your client set up, how is the website set up? Does it have ASP capability and connectivity to a back end database or will be just the option of creating emails using the mailto: function


    Is there the possibility you could redesign your database to be a back end on the web server and the Database administrator has a front end Access database connected to the back end. At least as data is inputted from the thin end web area the DBA can still link in and read it?

    Some ideas.
    Jerry

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to get data from website to desktop? (Access 2003)

    Hi Jerry - Thanks - I need ideas. <img src=/S/lightbulb.gif border=0 alt=lightbulb width=15 height=15>

    Yes I could split the database into frontend/backend, but are you saying its possible to have the front end on a desktop and the backend hosted at an ISP?

    I have never used the Data Access pages in Access - could this be part of the answer?

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: How to get data from website to desktop? (Acce

    To find an alternative to your original idea of automating the import from emails, you would need to find a place to put the backend so that both the website forms, and your desktop front end can read and write data to the same backend.

    Your ISP could have the baceknd on their server, in which case the web forms could talk to the database, but your desktop front end probably could not.

    If the backend is on your desktop, you can talk to it, but the web forms probably cannot.

    If your business hosted its own website, on its own server, you could probably find a location that would serve both purposes.

    If you find a suitable location for the backend, then you need to implement the web forms. ASP and Data Access Pages are two alternative ways of doing that.

    I have seen a number of threads here where people have coded the automatic importation of data from emails into Access databases, so that solution could probably be made to work.
    Regards
    John



  5. #5
    Lounger
    Join Date
    Dec 2004
    Location
    Fort Collins, Colorado, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to get data from website to desktop? (Access 2003)

    Hi... I'm presently building a VBA module to perform that set of requirements. Essentially, it uses a third party object, instanced in MS Access, to go to the URL, find the data or table, and pull the appropriate fields into the database Cache... just saves a table. Then I have the VBA, clean and parse the data, split data elements on "multi-field" lines of data, and store in a 'final' table. This table uses rigid field names (so you can see I need to idetify elements to be able to post to ADODB, indexing, with datatypes 'cast' as much as possible (I HATE big text fields) ... I'd be glad to provide screen shots if you're interested. Your problem, actually, sounds like a piece of cake compared to the unknown fields I've been dealing with. Regards

  6. #6
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to get data from website to desktop? (Access 2003)

    "a piece of cake"? - I am fascinated. What is this 3rd party object? Did you buy it somewhere?

    I'll be ever grateful for screenshots and any other info you can give me <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22>

  7. #7
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to get data from website to desktop? (Acce

    Thanks John - I'm checking with the client to see where they host their site.

  8. #8
    Lounger
    Join Date
    Dec 2004
    Location
    Fort Collins, Colorado, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to get data from website to desktop? (Access 2003)

    Edited by HansV - <!t>[E]<!/t> is converted to <img src=/S/schwa.gif border=0 alt=schwa width=7 height=9> by the Lounge, and <!t>[i]<!/t> makes text Italic. I'm not sure I could reconstruct the original code correctly.

    Hi... I don't want to look like a advertising 'plug, nor say too much until we complete the entire record formatting for the final table ... here's a snippet of the VBA that takes the 'Cache' data (the web data captured and stored in raw form) and parses the rows and moves into the final table. You can see, the filters are basic, but you have to be creative with the Access functions to 'look' and 'control' the strings as they are transferred. It's a lot cheaper and more accurate and reliable, than paying for raw page scraping. Regards <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

    Dim szWebDate As String
    Dim szCourtName As String
    Dim szCaseTitle As String
    Dim szFileDate As String
    Dim szStyle As String
    Dim szAction As String
    Dim szPhoneNumber As String
    Dim szFaxNumber As String
    Dim szPlaintiff_Attorney As String
    Dim szDefendant_Attorney As String

    lngRecNo = 0
    rst_Source.MoveFirst
    Rem Preliminary sequence removes the [A-Z]"-"... character
    Rem and sorts 'row type" by the contents of each row. Multiple,
    Rem and, maybe, extraneous variables are used liberally for now.
    Do Until (rst_Source.EOF)
    szFldData = CStr(rst_Source.Fields(2).Value)
    If (szFldData Like "*[A-Z][A-Z][A-Z]??#, 200#*") Then
    varResult = cmdEnhancedSplit(szFldData)
    Rem return is a well defined variant array
    szWebDate = CStr(varResult(0) & " " & varResult(1) & "," & " " & varResult(2))
    szCourtName = CStr(varResult(3) & " " & varResult(4))
    End If

    If (szFldData Like "*[v][s][.]*") Then
    szCaseTitle = Trim(szFldData)
    End If

    If (szFldData Like "*[D][A]<!t>[E][F]<!t>[I][L][:]*") Then
    Rem This needs a variable number of array elements
    varResult = cmdEnhancedSplit(szFldData)
    szFileDate = CStr(varResult(2))
    szStyle = CStr(varResult(4) & " " & varResult(5) & " " & varResult(6) & " " & varResult(7) & " " & varResult(8))
    End If

  9. #9
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to get data from website to desktop? (Access 2003)

    hawksway ... yes, but *how* did you get the data off the website so you could parse it? Can you tell me about the 3rd party object that does this?

  10. #10
    Lounger
    Join Date
    Dec 2004
    Location
    Fort Collins, Colorado, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to get data from website to desktop? (Access 2003)

    Hi.. not to be obscure on a public forum, but this uses an ActiveX third party tool to access the URL and capture the page data; text, images, and table/grids. The 'processing' code is managed in MS Access (or SQL, Paradox, any COM+ compatible application, how about TurboCad [img]/forums/images/smilies/smile.gif[/img] and the data stored in same. Short of a private e-mail, I can't go into detail other than answering your question "yes, there are fairly simple techniques to capture your web data, format it, and place it in a database repository" using the described method. Kind Regards

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: How to get data from website to desktop? (Access 2003)

    I'm also interested in this, my email address is in my profile.

    I would appreciate it if you would share this with me.

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to get data from website to desktop? (Acce

    I'm also very interested in this idea.

    Could you also be kind enough to semd me an email with some details - the address is in my profile

    Thaks

    Nick

  13. #13
    Lounger
    Join Date
    Dec 2004
    Location
    Fort Collins, Colorado, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to get data from website to desktop? (Access 2003)

    Ummm... there are several ways to access web data, first and foremost is a .NET solution with all the peripheral issues of connection control, security, client registry, and installation .... not to mention, 'parsing'. Now you have a HUNK of issues, if it's a simple client requirement, whereas I read Gwenda's original request and a very broad "can it be done?" "Is it cost effective??" "how diffficult??". All, good questions, any coder would expect to be answered on a public forum.

    My solution, while not divulging code specifics nor casting a commercial flavor on the dialogue, was presented (our current project), to indicate, with some assurance of success, that using a third party ActiveX tool permits the coder to avoid the bulk of the issues mentioned above.... no .NET... packaged and managed in MS Access VBA! The are a multiplicity of controls out there to do this, we've tested several and selected the most flexible and accurate, in these terms..
    1) Possess a standalone run-time module for easy distribution and NO registry issues beyond mere MS Access and/or ActiveX installation. One CAN purchase full-blown applications, aside, but compatible with MS Access VBA, if desired; we're not a Retail Store.
    2) ActiveX or *.DLL implementation sufficient for rapid, accurate, and supportable Web Operations using BroadBand or Dial-up (manual, we're not offering a network commander with this simple package <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> ) .
    3)VBA, VB, VBScript, and, yes, ",NET" compatible for enhanced and/or extended network capability. Most of our clients ask for "supportable" code, ie, beginners or part-timers can understand and maintain the ultimate solution.
    4)Understandable, and easily parsed results... each of you are well aware of the differences in Web Sites, ergo, we need to be able to easily tailor the URL interface to adjust to changing addresses & conditions. We are not writing a "one fits all" solution; the clients have specific sites and data retrieval requirements. It may be just a table! But, it demands at least to site-specific capture routine; VBA or VB
    5)Manufacturer requests: My private E-mail is posted in my profile, send me a note; please avoid the "can I do this questions'... I'll merely refer you to the component Retail Stores and we all know how to find them. Perhaps, this is not understood... we observe this forum's rules, in particular, avoiding "commercial" references or "plugs" ,,, questions of full code example, component selection, "try-to-buy", testing, component features, and other, readily available design issues/solutions are "commercial" factors we, unfortunately, do not post . The component manufacturer's may not be aware of our specific uses and/or solutions, ergo, on a free forum, we are remiss IF providing vendor name or contact information <img src=/S/confused.gif border=0 alt=confused width=15 height=20> .. and you all, should, know, bottom line, we're motivated by economics.
    Geez, this ought to be posted in the general section .. it's more forum culture and advice protocol, than, Web Page capture....

    Hope this helps. Wayne

  14. #14
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to get data from website to desktop? (Access 2003)

    Gwenda,
    Is the data kept in an Access database on the web site and can the website handle .asp?
    If so, I can help you. Some of my databases do this daily. When they are opened they download the data from the database on the website and
    populate the local tables with current data.
    Let me know.
    Scott

  15. #15
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to get data from website to desktop? (Access 2003)

    Hi Scott - Yes the Access database is on a website which can handle .asp.

    Please let me know how you accomplish the feat of getting the data from the database on the website and populating local tables. I will be forever grateful.
    I have a meeting with the web administrator on Friday and I'm sure s/he will set up anything I need to make it easier for me to get the data out of the web-hosted db -- but right now I'm not sure what to ask for.

    Help <img src=/S/please.gif border=0 alt=please width=31 height=23>

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •