the ProvideX DB access is only through the ODBC DSN and is read only access, so even if I gave the (you nailed it Vlad) inexperienced users the ability to delete the linked table in Access completely they would not affect the source data. This is why I referred to the Access linked tables as 'copies' of the tables. I know they are like looking at the source table live, but we can't alter the source table in anyway via Access. The only thing we use the tables for in the Access linked table is to run custom reports that don't exist in our accounting system as canned reports. We have no need to add or delete records from the linked tables. If a user needs a 'custom' data set, I will build it with a query in Access so that the user only needs to filter for what they need. So the queries must be in access as I have ZERO ability to work in the ProvideX DB itself. The only way to make ProvideX DB changes is through the accounting system (MAS100) front end interface or through their built in hex editor.
Most of the users import the data into Excel and do whatever from there.
As far as dropping/recreating or updating the tables, I will go with which ever is easier. Like I said if the Access DB gets wiped out completely, I didn't affect the source tables and I can restore the Access DB just by running the import job again. The ProvideX data is backup every 15 minutes to a duplicate VM of that server, so in the event our accounting server goes down completely we can be back in full production within 30 minutes and the max. amount of lost time is 15min. The data in Access can be 24 hours behind the live data and there is no harm. The reports we create from the Access copy of the data are of the 'here is what happened last week or last month' type.
I hate that it has to be so complicated, but a lot of this is created because the ProvideX ODBC DSN is to put it bluntly, crap. It is slow and sometimes will time out when going directly into Excel, especially when applying filters and criteria. We also have some tables that have more records than Excel can handle which when combined with filtered datasets timing out, we can't import into Excel anyway because unfiltered the data 'breaks' Excel. The ODBC DSN also doesn't accept the full set of SQL commands (This is more likely the ProvideX DB and not the DSN) , so some of the data requests I have coming from our intranet web server won't execute.