Results 1 to 4 of 4
  1. #1
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451

    Need advice on second DB linking

    Greetings, i have two database's, one for production tracking and one for maintenance request and tracking. I have been requested to add a maintenance request form to my production database so that the operators can enter maintenance request for their equipment. This would require one table from the maintenance database being linked to the operators production database FE but i already have some issues with my old network so I'm leery of this having never done it. Will a FE see any difference in performance being linked to two BE's rather than one? My thought if it was possible would be to continue being linked to one BE for production and use an unbound entry form for the maintenance request that upon submitting the request would link to the second database, save the record and then close the link. I know i can do the first two parts, linking and saving but closing the link so that it does not continue to affect performance I'm not familiar with.



    Any thoughts, ideals, direction and examples would be appreciated. Thanks

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,863
    What I do is create a table in the FE that lists all of the Table names and their respective DB name.

    With this, I can programmatically recreate all of the linked tables. Now, to your question. What you want to make sure and do is to maintain and persist a connection to the BE files from the FE. You want to do this when the User is working on and connecting to via a LAN. I will use a Main Menu form that is bound to a small table in the backend. This form will remain open for the entire time the FE file is open. In the case where a user may work with one of the BE files once or twice a day, you may want to persist a connection for those occasions, only.

    When you are developing and making changes to an FE, work by connecting to a local copy of the BE files. This is where the list of table names come in handy.

    What all of this is doing is helping with the performance while the Users interact and you develop.

    Here is a small portion of code I use to reestablish a connection. When connecting to SQL server, it would be a little different.
    https://www.accessforums.net/showthr...003#post331003

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,912
    It is just my opinion because I have not done it yet, but I can think of no reason performance should be impacted because you are linked to two back ends. Actually I have to take that back, I create a separate db for temp tables to avoid any bloat and just kill the temp db when I'm done.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  4. #4
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    Thanks guys, I'll start playing with these ideals and see what I can come up with. If you think of more direction please let me know.

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

Similar Threads

  1. Some Advice
    By Race-winners in forum Sample Databases
    Replies: 1
    Last Post: 12-23-2015, 12:38 PM
  2. New-and need help as well as advice
    By bjd1020 in forum Access
    Replies: 8
    Last Post: 11-16-2012, 02:25 PM
  3. Need some advice and help
    By winterh in forum Access
    Replies: 9
    Last Post: 04-18-2012, 06:41 AM
  4. Replies: 4
    Last Post: 03-31-2012, 10:52 AM
  5. Replies: 0
    Last Post: 03-04-2011, 10:28 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 - Senior Forums