Automating update of contact details

[chutney]

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

[BradHodge]

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

If you exported it to Excel and sent it to them in that format (electonically), you could then reimport it when you got it back. This would stop you from having to key in the data. One problem you might have though is that you might have users breaking data formatting rules and therefore causing trouble when you reimport. I'll be interested in seeing what everyone else comes up with.

Good Luck, and Welcome to theScripts!

Brad.

[patjones]

If you exported it to Excel and sent it to them in that format (electonically), you could then reimport it when you got it back. This would stop you from having to key in the data. One problem you might have though is that you might have users breaking data formatting rules and therefore causing trouble when you reimport. I'll be interested in seeing what everyone else comes up with.

Good Luck, and Welcome to theScripts!

Brad.

Hi:

I would do the same as Brad...merely send out the spreadsheet, get it back updated, and reimport it into your database.

A lot of people don't realize it, but in Excel there are measures you can take to restrict the data that is entered into a group of cells. One way is to go to Data >> Data Tools >> Data Validation, and then set the proper validation for each field in your spreadsheet. (I'm assuming Excel 2007; it might be found in a different place for earlier versions of Excel).

Another thing you can do in Excel is set drop down boxes. On a separate sheet say, you type in your list of allowable values. Then, back on your first sheet, you use the Data Validation tools to set a drop-down format for a column that allows only values from the list you typed on the other sheet.

Finally, make sure that the data types you work with in Excel for each respective field are the same as what you are working with in Access. Otherwise, you'll get import errors when you try to put the spreadsheet back into Access. I try to stick to text data as much as possible. For instance, zip code is always good to keep as five-digit text, because if you put "01234" into a numeric column, it'll come out as "1234".

This could all be a little work, but you'd only have to do it once, and you would ensure that the import back into Access goes smoothly. Hope it helps!