Results 1 to 4 of 4
  1. #1
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065

    SQL Server Views

    I have 3 SQL Server Databases in 3 Facilities. What I want to do is create a view in our Local SQL Server that Draws data from the other 2. Which I've done but I'm not sure I'm doing it correctly. What I have is a job that runs in the morning prior to people coming in that runs a query to create the data locally for the users to access. the queries take a few hours to run and the data is static for the day. I have a new request that needs the same type of data but it must be dynamic. My question is if I create a view that uses the off-site SQL Servers is the data continually updated during the day? Also will I have issues with the timing for that data. (If a user updates the data in the vendor supplied front end how long will it be until the data shows up in my View?

    Any help is 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
    If the view is using linked servers, then it's data would be dynamic. As soon as the front end updates the data, the view should see it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    Do you have:
    3 different databases on same SQL Server instance;
    or 3 databases on 3 different physical SQL Servers;
    or 3 databases on 3 different SQL Server instances on same physical server (last 2 setups really don't differ too much for your task)?

  4. #4
    turbofish is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    25
    Since the query takes hours, your answer isn't going to be a view but more along the lines of replication that will keep the data synced between the servers. Merge replication would be the best in your case of semi disconnected data since it would catch up on intervals of your choosing. I would also suspect that there needs to be some kind of query/database performance tuning as well. We have databases on multiple servers that would total around 1 TB and nothing will take nearly that long.

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

Similar Threads

  1. User views?
    By Bigz132 in forum Access
    Replies: 1
    Last Post: 11-29-2014, 12:01 PM
  2. SQL Server Views documentation
    By RayMilhon in forum SQL Server
    Replies: 0
    Last Post: 09-04-2013, 11:35 AM
  3. Including Quotation Marks in SQL Server Views
    By EddieN1 in forum SQL Server
    Replies: 0
    Last Post: 02-17-2013, 07:32 PM
  4. Updating SQL Server Views via MS Access front-end?
    By tzvi in forum Import/Export Data
    Replies: 1
    Last Post: 01-10-2013, 11:13 AM
  5. Replies: 6
    Last Post: 05-14-2012, 08:32 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