Redirect linked tables

Question

I'm using Access 2016/Office 365 on 4 computers.  From a variety of sources I download Excel data files (.cvs) and save them in a folder in Dropbox (available to all 4 computers.)

I have several databases, each in the same folder as their linked .cvs  data file.  I'm the only user but I will work on any one of the computers (Same Wi-Fi, different rooms).

The link between the Access tables and the data files varies depending on which computer I'm working on.

 e.g. C:\Users\jw_000\Dropbox\medmgmnt or C:\Users\JnP\Dropbox\medmgmnt, etc.

I can edit the link depending on the computer but would really like to  have that done automatically.  I found a couple articles on relinking tables in a BE to the FE application but can't quite figure out how to use them for a table<=>.cvs link. 

If I split the database, will the table to .cvs links remain constant for each computer and I would only need to follow the example to link the BE to the FE.

Any suggestions would be appreciated.

Thanks,

Jeff W

Answer 1

OneDrive is no real difference than Dropbox. They're both cloudbased storage devices, and Access doesn't play nicely with any of those.

Splitting the database during development is equally as important as it is in use. Corruption most often occurs in the objects (forms, reports, etc), and if you store everything in one container - including your data - you could end up losing everything.

Answer 2

Yeah that’s what I thought.   Thank you for the advice.  I’ll split it and see how that goes.

Answer 3

Thanks Scott.  Will try the network approach for the linked data.  Don’t want to split the db as it is a work in progress and the FE needs to be in sync on each station.  Would OneDrive be a better option?

thanks,  Jeff

Answer 4

First and foremost: Access is not suited for wireless connections. You may be okay with it now, but it's just a matter of time before you corrupt your database (often without hope of recovery). Access needs a very stable, fast, and wide network path, and WiFi doesn't fit at least one of those criteria.

If you're using linked tables, you're already using a form of split database. A Split Database contains a Backend (tables only) and a FrontEnd (everything else). The FrontEnd is linked to the Backend, and a copy of the Frontend is installed locally on each machine (i.e. it is NOT shared from a network location). You may also have some local tables in the FE, but in general those store local user settings and such. Data that is shared among users should always be stored in the common backend.

I'd also caution you against using Dropbox as a location for your backend. Doing this will result in multiple copies of your backend, since DropBox will create one for each individual "Drop" location, which means you never know if you're working with the correct "version" of the backend. You would be much, much better off placing the backend on a network share and linking to it using the full UNC path:

//machinename/folder/database.mdb

If you do this, and if you ensure that location is available to all machines, then you won't have to worry about where the file is located.