Solved Edit Records from outside the Access DB

[raziel3]

Is it possible to edit records from outside a database via Excel.

Here's the situation. My co-worker has a Database saved in her OneDrive the problem is that, if I want to edit records I will need to download the database, make changes and reupload. If I do that while she is adding records all her work will be overwritten.

Splitting the database is not an option because I will not be able to link to the backend stored on OneDrive and to get a vpn set up is whole other problem (IT again ).

I was thinking to use power query in Excel to make the connection to the database but that is read only. I would still need a way to push back the edits to the database tables.

What are my options?

 

[theDBguy]

I'm not quite sure how OneDrive works. As far as I know, it is a "whole" file architecture rather than a "connection type" one. In other words, I believe OneDrive works by keeping two copies of a file (one local and one online) synchronized. What you need instead is be able to access the single file in one location. Access allows that for the local copy, but OneDrive probably doesn't allow that for the online one. I'd like to hear what others might recommend.

 

[June7]

Yes, Excel VBA can set a connection to Access and edit data. Example https://stackoverflow.com/questions/61169073/updating-records-in-access-table-using-excel-vba. I doubt can be done with OneDrive. All advice I've ever seen says not to use OneDrive to host a multi-user database.

 

[raziel3]

All advice I've ever seen says not to use OneDrive to host a multi-user database.

Yeah I know but because of the Security setup on the PCs, options are limited.

 

[Pat Hartman]

You CANNOT safely share an Access database using OneDrive or any other cloud technology. PERIOD. Either you have to schedule your sharing so you don't clobber each other or only one of you can do any updates and the other has to communicate their updates using Excel or some other file type.

 

[MsAccessNL]

Save your data to a textfile on a shared folder (one drive) let Access check (on timer or other event) if this folder containsa file and import the data, move the file to an archive folder after update. I made a db this way wich checks for updates every second.

 

[The_Doc_Man]

Yeah I know but because of the Security setup on the PCs, options are limited.

Here is your solution. Someone in your office won't like it. (Maybe you; maybe someone else.)

First question: How important is this database? If it is not mission-critical, you are going to be stuck. If it IS mission-critical, then...

Second question: Does the person who wants the output from this DB influential within the company? If not, you are stuck here. If yes, then...

Third question: Will that person talk to the IT staff about the fact that they are blocking mission-critical data from being gathered?

The goal is that if this is important, there will be a management channel that will eventually loosen up something. If it is not important, consider the Serenity prayer and learn to accept that which you can't change.

OneDrive WILL NOT WORK with an Access back-end file. It doesn't allow the right protocols AND you don't see the "real" file anyway. You only see its shadow, and as you already pointed out the shadow can "lose out" if another shadow comes along at the same time.

In fact, even with EXCEL, you run into the same problem because on your local PC, you are working with the shadow, not the original.

 

[gemma-the-husky]

I would be reluctant to manipulate Data from outside access. You even need to cautious editing access data directly in the tables.

Generally you have lots of code to manage and validate the data, and prevent erroneous data being entered. By using excel or editing tables directly you immediately lose all that protection. Users can do anything to the data with no control at all.

 

[raziel3]

Thanks everyone. I decided to give up on editing the data simultaneously and schedule updates until I can get IT to set up both PCs on the same network.