Concurrency - Access-SQL
-
I have SQL tables linked to an Access database, which I use as a front end. The user enters start and end dates, then a VBA module to run a pass-thru query that kicks off a stored procedure on the server. The stored procedure truncates a SQL Server table, then refreshes it with information for the user selected date range.My question is this: After running the process and opening the table on the server (Enterprise Manager) as well as the connected table in Access, I found that the data did not match. Refreshing the connections in Access (Linked Table Manager in Database Utilities) resolved the issue.Can anyone tell me why this is happening?.
-
You need to be careful how you pass the dates between the two databases. The integers representing a given date in one may represent a different date in the other. If you pass the dates as text and convert them back to dates in sql you should avoid this.
-
Yep. When I was teaching SQL classes I used to tell the class that one thing that irked me was that Enterprise Manager (EM) is not so good at refreshing. Over time I decided that it was designed to be that way for a reason. I now think it is the least intrusive and interruptive not to refresh all the time.
A Microsoft architect or developer would have had to decide how often EM refreshed. Since it is royally irritating and resource intensive to refresh at small time intervals (say 5 seconds), they opted not to do it at all, I think. EM must make calls to system tables to refresh its interface, and sometimes those calls are not as efficient as you would have written them. By running a trace you'll see that there are a lot of temp tables created - again no doubt by thoughtful design.
Having said that, one possibility would have been to have EM refresh upon certain events. In your case - the populating of a table - wouldn't be a great choice because in theory people would be entering data all the time. If EM were refreshing upon data entry, you'd never be able to see the interface - it'd be too busy loading.
Despite all this, I am aware of the problems of not refreshing. It can produce "ghosts", where it shows data that isn't there. For example a class got into an argument about retoring differential backups once, where one gentleman posited that restoring all differential backups (instead of just the most recent) is a bad thing because it can lead to extra data being persisted that was deleted at one point. We tried the experiment in class of taking several differentials after adding and then deleting some data in between diffs. As we restored them, EM doesn't refresh, so the data would appear to be there, but in reality it's not.
I think it was a thoughtful design choice.
Cheers.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply