By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,775 Members | 2,658 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,775 IT Pros & Developers. It's quick & easy.

Updating Access using Excel spreadsheets used to distribute Access info

P: 3
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
May 15 '12 #1
Share this Question
Share on Google+
7 Replies

Expert 2.5K+
P: 3,072
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.

May 15 '12 #2

P: 3
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?
May 15 '12 #3

Expert Mod 5K+
P: 5,397

You may want to take a look that transfersheet method:
...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:
Expand|Select|Wrap|Line Numbers
  1. Sub zj_excel_query2sheet_1()
  2. Dim filename As String, qryout As String
  3. fileName = "C:\Documents and Settings\All Users\Workbook1.xlsx"
  4. qryout = "Query1"
  5. DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xml, qryout, fileName, True
  6. 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.
Expand|Select|Wrap|Line Numbers
  1. Sub zj_excel_sheet2tablet_1()
  2. Dim fileName, qryout As String
  3. fileName = "C:\Documents and Settings\All Users\Workbook1.xlsx"
  4. qryout = "NewTable"
  5. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, qryout, fileName, True ', "B2:J32"
  6. 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.

Attached Images
File Type: jpg TrnsXData.jpg (51.3 KB, 3035 views)
May 15 '12 #4

P: 3
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."
May 15 '12 #5

Expert Mod 15k+
P: 31,417
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.
May 16 '12 #6

Expert Mod 5K+
P: 5,397

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
May 16 '12 #7

Expert 2.5K+
P: 3,072

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.

May 17 '12 #8

Post your reply

Sign in to post your reply or Sign up for a free account.