access database records to web service

[rajeevs]

Hi
I have an access database (2007) for managing staff leave. I would like to connect it to our web service and send the records and get response from the web service back.
Can anyone suggest or guide me how to achieve this.

[zmbd]

This is very difficult to do depending on your situation.
You will have to work very closely with your IT department once you have everything in place.

Please use the search next to the bytes logo (or just to the left of the orange magnifying glass):
access zmbd web database

and again
access zmbd publish

and this link:
http://bytes.com/topic/access/insigh...ccess-data-web KEEP IN MIND: Data access pages support has been depreciated as of ACC2010 and completely removed in ACC2013.

http://bytes.com/topic/access/answer...atabase-online is where this has progressed.

[rajeevs]

Dear zmbd
Thank you for the reply.My IT will be providing the web service and other requirements related to the web. I like to know that after generating my recordset from ACCESS how can I transfer that to the web service. What is the easiest way? Is there any VB code can do this transfer of records from ACCESS to the web application?
Basically the data in ACCESS is staff leave and we need to transfer those records to the web service and update. Hope I have explained about my issue. Please guide me

[zmbd]

I would go with a hybrid share-point

What I mean by that is that most of your actual work is handled by the Access program. You Design a few very basic forms and tables to handle the Lists within the Share-Point server.

This is what I've done.
1) Decided on what I really needed from the share-point side of the interface... in the most simplistic terms

2) Used the web-database template (ACC2010, I've not used ACC2007 so I'm guessing that this is fairly much the same) and developed the most basic forms and tables that I could.
2a)(We have a person on site that mainly handles Share-Point development. Often, I can simply design the front-end with the look and feel that I need and he can translate this fairly much from the Share-Point developer's side and I need only link into the lists once done).

3) Started development of my "Normal" database with a split back-end and then add links to the share-point tables.

Moving data between the linked tables is no different than any other data movement.

Introduction to integrating data between Access and a SharePoint site

This is for ACC2010 and some of this may not be available in ACC2007 but you should know about this too: Edit or publish a web database on SharePoint 2010 (note the applies to ACC2013 tag, that's a recent change to this page so I suspect this will also apply in ACC2013)

Things to keep in mind:
- Forms and reports that are developed for Share-Point do not support VBA and support only a very limited set of the Access Macro language.
- Share-Point does not support table relationships. The work-around is to use the most hated look-up-field wizard and create look-up fields at the table level.
(I try to avoid this; however, it often can't be avoided)

Some other things:

Ways to share an Access database (ACC2007)

Publish a database to a SharePoint site (ACC2007)

Finally:
Do not use the older MDB file format to try and workaround the removal of Data Access Pages (url=http://office.microsof t.com/en-us/access-help/what-happened-to-data-access-pages-HA010030832.asp x]What Happened to Data Access Pages[/url]
There is no effective support in ACC2007/ACC2010 and they cannot even be opened in ACC2013 without a major hack.

[rajeevs]

Thank you for the detailed reply. But my problem is not to share the data or datbase. I need to send the records to an existing web service (which manages the staff leave.
So in other words I will populates the records from ACCESS and upload that to a web application. Hope I explained well

[zmbd]

ah... a much different beast than what I thought you were talking about.

My advise there is to talk with the database administrator with your provider. However, most likely a text file export will be the option. Without a lot very detailed information from your provider we'll not be able to help you... and I highly doubt they will want to release that kind of information to a public forum.

Something else, Plain text files scare me a tad so I would look at sending it encrypted.

[rajeevs]

Dear zmbd
Yes. The concept is sending or transfer the query results to the web application. The access database will produce the result recordset. I want to transfer the recordset to the web application. I will have the username and password to access the web service and the web service will be able to receive the data and store the data. In return the web service will send a response for each record processed.I can provide the structure of the recordset which the database produces and expected string return from the web

[zmbd]

You will still have to talk with your service provider as to how/what files they will allow and what protocols they allow, usually a FTP transfer. If this is sensitive information then I would also be inquiring about secure transfers with your provider.

As for directly linking over the interner...AFAI K: only via SharePoint (and that's iffy) or a VPN services, once again, this is provider specific. The base point here being that otherwise, Access isn't intended to operate directly over the internet.

[rajeevs]

Thank you zmbd
I have only a logical thinking about this issue. I was reading lot of posts over the internet about web service and transfer of access recordset. Now my first question is how can we transfer a TXT or XML file to a web application if we have access to the web application? Secondly how much coding is required. I thought it would be possible by VBA or some kind of scripting. Hope you understand my scenario.

[zmbd]

rajeevs
Until you contact your service provider and we have some more details, there is nothing more I can help you with.

Perhaps someone else reading this thread will be able to provide some more guidance???

[rajeevs]

Dear zmbd
You mean our system admin or internet service provider?
Please explain what kind of information you are looking for. I can provide

[zmbd]

The Webservice provider.
They are the ones that will be able to tell you about your VPN connection requirements, or how they want to receive the updates to your service's application.

[rajeevs]

The data can be transferred manually from the access DB by a user on a weekly basis. It is basically a query result with certain records (not many - may be a maximum of 200 records)send to our own web application residing in another location through internet. After successful transfer the DB has to receive same recordset with two three additional fields which the web service will provide

[zmbd]

The data can be transferred manually from the access DB by a user on a weekly basis.

And pray tell, how is that being performed?
If you want to automate this process, then you MUST talk with the person(s) responsible for providing your service.

[rajeevs]

Thank you for the quick reply zmbd. Desperately that is what i am looking for :How to transfer the records from ACCESS to web ?