Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    dkone is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jan 2018
    Posts
    9

    Is this possible using linked tables?

    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

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Can I remotely connect to a MS Access DB that is all linked tables (and queries that use the linked tables)?
    Simple answer - YES
    1. You IMPORT the tables into your database - they will appear as linked tables
    2. You can either IMPORT the queries OR run them from the source database - specify this in the query properties

    However, not sure I fully understand the setup you are describing.
    I have no knowledge of ProvideX DBs so all the above may not work for that setup

    The other word that may be an issue is 'remote'
    If you just mean on the same network there should be no problem providing you can see the source database from each workstation
    You should only need to enter the password ONCE - when you link the tables
    If the connection isn't always available then you do have issues ....

    If your ODBC connection is an issue, you could use VBA code to link to the source database
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    dkone is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jan 2018
    Posts
    9
    You stressed the work IMPORT, so I assume that is different from linked tables. When I was going from ProvideX DB to SQL DB, I imported the tables, which for this application a once a day import is fine and I would run the import job every night which would drop and recreate each table. So is there a mechanism in Access that will allow me to import all the tables and do so automatically on a schedule, ie.. every night at 3 am run the imports.

    The queries I was planning on just re-writing each of them in Access since I can't find a way to easily use the SQL server SQL for the queries and bring it into Access. Access always seems to fail when I copy and paste the SQL statements, usually on the join statements.

    When I said remote, I did mean on the same local network, but different machines.

  4. #4
    dkone is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jan 2018
    Posts
    9
    Ridders52, I forgot to say it in my response, but thanks for your reply.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    When you connect to an external database, you have a choice of import tables or link tables
    If you select LINK, you will only see a list of LOCAL tables in the external database
    So instead you select IMPORT to see the full list of LOCAL & LINKED tables
    Confusingly the LINKED tables will not be imported - a new LINK will be created

    I really do NOT recommend repeatedly importing, deleting & reimporting tables.
    It will serve no purpose and cause database bloat.

    If the table names are unchanged, there is no need to drop & recreate the links UNLESS the source table structure has changed
    The linked table will always contain the latest data.
    Conversely if you IMPORT the tables, they become detached from the source & so do not stay up to date

    There is no need to copy & paste SQL statements.
    The import objects dialog box allows you to select ANY database object that isn't hidden
    OR as already stated you can run the query from the external database itself ... though if it's a regular event I wouldn't do that

    There should be no issues on the same network AS LONG as each computer running the Access FE db has the SAME PATH to the external database
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    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

  7. #7
    dkone is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jan 2018
    Posts
    9
    I understand what you are saying. The source ProvideX DB only has local tables, my Access DB has linked 'copies' of those tables. For Access with linked tables I had no intention of dropping/recreating the tables because they are linked. I only did the drop/recreate in SQL because there was no other loads on either the ProvideX or SQL DB and it was happening at 3 am. As far as the copy paste of the SQL statements, I only tried that when recreating the queries from SQL in the Access DB. Access must not support all SQL language, I don't know. I just know that on some of my SQL queries, Access would throw an error on the JOIN statements. I can't write queries in the ProvideX DB, all my queries reside in the SQL DB and must be recreated in the Access DB.

    The problem that I am running into is that the users machines do not have Access installed. On the users machines, they are using the MS Access ODBC DSN to talk to the Access DB sitting on a separate machine on the network. When the users machine attempts to read the data from the Access DB it fails because the Access DB needs to talk to the ProvideX DB via the ProvideX ODBC DSN that only the machine that has Access installed (also the machine where the Access DB is located).

    I think my terminology with Access has made this harder for me to communicate. In the Access world there is no DB server, but I have been referencing the machine that has Access installed as if it is a DB server.

  8. #8
    dkone is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jan 2018
    Posts
    9
    Vlad & Colin,

    I think between both of your replies I have a solution using Access. It seems as if linked tables will not work unless I either install Access on each users machine (bad idea, too many machines to support and license fees) or install the ProvideX ODBC DSN on each users machine (bad idea due to security). So imported tables is the way I have to go. There will be one machine that has Access installed, it will also be where the Access DB is saved. That machine will import the ProvideX tables each night using Vlad's utility. The users should then be able to see the tables via their locally installed Access ODBC DSN connection.

    Does this sound like it will work? We have no real load on the network overnight and when importing with SQL it would only take about 15 minutes to import the 30 or so tables. Nightly refreshes of the data are sufficient for our needs, so a live view of this data is not an issue.

    The real problem is that data structure in the ProvideX DB has some fields in a format the SQL 2005 and greater will not accept without me doing a bunch of Casts or bringing everything in as Varchar() fields. I need to be able to basically do a Select * when bringing in the tables. SQL 2000 and Access have no problem with bringing in the tables.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    First of all, you do not have 'copies' of linked tables - they ARE the tables

    Secondly you can install Access runtime version of each computer which doesn't have the full version. No licenses required & no cost.

    Thirdly, I still think you are overcomplicating things.
    I have a very similar scenario though in my case the BE file is in SQL Server

    A scheduled task is used to do the following:
    Each night at around midnight, the main database is locked so that it can be updated from external data.
    A separate app does the updating & the whole process is automated.
    This app runs from the server.
    First it makes a backup of the datafile
    It then imports over 30 CSV files from other sources to 'buffer' tables, processes them into a format that Access can use then runs code to append/update or delete records in approx 30 tables in the main databases
    The buffer tables are then emptied and the main database is unlocked.
    The whole process is logged & an email is sent to the program admin overnight so he/she can see at a glance whether any issues occurred e.g. due to power failure.

    This is an extract from the user guide explaining how it works

    Click image for larger version. 

Name:	Capture.PNG 
Views:	22 
Size:	25.2 KB 
ID:	32236

    The important thing that I will stress again is that the Access tables themselves are NEVER deleted & recreated.
    If that process fails after tables are deleted (e.g. due to network failure), you will soon realise why its a very bad idea

    HTH
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Not sure I follow you, why do you need to Cast if you import the tables in Access? Fuze should loop through all the linked tables in the "linked" Access file and import them one by one into a new one (so the new one will get overwritten every time you run Fuze).

    Cheers,
    Vlad

  11. #11
    dkone is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jan 2018
    Posts
    9
    I meant if I want to use SQL 2005 or greater I would have to 'route' around the malformed data types by using CAST or by setting to VarChar to get the data into SQL. I don't want to go that route because every time we upgrade our accounting software the vendor will randomly change field names, leaving my table imports as a moving target. So the simpler the better when it comes to the table import or linking.

    Access is awesome in how it imports or links tables. Linking is the best since it is 'point & click' simple. I haven't gotten to playing with table importing yet, but I imagine it will be much easier in Access than in SQL. The newer versions of SQL are a PITA, you have to set up a .Net for ODBC connection, then write a SQL import script, etc... (when using SQL the ProvideX ODBC connection won't show a list of tables, whereas in Access I can see the list of all the ProvideX tables.) What I liked the most about Access is that I can select all 30+ tables at one time whereas with SQL I have a connection and job for each table.

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I have just updated FBA_FUZE and added an option to leave the all-in-one file open; if left unchecked it will close the files (which is what I think you want if you use it with the Windows Scheduler).
    Vlad

  13. #13
    dkone is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jan 2018
    Posts
    9
    Thanks Vlad,

    I am looking forward to testing this with imported table and hope to get to it later today or tomorrow.

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    DKone
    I know I'm repeating myself but you do not need to delete files or tables which appears to be what the Fuze approach does from gicu's description.
    If anything goes wrong after the file or tables are deleted, all you can do is restore from a backup

    If you do regular backups, you may not lose much but the point I'm making is, this approach is both unnecessary and no easier to do.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Colin,

    I think the biggest advantage of using Fuze is that would isolate the "live" sensitive data from the rest of the users. I don't think is a good idea to give access to a group of (inexperienced) users to the live data (via the linked tables) as they could edit it, delete it or otherwise modified without the complex rules that are proabably implemented in the original accounting system.

    Just my two cents...

    Cheers,
    Vlad

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-07-2016, 02:41 PM
  2. Linked tables
    By elico in forum Access
    Replies: 4
    Last Post: 01-27-2016, 04:40 PM
  3. Linked tables
    By zodiba in forum Access
    Replies: 1
    Last Post: 01-26-2016, 12:26 PM
  4. Replies: 4
    Last Post: 11-22-2013, 11:20 AM
  5. Replies: 5
    Last Post: 02-02-2012, 06:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums