Results 1 to 9 of 9
  1. #1
    MrBologna is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    48

    Secondary ODBC connection: frontend or backend?

    Quick question about an ODBC Connection.



    I have a pretty standard split database, both MS Access files.

    I also have a few linked tables via ODBC connection.

    My question is, is it better practice to put the linked tables in the frontend, or the backend?

    Can the backend keep an open connection and allow frontends to query that info, or does it just make a copy of the linked table in the frontend and each user queries the connection separately?

    Some of my linked tables are pretty large, so the faster I can make the connection the better.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Only user specific tables should go in the fe. Not sure I understand the rest of the question(s) but ODBC linked tables are not copies of the source table in the sense that they are table defs in your database. They are just links.
    You can keep a connection open by opening a select query on the be tables, but you should only do it to one table. If your connected tables are very large, consider having native be tables that only contain what you need. You can do appends and updates to them on a regular basis; I used to do this nightly via Task Scheduler.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    My question is, is it better practice to put the linked tables in the frontend, or the backend?
    the front end

    Can the backend keep an open connection and allow frontends to query that info, or does it just make a copy of the linked table in the frontend and each user queries the connection separately?
    if you create a linked table in the BE then have the front end link to that, you will find the link is direct to the source, not via the back end
    Some of my linked tables are pretty large, so the faster I can make the connection the better.
    comes down to good form/query design and relevant indexing. Forms should not be based on tables and filters, instead use queries and criteria

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Just to clarify ...in case the last two posts seemed contradictory...which they aren't!

    The actual tables need to be in the backend database(s).
    The front end contains links to those backend tables which may be in one or more databases
    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

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Hmm, so what if the tables (or some of them as it seems to apply here) are ODBC linked tables? You'd put them in the back end or front? I'm saying be, Ajax is saying fe - or so it seems. So I'd have links to the ODBC tables in the be. If you look at the links to the be from within the fe, you don't notice the difference IIRC. It's the way I've always done it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    MrBologna is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    48
    The second section of Ajax's response answers my question I believe - putting the ODBC linked tables in the backend and then including them in the list when linking the BE tables to the frontend, will just create a copy of the ODBC connection string.
    At which point keeping the linked tables in the backend is a bit pointless, but not detrimental and can be useful to not have to re-do the ODBC connection info when re-linking or whatever.

    thanks guys, +rep all around!

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hang on. I think the message has got confused. Sorry if I contributed to that by attempting to clarify things.

    Connect your FE to tables in one or more BEs whether these are Access files, Excel, CSV or ODBC connections such as SQL Server

    You don't need to duplicate the tables in your ODBC connection by putting them in the BE as well.

    if it suits you to do the ODBC connection in the BE, that's fine...but if so just connect to the BE.

    PS Thanks in advance for any +rep points if you are giving them out....

    PPS Make sure you main a persistent connection to a small linked table to maximise performance
    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

  8. #8
    MrBologna is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    48
    The ODBC tables aren't duplicated in the backend - I'm referring specifically to the linked table in both places.
    If I understand correctly, including these linked tables when I am linking everything in the BE to the FE will just copy those ODBC connection strings to the FE, and the FE connection strings will point directly to the ODBC source; bypassing the BE.

    Keeping them in the BE would be theoretically pointless past initially creating the links.

  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
    I believe we are saying the same thing ...but in a different way!

    If you import tables from a backend, where those tables are linked tables in the backend, these will show as linked tables in the FE and display the original connection string.
    Try it and you'll see what I mean
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 06-25-2018, 04:15 PM
  2. Replies: 3
    Last Post: 04-25-2018, 10:32 AM
  3. Frontend to Backend Databases
    By raychow22 in forum Access
    Replies: 6
    Last Post: 08-11-2017, 04:47 PM
  4. Replies: 1
    Last Post: 03-02-2015, 09:44 AM
  5. Replies: 5
    Last Post: 10-25-2011, 08:01 AM

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