Updating Access using Excel spreadsheets used to distribute Access info

[dchyde]

The volunteer organization I work with maintains an Access database of its members. Periodically, geographically specific member lists with portions of each member’s information (the balance is confidential) are distributed to geographically specific coordinators in the form of Excel spreadsheets. While using their member lists, the coordinators learn of updates to the information about members in their area and make changes to the spreadsheets they have received.

Is there a simple and reliable way for the central Access database to be updated using the changed spreadsheets the coordinators create and avoiding duplicate data entry? Please understand that the organization has no IT personnel, so the process would need to be very straightforward.

Thank you

[nico5038]

Excel sheets can be linked as a table and used for input.
It's however very risky, as datatype problems are common (erroneous cell definition after copy/paste) and you'll need a unique ID to identify the row in the original table.
Thus I would probably create such a linked table and show the differences, to update the fields manually.

Nic;o)

[dchyde]

Thanks for your response, Nic;o). What I gather from your response is that for confidence and accuracy, the Access database should be maintained manually.

If coordinators simply make note of the changes they are making to their Excel member lists and forward just the changes to the central office that maintains the Access database for manual entry, that would be the same result, correct?

[zmbd]

dchyde:

You may want to take a look that transfersheet method:

http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

...You can use the TransferSpreadsheet method to import or export data between the current Access database or Access project (.adp) and a spreadsheet file...

Normally, I've used this to push the data out of the database such as in the following:

Sub zj_excel_query2sheet_1()
Dim filename As String, qryout As String
fileName = "C:\Documents and Settings\All Users\Workbook1.xlsx"
qryout = "Query1"
DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xml, qryout, fileName, True
End Sub

Using this, you will get a workbook with a worksheet named "Query1" and it would look like:

The first row has the field names as the header.

Now I ran this as an import.

Sub zj_excel_sheet2tablet_1()
Dim fileName, qryout As String
fileName = "C:\Documents and Settings\All Users\Workbook1.xlsx"
qryout = "NewTable"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, qryout, fileName, True ', "B2:J32"
End Sub

The first time ran without a hitch (first time I've done this :) ) Notice I set the qryout="newtable"... when ran, this table was created and then populated with the correct information. I then opened the workbook and made a few changes and added a few new "records"... I then ran the same code a second time and these changes were appended to the table.
I suspect, then that an update query would be called for and then ether delete the table or clear the records.
An update query would allow the changed data to reflect in the orginal table and any new records should be appended.

You should make a copy of your production database and see if this points you in the right direction.

-z

[dchyde]

Thanks zmbd. This is all over my head, I'm afraid. I am probably in the wrong place for someone of my level of sophistication. You were very generous with your time in putting this together and I am ashamed at not being able to take advantage of your effort. I need to get "Access and Excel for Dummies."

[NeoPa]

In short, it's possible, but simplifying it for the users would involve a level of automation that I doubt you'd be comfortable with.

Essentially, for any individual worksheet, the process would have to include :

1. Import the raw data in. Probably into a temporary table.
2. Using this table update the original data in such a way that records from the temporary table and records from the original database are matched accurately and reliably. Complexity increases related to how many separate fields and possible updates are supported.
3. If new records are required as well as updates then, as long as they are correctly and safely identified, these can also be handled in the previous step.
4. Lose the temporary data/table.

[zmbd]

@dchyde:

My pleasure...

IN #6, NeoPa stated the steps in a much better way than I did in my post.

As for being in over your head... better to drown than to have the lions eat you for lack of trying! };-) Even the "Experts (which I am not!) started out in the shallow-end of the pool!

- Because we don't have all of the design information behind your database, any further suggestions that I might make might muddle things further for you. However, I can see this working in my mind's eye!

Best Wishes
-z

[nico5038]

@dchyde,

You're right about your statements in #3. The number of changes will be limited and manual updates are in my eyes to be preferred, as the coordinators can make errors too. By automating this process you loose a set of eyes checking for errors and the time to build this is probably more than the time needed to make the changes manually.
The best way would be to switch in the future to a web based application and authorize the coordinators to maintain the name and address data.

Nic;o)