How to update Access table with update query using Excel data?

Topic

New Reply

I like to update an access 2013 table with data from excel. I use the update query. An older similar post is available, but was too old to reply.
The issue is that it doesn’t work in all cases.
I have access linked to a sharepoint list (SPL). The SPL list itself is the main data source, however for updating and manipulating the list I find Access much easier. Since not every customer has sharepoint access we use excel as the medium to request and provide data. The excel workbook is directly generated from the SPL.

When the updated excel work books are received I need to update the Access source table (the one connected to sharepoint).

I use the update query. 1) I choose both tables. I connect the primary key ID with the ID of the Excel table. I choose the source field in the update column and I choose the relevant field from the excel table to update my source field. So far it looks like it should work. However it results in an error ” data cannot be inserted because there is no matching record”. However when I make a selection query (same way as above without the update option) it neatly displays the connected ID (primary key) and the two fields I like to manipulate. I tried both the “linked” and “copy into new table” options. When I just generate a new excel sheet from scratch and use an ID number that exists in the source table and for example add a data column it works flawlessly. Any ideas?

Reply | Quote

Replies

Have you tried to update a record when looking at the selection query? I suspect you will find that it cannot be edited. The first thing I would look at is whether you have a primary key on the table. Import your Excel worksheet into a temporary table, and set a primary key on the ID field of that table. Then try your update process and see what happens. Let us know how it turns out.

Reply | Quote

Wendell,
Thanks for your reply. I spent a few hours delving in this issue this morning. Before I’ll provide the findings, first back to your remarks. Yes I can edit the information in the field. The source table has a primary key, generated from the sharepoint.
The exploration I did this morning:
Access has the SPL connected tables. Generated from SP.
I made a new excel sheet. I entered an ID and text field and converted it to a table. I used a ID number that exists in the Access dbase and one outside the existing numbers.
I linked the excel sheet. I made the update query and the fields were updated. So far so good.
2) I now used a excel sheet that has been generated through SP. I used the same procedure, now I receive the “no matching record” message. I did the same, but now importing the excel sheet as a table. Same procedure, same error.
3) I converted the source linked table to local table (not what I want, because the link is the reason to work with access in the first place). Made same update query with this now local table and connected with SP generated excel sheet. Now it works!
4) I brought the access dbase back to its original state (connected tables). I just copied the cells from the excel sheet as values into a new work book. Than some weird behavior: When importing the wizard add a number of empty records. They are not visible in excel.
Update query results in the error “no matching records”. The weird thing is that despite the warning some records are updated, including some that shouldn’t. In other words something wrong in the ID to ID relationship.
So my temporary conclusion is that some hidden information on the data structure in excel after being generated by SP conflicts with the import of a similar generated table in access. What I don’t understand however is when copying all cell in Excel, and paste as values in a new Excel sheet it behaves different than for the case I just make a new spreadsheet and enter the data manual (case 1).
I know,long answer, but perhaps somebody recognizes the problem/error or has a solution. Short version, in my opinion it should work, but…..

Reply | Quote

I’m not sure I have enough detail to help, but I suspect that the linked Excel table doesn’t really have a primary key as far as Access is concerned. When you link to an Excel workbook, the boundaries of the Excel data are based on the bottom row of the worksheet, which may or may not contain data, including the ID number. And that may be generated from SharePoint, as the lists in SharePoint don’t necessarily have the same constraints that you have in Access (or SQL Server). Update queries in Access generally require that both tables have a primary key, and I believe that the assumption on an Excel linked table is based on the first 100 or so rows of data. If you then hit rows further down where there isn’t any data, or there isn’t and ID, the error occurs. To confirm that is the case, you would need to select what Excel things is the complete set of data in that worksheet, and inspect it to see if there are empty rows at the bottom or within the data. Then you would need to look at the table properties for the linked table in the system level tables.

In general, the idea of using Excel to edit data, and then upload it to Access or SharePoint is an attractive idea, but unless the editing in Excel is carefully controlled with forms and code, the results are frequently problematic. The places where I’ve seen this work the best is where the developer creates a process to do the updating of the connected tables, and examines each Excel worksheet record (in the linked table) for data validity before using ADO or DAO to update the connected tables. As not many people seem to be using SharePoint with Access, but many are interested, please let us know what you find as you chase down the issues. And thanks for the level of detail in your first response. Having three (or four if your SP lists are actually stored in SQL Server) products interacting like this is complicated.

Reply | Quote