Solved

Update a Linked excel table

Posted on 2015-01-21
10
114 Views
Last Modified: 2015-01-21
Experts,

I have a linked excel table.
It appears fine.
The excel table within Access seems to be locked down and I cant make any edits from within in Access.  

Is it possible to remove this lock down in the linked excel table within Access?  I want to make edits in Access and have those same edits made in the excel file.  I want to move away from making the edits in the excel file.  I have thousands of rows.

thank you
0
Comment
Question by:pdvsa
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 40562490
It is not possible to edit linked Excel file. It was possible before Access 2003 (not sure in exact version), now you can do it only from Excel:
https://support.office.com/en-us/article/Learn-how-to-import-or-link-to-data-f4a5b6e6-c98d-43a0-9588-0eab19a75619
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40562526
I want to move away from making the edits in the excel file
A common alternative is to simply move this data to Access exclusively...

...Then you can link the Access data back to Excel for any Excel specific analysis you might need.

But also note that thee are many tools in Access that can analyze your data to the same level as Excel...
(so again, ..it may be an option to move the entire system to Access)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40562533
But with the above setup, you still could not make changes in Excel, and have those changes reflected in Access.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:pdvsa
ID: 40562593
Thank you.  I plan to move it all inside of access but I am not at that point right now.  

I do however import the data from time to time when I need to make a report.    If I had an ID on the excel side possibly I could use an executable to open the excel file with a docmd.open where ID=ID?
0
 
LVL 10

Expert Comment

by:10023
ID: 40562608
You can turn it around and export the data from the access table to the excel spreadsheet and then view the updated export action in access from the external data tab
0
 
LVL 10

Expert Comment

by:10023
ID: 40562618
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40562642
If I had an ID on the excel side possibly I could use an executable to open the excel file with a docmd.open where ID=ID?
...So has your question here changed?
0
 

Author Comment

by:pdvsa
ID: 40562652
10023:  I thought of exporting to excel but I would lose the formatting.   I have quite a lot of formatting in the original excel file.
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 40562668
The reason that Access can no longer update a linked Excel Spreadsheet is because MS lost a lawsuit that claimed they used code covered by a patent or copyright.  At the time MS refused to license the code from the original developer and so was required by the court to remove the functionality from Access.  The update was actually to Jet and so affected all versions of Jet.

Now, the only way for Access to update Excel is by using automation or the TransferSpreadsheet method which just pushes out an entire sheet.
0
 

Author Comment

by:pdvsa
ID: 40563123
thank you Pat
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access Date/Time to SQL Datetime2(7) 9 27
Display label on subreport when NO DATA on subreport 4 22
Access Update Query 1 20
Access 2003 query lost it's only join 7 27
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question