MS Access - Update Linked Table (1 Viewer)

r24igh

Registered User.
Local time
Today, 00:02
Joined
Apr 18, 2016
Messages
16
Hi all,

I'm building an access DB which has a linked table to excel, which is working and all is fine on that front.

What I need to be able to do, is build in functionality that can edit the linked table. As I understand, I can't do this because the data is dependent on the table and can't be overwritten.

To give you some background, what I'm doing is building a supply of workers i.e 10 people across 3 teams and the # of hours they work, to project the resource we have MoM. The aforementioned data is fed from a HR database. This comes through to the access table and what I want to do is build a form that allows me to factor in things like absence and holidays as this db will be used by a centralised team, not HR and HR do not record holidays. Is there a way I can "unlock" the cells to be able to overwrite them OR is it more practical to build a form that updates the source excel sheet directly from the access DB?

If the latter, can anyone offer some pointers? Not shy of VBA but by no means an expert.

Any alternative solutions are much appreciated.

Thanks!
 

JohnLee

Registered User.
Local time
Today, 00:02
Joined
Mar 8, 2007
Messages
692
Hi,

I worked with something like these a good few years ago and I found the best option was to not link to the supplied excel spreadsheet, but to actually import it as a table within the database, that way I found I was able to do the things like you mentioned without any problem.

What I did was to establish with the HR department how often they were likely to update their supplied information and based on that I set up an automated process that checked for when that excel file was updated and my process would update my Database table with the latest information from the HR departments Excel spreadsheet.

I found this method worked very well and I was able to achieve what I wanted in my database with the provided data.

Regards

John
 

r24igh

Registered User.
Local time
Today, 00:02
Joined
Apr 18, 2016
Messages
16
Hi,

I worked with something like these a good few years ago and I found the best option was to not link to the supplied excel spreadsheet, but to actually import it as a table within the database, that way I found I was able to do the things like you mentioned without any problem.

What I did was to establish with the HR department how often they were likely to update their supplied information and based on that I set up an automated process that checked for when that excel file was updated and my process would update my Database table with the latest information from the HR departments Excel spreadsheet.

I found this method worked very well and I was able to achieve what I wanted in my database with the provided data.

Regards

John

Hi John,

Thanks for taking the time to post and share your experience!

This is probably the best alternative solution there is! It makes a lot of sense to make it a static file and then upload the data as it is updated. The only constraint there is, is that it would not record holidays. I would have to keep a record and manually update each time there was an updated file. Did you encounter similar problems? If so, what was your workaround?

Thanks again for your time and help.

Kind regards,

Ross
 

JohnLee

Registered User.
Local time
Today, 00:02
Joined
Mar 8, 2007
Messages
692
Hi,

I found that I was the first in the know about when holidays were being requested as the system that was in place for the company I worked for required all requests to go through Team Leaders/Line Managers, so based on how the HR department calculated an individuals holiday I produced some code that would work out based on their hours of work would calculate accurately their annual entitlement, Team Leaders/Line Managers would access the database for their own staff [they didn't have access to anyone else's records] and enter in an individuals holiday request and they were responsible for authorisation of that holiday, the database would calculate holiday taken/requested and display holiday remaining based on that information.

So based on that basic process, Team Leaders/Line Managers and of course the central administration department had a good tight handle on holiday management.

Now I know that there are many systems created to manage that aspect much better than what I have described, but for the purpose of what was needed back then it work for me.

I hope this has been helpful and you are able to work out how manage that aspect.

Regards

John
 

Users who are viewing this thread

Top Bottom