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

Topic

New Reply

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.

Reply | Quote

Replies

Hi Gwenda

Your clients don’t want much do they

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.

Reply | Quote

Hi Jerry – Thanks – I need ideas.

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

“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

Reply | Quote

Edited by HansV – [E] is converted to by the Lounge, and 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

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][.]*”) Then
szCaseTitle = Trim(szFldData)
End If

If (szFldData Like “*[D][A][E][F][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

Reply | Quote

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?

Reply | Quote

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 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

Reply | Quote

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

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

Reply | Quote

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

Reply | Quote

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 ) .
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 .. 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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

I was away for a couple of weeks, so I missed the start of this thread, but I see that nobody has mentioned replication as a method for getting data syncronized if you have to have two copies – one for the desktop users, and another for web users. It adds some significant overhead, and doesn’t give you simultaneous updating, but if once or a few times a day will suffice, it might work for you. Sounds like lots of fun – we’re about to start a similar project for several clients where they want two copies, but we are using SQL Server which gives us some different options.

Reply | Quote

Gwenda,
I am away from the office at a meeting this week. I will post an example next week. Basically you have an .asp page for each table you want to download the data from.
These pages will be in the same folder as the .mdb file on the web site.
The code in these pages access the data in the tables on the web site.
The local database when opened or whatever time you want(button click etc…) runs code to see if it can connect to the web site. If it can, it deletes the current data in the local tables, connects to the .asp page and appends the local tables with the data from the web site tables.
You do this for each table you want to get data for.
The only thing you will need to edit in the asp pages is the table you want to get the data from and the actual name of the .asp file. This name is used in the Access database code.
Scott

Reply | Quote

I’m looking forward to seeing the code … does it use an ITC contol inside Access VBA to connect to the web site?

Reply | Quote

Note that in Scott’s situation, the local tables are being replaced, so all editing has to be done on the web copy – will that work in your situation?

Reply | Quote

Gwenda,
I had some extra time at the meeting so I put this together.
Here is a stripped down version of the components you need to do web downloads.
It incluses the .asp file and the database components.
There are a few items you will need to edit.
The asp file:
On the 10th line, change the database name to the name of the databaes that is on your website.
On the 14th line, change the table name in the sql statement to the table name of the web database you want to get the records from.
The actual name of the asp file. I usually call it the same as the table I’m getting data from. As you see this one is getting customer
data. The name will also be used in the local database code.
Upload this file to the same folder on the website where the database is located.

Now to the local database:
In the module modConnected edit the first line after the Dim statements in the isConnected function.
Replace http://www.google.com with the name of your web site. Save and close the module.

Table setup:
Create two tables with the same structure containing all of the fields you need from the web database.
Call 1 table tblTempImport and the other one whatever you want. In keeping with the theme I’m calling it tblCustomers.
We import the data first to the tblTempImport in case you need to do any manipulation of the data before you append your main table.
In my applications I delete all the data in tblCustomers before appending the table to make sure I get any edited data from the web.
It can also be modified to only capture certain records. eg. only data after a certain date range if you have an input date field in your web data.

Code in the form frmGetData:
In the declarations section of the code change the URL to the url path to where the web database and the asp files are.
Your web master should be able to give you this info.
In the Form_Open code edit the getCustomers.asp to what you named your asp file on the web.
Edit the 2nd DoCmd.RunSQL statement and replace the table name with yours. This will delete the existing records.
Finally Edit the table field names in the rst.addnew section of the code to match your table names.
The !rs field names are from the web site table, the !rst field names are from the local table
The !rs table names don’t have to match the rst! names
Finally open the form frmGetData after your routine to open your database or from the click of a button or wherever you want.
The form doesn’t usually show up on the screen. I open it on the close event of my splash screen (about), this way the splash screen stays
visible and has a message that it is downloading current data.
Have fun
Scott

Reply | Quote

Scott – I’ve been busy writing the proposal as I’m sure now that I’ll be able to fulfill. I haven’t reviewed your code yet, but will start later in the week. Thank you!

Reply | Quote

Hi Scott, First – THANK YOU

I really like the code within your application because you’re using the Windows API directly. Your code is working for me, but now that I’m ready to start work, the specs have changed.

The ISP is now going to deposit a CSV or XML file for me each day at midnight (the file will have the date as part of the filename), so now I have to go get the file off the site instead of reading an Access db on the site. Once I’ve got the file, I’ll save it on a local hard drive, parse it and then update my Access app.

Does the Winsock API have a function like WinInet’s FtpGetFile function? Do you have any suggestions about the best way to grab a file off the site from within Access?
Gwenda

Reply | Quote

For anyone interested, here is the answer.

Working with the website developers, we decided that the website form would publish to a Comma-separated-values (.CSV) text file, which would be sitting at an ftp site. Each day at midnight, a new file (which has the date as part of the filename) is created. Then each day at 2 am, Access uses the Windows API and calls methods in WinInet.dll to go get the file from the previous day and download it to the local harddrive.

I’m still working on this, but the basic ftp download from wihtin Access is working. I’ve attached the code.

Reply | Quote

G’day Gwenda,
Thank you for the code you attached to your last posting. I have found it very useful. In fact it helped me develop a transfer function which I call when my MSAccess database is loaded. The function copies shared databases from my FTP site to the local computers. This all works perfectly in MSAccess 2002! Thank you very much! I have attached my code.
I have a further problem I am hoping you can solve for me. Some of my clients use MSAccess’97. One line of code will not compile:-
Result = InternetSetStatusCallback(mOpen, AddressOf INTERNET_STATUS_CALLBACK)
This will not compile because MSAccess’97 does not support “AddressOf”. Can you please tell me how to get around this?
Regards and many thanks, Terry

Reply | Quote

Michael Kaplan and Ken Getz wrote a replacement function for use in Access 97. The site where it was originally published isn’t available any more, but you can copy the modAddrOf module from the SubClassFormatByCriteria database from FomatByCriteria by Stephen Lebans.

The syntax of this AddressOf function is slightly different than that of the AddressOf operator in Access 2000 and later. Instead of

AddressOf INTERNET_STATUS_CALLBACK

use

AddressOf("INTERNET_STATUS_CALLBACK")

Warning: as is noted in the module, do not try to single-step through the code for AddressOf, for that would crash Access.

Reply | Quote