SQL R22008 Vs MS Access Everyone Connected can not see changes

Posted on 2011-04-19
Medium Priority
Last Modified: 2012-05-11
I am new to SQL.  I importated my DB in MS SQL 2008 R2.  Everything went fine.  I am storing rows a data on a grid.  When a user clicks the grid it put the user name in a cell.  When the user moves off of the row the cell on the grid gets updated to blank.  All users that are coinnected to the Acess data base see the chantges without doing anything special.  But MS SQL 2008 R2 does not broadcast those changes.  Can anyone tell me why? I just do not get it.  I would think that is what you would want a data base to do.  Is show everyone the changes immedialty.

Question by:MKadric
  • 3
  • 2
LVL 28

Accepted Solution

Ryan McCauley earned 2000 total points
ID: 35429029
When you say it doesn't "broadcast changes", what do you mean? Who, specifically, is unable to view the changes? How is Access viewing the changes? Does Access contain linked tables to the SQL Server?

Author Comment

ID: 35429535
1) When I say broadcast changes. On the SQL server version the DB is updated but any client computer that is connected to the DB at the time of update those updates are not shown.,

2) ACCESS is the first veriosn and it works correctly.  

4) SQL is one version and MS ACCESS is another version..  Same code jus differenct connection strings.

5) Access is standalone.  

LVL 28

Expert Comment

by:Ryan McCauley
ID: 35429970
So the SQL database gets updated, but the clients with data on their screen don't get the updates? If they close their screen and re-open it, do they see the updated data then, or do they still show old data? It's possible that the update is working properly, but where Access might have somehow notified users that there was new data, SQL Server doesn't have that kind of functionality - all events are driven by client requests. I'm not sure, but Access forms may detect that the underlying data has changed and trigger a refresh.
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.


Author Comment

ID: 35431368
Correct (So the SQL database gets updated, but the clients with data on their screen don't get the updates)

If I do a requiry then the updates are there or if they re open applicaiton.  This is so totally unexceptable.  When I requery the screen flashes.  I have to remember what row they were on in the grid, requery and put them back on the same row.  

I do nothing special in MSAccess.  It just does it... Just like MSSQL Should.  
I would think that MSSQL is far more brighter than MSACCESS
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35432648
>>I do nothing special in MSAccess.  It just does it... Just like MSSQL Should. <<
That is a very bad assumption.  They are totally different animals, expecting SQL Server or Oracle or MySQL for that matter to work the same as a file based database that relies on client based resultsets is naive to say the least.

>>I would think that MSSQL is far more brighter than MSACCESS <<
Only if you develop taking advantage of its functionality, otherwise it is just as dumb as MS Access if not worse.
LVL 28

Expert Comment

by:Ryan McCauley
ID: 35433769
MSSQL and Access, though both with database functionality, are two completely different systems - Access is an application designed for viewing and manipulating data, and SQL Server is designed to store vast amounts of it. The "automatic refreshing" feature in Access doesn't exist in SQL Server because MSSQL just stores the data - the application you've written on top of it is what needs to refresh the data.

If you're using a datagrid and you need to refresh the whole thing, then you're at a loss - you'll have to remember which line they're on, refresh the grid, and then put them back.

Another option may be to fetch the data for their current line in the background, without refreshing the grid - compare it to what's in the grid currently, and if they're different, refresh the grid (or just update the row they're on).

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question