Can I remotely connect to a MS Access DB that is all linked tables (and queries that use the linked tables)? The last time I used Access it was version 2007, from there I converted all our DB's to SQL so I am very rusty.
I am struggling to find a way to share data from our accounting system's ProvideX database ever since we upgraded to the latest version of the accounting software. If anyone has used the ProvideX ODBC driver you will understand the pain. In previous versions of the accounting package I would do a nightly import of the ~30 tables we run custom reports/feed our intranet site, they were imported into SQL2000 via Agent jobs. The current version is not compatible with SQL2000 (I am oversimplifying when I say not compatible). I need a new easily accessible DB. I tried SQL2017 & 2005, neither would work because of data import errors with some of the tables. So I thought Access linked tables, sure they would be slower (at least the queries will be) but on the upside the data is always 'fresh' when users look at it. Here is the setup:
- Accounting Server with ProvideX database
- MS Access running on a Win10 pro workstation. The Access linked tables talk to the ProvideX ODBC datasource (called: MAS_Silent). This all works, I can see the tables, I wrote a few queries, etc. I also saved the password in each table thinking that the link would be persistent.
- File server running on Win server 2012. This is where the Access DB file is stored. I have also tried by keeping the DB file on the workstation running Access.
- random users workstation, running either Win7 or Win10 Pro (OS makes no difference). The users do not have Access installed. On the users machine I have a MSAccess ODBC connection (Called: MAS2017_AccessDB) setup that talks to the linked DB above. This partially works, in Excel for example, I can pull up the MAS2017_AccessDB ODBC connection, it sees all the linked tables and queries, I pick what I want, but as soon as I hit finish and the data should be returned to Excel it fails with the error: "[Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'MAS_Silent' failed.
So as a test I installed the MAS_Silent ODBC DSN on my workstation and tested with Excel and it worked. The problem is that I can't install the ODBC DSN on each users workstation for security reasons. There is only one account in the accounting system that has remote DB access and with it you can access every table in the ProvideX DB.
My thinking was that the linked tables would always be persistent since I saved the password. Is there any way to talk to the Access DB from a remote machine without having the MAS_Silent ODBC DSN on the user's computer.
I have a feeling the answer is no since the remote ODBC DSN talks directly to the DB file itself and not to a DB server.
God how I hate the ProvideX DB, I think it is as close to Canada declaring war on the the rest of the as they can possibly do.
DK