Results 1 to 8 of 8
  1. #1
    Mclaren is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2010
    Location
    Johannesburg
    Posts
    164

    Split DB over slow network

    Hi all,



    So i have a split database. BE is on a server, which is in our offices. our Warehouse (20km away) also needs update records, but the connection to the server is somewhat slow.

    My idea is to create records in temp tables on the FE, and after each transaction, update those records to the BE. Two tables in total. tbl_Returns and tbl_Returned_Items. Then once the tables are updated, the records are deleted from the front end.

    Problem is: I have to update the tbl_Returns table (the one side of a relationship), then update the tbl_Returned_Items table (the many side of the relationship). I have created an append table which runs just fine for the first table, but the records in the BE get new primary keys. I am not sure how to link the tbl_Returned_Items in the BE.

    First off - is this a good approach, if not, what other options do i have ?
    Second - if i continue heading down this path, how do i return the Primary key of the newly updated records to the FE so i can then run an append query to update the tbl_Returned_Items on the BE ?

    Any help will be greatly appreciated.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    One option is the warehouse remoting into a computer in the office.

    If you head down the current path, note how Allen gets the primary key via bookmark:

    http://allenbrowne.com/ser-57.html
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Mclaren is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2010
    Location
    Johannesburg
    Posts
    164
    Quote Originally Posted by pbaldy View Post
    One option is the warehouse remoting into a computer in the office.
    Not an option, due to user rights and security. Will check out the article

    Is it an option commonly used ?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I haven't used that method, but I'm more likely to be using SQL Server. It's certainly viable for adding records. Lots of thoughts here:

    http://www.kallal.ca/wan/wans.html
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I take it back, I have used that type of methodology but it was not speed related. I needed to use an un-normalized front end table for the users to add data, then I used code so push that to properly normalized tables in the back end.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    turbofish is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    25
    Since remoting into the machine is not an option, this is one of those cases where you could use Access for the front end but you really need to use SQL server to take advantage of replication. In this case, merge replication would be the best especially when dealing with a disconnected model. That way if you have a slow connection or even if you loose your connection, as soon as a connection is reestablished, replication will get the data caught up without any real fear of corruption. Depending on an Access database file across a network that is 20 k away would guarantee a future bad day.
    I have used this method to keep 130 different remote locations up to date that were located in 11 different states. Heck, now even our database is located 120 miles away from our corporate office.

  7. #7
    Mclaren is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2010
    Location
    Johannesburg
    Posts
    164
    Quote Originally Posted by turbofish View Post
    Since remoting into the machine is not an option, this is one of those cases where you could use Access for the front end but you really need to use SQL server to take advantage of replication. In this case, merge replication would be the best especially when dealing with a disconnected model. That way if you have a slow connection or even if you loose your connection, as soon as a connection is reestablished, replication will get the data caught up without any real fear of corruption. Depending on an Access database file across a network that is 20 k away would guarantee a future bad day.
    I have used this method to keep 130 different remote locations up to date that were located in 11 different states. Heck, now even our database is located 120 miles away from our corporate office.
    I have 0 experience with SQL server......can i use SQL Express ?

  8. #8
    turbofish is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    25
    You can use SQL Express only for the subscriber, not the publisher so you would need to purchase one SQL server

    https://docs.microsoft.com/en-us/sql...ql-server-2017

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

Similar Threads

  1. SLow on a network
    By byterbit in forum Access
    Replies: 1
    Last Post: 10-09-2014, 11:02 AM
  2. Slow over network
    By cbende2 in forum Access
    Replies: 5
    Last Post: 07-31-2014, 01:09 PM
  3. Slow split database on network
    By riteoh in forum Access
    Replies: 9
    Last Post: 11-09-2012, 02:28 PM
  4. Slow Over Network
    By dnelson33 in forum Access
    Replies: 4
    Last Post: 02-07-2012, 10:34 AM
  5. Slow Processing over Network
    By weasel7711 in forum Access
    Replies: 2
    Last Post: 05-04-2011, 09: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