Working offline downloading data from server

[diegomarino]

Hi,
i'm using an offline database that i upload on onedrive and download every time in two PC.
Since i'm going to use powerapps too i need some online server but i don't want to need always connection when i use my access database.
So i was thinking to have a local copy of every sharepoint list (i used sharepoint before), and i'm figuring out the best practice to do use this procedure, maybe on opening the database i update the local copies of sharepoint lists or, i dunno, make a copy of every sharepoint list in my database as local tables.
then, on closing database i have to update my sharepoint list from local tables.

i like to work offline also cause it's faster, i have some superheavy vba code and reports, and i saw the difference between offline and sharepoint.

what's your suggestion? consider that i do not use relations in access, i just link tables using comoboxes in forms, and i'd like to stick on that, so no sql server or stuff like that, i need very simple storage

thanks

[CJ_London]

I wouldn't be doing this

i'm using an offline database that i upload on onedrive and download every time in two PC.

unless there is absolutely zero chance of both PC's being in use at the same time. Onedrive has a file transfer protocol, which is not wha Access uses.

In Beta testing at the moment is a dataverse connector for access - it may or may not suit your needs - but is intended to make data from an access database available to powerapps and the like. google it to find out more.

Otherwise at the moment your choices in relation to powerapps is sharepoint or azure.

[diegomarino]

i'm the only one using that.

ok for sources, so what's the procedure? have a local copy that i update from server maybe using the "modified date" field?

[CJ_London]

what do you mean by 'sources'? for what? powerapps?

[diegomarino]

For sources i mean my tables in a server (maybe sharepoint), that i use both for powerapps that for access. In access i'd like to have offline tables that updates from server: how could i do that? i have a "created" and a "modified" field that i update when i create or modify a record, should i update my local table looking at new records or modified ones?

[CJ_London]

Providing you are the sole user and only 'logged in' from one machine at a time you could just download all the master data at the start of a session and upload again at the end of the session.

Have linked tables to your server in the front end as well as the linked tables to the local back end. At the start of the session run a routine to delete the contents of the local tables and repopulate by inserting the master data back to the local tables. At the end of the session, reverse the process. If you don't have much data that is probably the simplest way to go. To reduce bloat, rather than deleting local table contents and repopulating, create a new backend each time from an empty 'template'

You might want to google what is called an 'upsert' query which will update changed records and insert new ones, plus you will need a delete query to delete records in the local data that are not in the master data. Might be more efficient if you are not creating a new backend. You will need a PK or some means of uniquely identifying a record such as a hash key - see below

However sounds like you are talking about synchronisation which is a whole new ballpark and I'm not sure sharepoint has the appropriate datatypes.

You will need more fields than created and modified date/time fields. You will also need an autonumber field preferable set to random rather than increment to minimise risk, you may also need a GUID field and perhaps a hash key. And typically you need 6 queries for each table (plus the linked table). In no particular order:
1. insert records in master but not in local to local - use PK
2. update records in local different from master - use PK, datemodified and hash - only update records where master modified date is later than local modified date
3. delete records in local but not in master - use PK

4. insert records in local but not in master to master - use PK
5. update records in master different from local - use PK, datemodified and hash - only update records where local modified date is later than master modified date
6. delete records in master but not in local - usePK

* a hash key is obtained by hashing all the values in the record where change matters into typically a 10- 20 character string. This is stored as an indexed field

you might be able to adapt 1 & 2 and 4 & 5 to an upsert query. Depends on your data. You say 'i need very simple storage' which says to me your data is not normalised. Unnormalised data is a PIA with databases and generally very inefficient.

You might also look at using data macros. I have a client where the master data is in the local tables (accessed by a number of users simultaneously) and the backend is in a mysql database utilised by their website. I use a data macros to instantly update the mySQL data whenever a record is added or changed (they don't delete). But again needs an internet connection

[diegomarino]

that's a lot of stuff. i will study my solution, but i have all i need, thanks