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.microsoft.com/en-us/access-help/what-happened-to-data-access-pages-HA010030832.aspx]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...AFAIK: 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 ?

[zmbd]

and you didn't answer the question...

[rajeevs]

Yes. They will do the needful from their side (web service). But to start with from my side I need to have something which can call the web service from ACCESS and send the data. I read somewhere about SOAP toolkit 3.0. I have downloaded that but I don't know how to start with. My access version is 2007.

[zmbd]

1) SOAP has been depreciated for over 5 years... migration to webservices pushed since 2002

2) You will still need the basic connection information from your Webservices provider. Once again, they should be able to help you setup the connection.

3) How are you sending the data to your provider now; FTP, Email, or what? In what format are you sending the data; txt, csv, pgp, asx, etc?

[rajeevs]

Answer to Q2 : This part they are ready to provide
Q3 : It was individual staff login to the web and enter data
We want to change that concept and link directly to the web service and send data for all staff by one user

[zmbd]

Well, then try the following:
Skip to the section for publishing:

Create a Web Service data connection (ACC2010) It is for 2010 however, other than the location of controls the method should be the same in 2007.

Microsoft Access 2010 lets you link to external data that is available through a Web Service. You must already have the Web Service connection file, which is supplied by the Web Service provider.The general process of using Web Service data is as follows:

[rajeevs]

Thank you zmbd for the patience.
I have tried the link. But the begining it says as :
Create a connection to a Web Service
1.On the ribbon, click the External Data tab.
2.In the Import group, click Web Service.
But I checked my ACCESS 2007 ribbon and cannot find Web Service in the import group. So i am stuck there

[zmbd]

I was hoping that ACC2007 had webservices installed.
Looks like you are going to have to use the SOAP toolkit or develop a custom dot-NET COM add-in for ACC2007.

It has been a few weeks since ADezii was around, perhaps he'll hear me talking about him and drop by the site. He was able to help with a SOAP webserviced connection here http://bytes.com/topic/access/answer...-access-2007-a maybe he'll be around shortly to give us a hand and perhaps the link will give you some ideas.

Along that same line, goto the top of this page, in the search box enter "Access 2007" SOAP there were a few other threads there dealing with this.

If they can provide a SQL-Server, or MYSQL IP connection string we might be able to go that route; however, I've never tried to establish a raw connection over the internet... usually a VPN network which once setup up you would link into the database like anyother networked drive..
.
>>>EDIT>>> Z's storing this link here just in case it proves helpfull' however, he hasn't finished reading information
Connecting to SQL Server over interner

[rajeevs]

Hi zmbd
I am also hoping that ADezii will join. Meantime I will try the link you have provided tomorrow and advise you tomorrow the result

[rajeevs]

Hi zmbd
I tried the link but nothing achieved. Hope I will get some help to overcome this situation