Dear all, please excuse the fact that this is not an explicit problem I have, but more of a general query.

I have an Access database (2003) with a load of contact details in it. Including various fields for each contact, such as their email, services they offer, where they are, main contact name etc.

Some of these contact details will be out of date and I want to make sure that all the contact details are up-to-date.

I am in the process of setting up a mail merge with a Word document which will output to Word their relevant details, and this can then be e-mailed to each of the contacts with them to update and e-mail back again. Someone would then manually update each of the contact details.

This is quite a time intensive way of doing it, and I wondered is there a way of automating the process? I don't have the time to put the whole of their details on a website so they can update it over the web, but is there a way that access could possibly read the return Word document to automate updating their details? Or is there another easier way of doing it, without using word and using something else such as Excel or something.

If anyone has any experience in doing this kind of thing I would be really glad of some tips.

Many thanks

I've used Excel Forms in the past. And you can link directly to the spreadsheet and read in the data.

Hi Chutney,

I think you are going to struggle to automate this process. This is the central core of a CRM application and the majority of the professional systems run by banks rely on telephone centres to update details changes as and when they are informed of them. If you have a lot of contacts and not much time or money I would suggest a possible OCR sheet posted to the relevant parties. Alternatively there are web sites that may allow you to collect information eg survey monkey. Alternatively hire a student give him a phone and get him to phone round / investigate on the web. I personally keep contacts database just for me and add notes when talking to people. It currently stands at 1230 probably lots of incorrect address information in it but the people I contact a lot will be right and if someone phones up who I haven't talked to in a while I'll ask them to confirm that their details.
M

Hi lightwave

Thanks for your pragmatic advice.

Before going down the telephone route, I will look into how long it would take me to set up a form on the web that would load in their details and they could then update.

B.

If you are using Office 2007, this is neatly packaged for you. One of the built in features is the ability to email to a selected group of people and to automatically process their responses. You can do this in earlier versions but there is a lot more work involved in pulling the pieces together. In 2007, Access creates an Outlook form and has the response sent to a particular mailbox. It then reads the messages in the mailbox and applies the changes to the database. This is a truely slick feature. However, isn't there always a however, there is no programatic interface to this feature. So, your users will need to know how to define the mailing and start the process rolling.

Hi Pat,

Thanks for your reply. I am using 2007, though the client is using 2003. I did investigate using this package, but many of the contacts that the form would be sent to are not using Outlook, and, correct me if I am wrong, but I think the form can only be updated if the user is using Outlook. I did a test sending a form to my gmail account, and I couldn't update it.

B

You're correct, the clients need Outlook. Excel is another option but you may run into trouble with that as well. I would not use Word because of all the Office products, Word is the most difficult to automate. Every time I set out to do something new, I feel like I'm playing pin the tail on the donkey.

You're correct, the clients need Outlook.

Hi,
I was afraid of that (that Access 2007 & collect data by emailed forms) will only work if recipients are using Outlook. I just ran a test and I can create the email with the "form" to be filled out okay, but at the recipent end, I can't type anything into the fields if I'm receiving the email in Thunderbird, Eudora 7, or gMail. I don't have outlook on any of the recipient machines to test with.

Also, I had set up my Access table with drop down choices (my real life test is an email form by which people will choose their menu options for an upcoming Christmas lunch). In the resulting email "form" you get a field to type into, and at the bottom of the form is a list of the options. So back up in the form, you'd type a "1" if you wanted chicken souvlaki, a "2" for lamb souvlaki, etc. So the form doesn't present a drop-down list. When the email is returned to Outlook/Access, I assume that Access looks at the number in the field and back in the Access Table, substitutes the lunch item corresponding to that number. The weakness seems to be in relying on recipients to be using a flavour of Outlook (full or express), is this a correct assumption?

I can send my email form if you are interested. My address is my forum user name @gmail.com.

Thanks,
-Tony

Don't forget you need to hit reply before the form accepts input.

Dear Pat and Tony,

Thank you very much for your responses. I have decided to simply do a mail merge to word to extract the information, but not to have it update automatically (it will need to be manually inputted). It seemed to be posing too many problems, unless the client had the money to splash out on a web form (which they don't). I'm still waiting to hear for the go ahead from the client.

Ben