Is this possible using linked tables?

[dkone]

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.

[isladogs]

I never suggested letting users in the plural run the update whether experienced or otherwise.
Read post 9 again.
The whole process is setup on one machine using a separate application which runs automatically using task scheduler.

I've been using this setup with several applications and with multiple clients each night for over 15 years.
It works perfectly.

[Gicu]

I don't think he needs to run an update like you describe in the post 9 (importing CSV files, etc.), he simply needs to find an easy way to provide canned data to various users to run reports. If you "import" the source tables into an empty Access database you end-up with them being linked and dependent on the DSN source or connection string used to link (which involves having the right drivers installed on each machine and so on). My solution simply takes all that away by providing the users with a simple Access file where all the tables are local and they have the latest data as of the last run of FUZE (I had clients running FUZE every 30 minutes). This eliminates the possibility of messing with the live data (not in this case as the links seem to be read-only), it is portable and for reporting purposes is the fastest as you don't have to worry about network speed or connectivity.

I was using your method for many years also and I know it works well (funny enough I used to be one of the main programmers for a Canadian company that was doing Access databases for language schools, school districts, colleges and universities in Canada, quite similar I assume with your type of work), lately I started to use SQL Agent a bit more as there was pressure from clients to move to SQL back-ends.

Cheers,
Vlad

[dkone]

dkone,

in your latest post you are asking if there is a way to schedule Access to import all tables at a certain time. Yes, there is. Please feel free to download my free FBA_FUZE utility from forestbyte.com (first one on the MS Access Utilities page). All you need to do is to set it up to point to your Access database that has the links to the ProvideX db. Then click the Autorun check-box and close it. Now create a new Windows Scheduler task and schedule FBA_FUZE to start at your desired time. It will import all the linked tables in a new Access file, open that file and close itself. Right now the resultant file is left open (it was intended to create a merged all-in-one front-end), but we can easily modify that if you wish.

Cheers,
Vlad

Can I contact you via the email address on your website contact page?

[Gicu]

sure, please let me know if you need help with any of the FUZE features.

Cheers,
Vlad