Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cire
Starting Member
15 Posts |
 Posted - 2006-04-10 : 06:45:21
|
| Hi all, i've developed a frontend query system using access for my users. This 3rd party system is more of a search/sort system, i.e. its read only and able to export to excel but its does not allow updates to server data, the server is based on a Ms-SQL backend, call this system AThe backend is actually a main server for the web application(System B) which my users use to update data. They update various stuff, this software serves more of a call centre kind of software where they see tickets assigned to them, the problem, the requestor etc etc and then they proceed to resolve the problem. however this software's reporting system isn't good enough and thus i had to develope a separate solution as menitoned in the 1st paragraph.Thats for the background now for the problem:If 1 user is updating a ticket through system B and ano user uses system A to query the server, an error occurs, i believe its because there is a conflict since one is trying to update and the other trying to retrieve, and the system isn't fast enough to detect that right? If so other than telling my users not to do both things at one time, are there other solutions? would the problem be access or another frontend software would result in the same problem? and if this in the wrong forumn could some admin move it to the correct one.ThanksEricThanks |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-04-10 : 09:51:49
|
| The problem with using MS Access for Enterprise applications is that the MS Access application model customarily involves loading a large recordset and then allowing the user to step through the records and modify them one-at-a-time. Of course, either the underlying dataset needs to be locked during editing or you have to resolve editing conflicts between users. An Enterprise application customarily loads and edits one record at a time through server-based stored procedures, thus minimizing locks and contention.If your application involves more than a half-dozen simultaneous users, then the traditional MS Access interface model is probably not appropriate. It is possible to design your Access interface to interact with store procedures rather than tables and views, but this involves sacrificing some of the programming convenience that makes Access and attractive development platform. So, at that point you need to consider whether you would be better off developing your application interface with a more robust programming language. |
 |
|
|
cire
Starting Member
15 Posts |
Posted - 2006-04-10 : 21:13:35
|
| Hi Blindman,firstly tks for your response, secondly regarding system B, its an application that developed by the company that provided the entire suite of reporting software, it was used primarily for users to create tickets(called requests) and for support side people to monitor and respond to these and thus did not include a comprehensive search feature which is why system A was developed.In addition i've designed system A(access.mdb) to pull data from the server-read only via pass-through queries, these data is then dumped onto an access table where the users will view and subsequently export it to excel, thus the tables on the backend DB are not modified nor updated in this process. I believe that system B developed by the company that supplied it, controlled the way the database was accessed, probably with some form of locks on it in that it doesnt allow a 3rd party system to retrieve data at the same time some1 else is updating on the primary application.On the other hand, i'm not too sure if my database adminstrator has set some form of control over the tables, this i probably have to check with him. In conclusion i don't believe using another program would actually solve the problem as it doesnt resolve the conflict of updating with the original applicaiton and retrieving data with a 3rd party application. But i'm open to other possibilities, more feedback and possible solutions welcome.ThanksEricThanks |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-04-11 : 08:47:34
|
| So does the conflict occur when trying to read from system B, or when trying to read copied data in system A?No way would I expect you to get locking or contention errors if you are simply doing read-only selects from the SQL Server tables.What is the exact error message your receive? |
|
|
|
cire
Starting Member
15 Posts |
Posted - 2006-04-11 : 22:16:26
|
| the pop up was my error handler's msg, when i clicked debug, it pointed to my docmd.openquery line but there wouldnt be any problem with my sqlstring as i've tested it without anyone trying to modify the data in system B which means the sql statement has no errors and data was retrieved.From what i know, any query run will actually retrieve the data from its previous state, i.e. if someone is updating data in system B, the values for that ticket is locked and any read-only select statements takes the previous state of that ticket, i.e. before the user enters the updating data window. apparently i've narrowed down the error to occuring when 2 operations are done simultaneously, i.e. when 1 person clicks "run query" in system A and ano person clicks "save" in system B. From what i understand is that perhaps as the server updates the data in the database, the query is being executed at the same time so the error arises. My adminstrator says if we use a separate instance of the database solely for the system A, it would probably solve the problem as that instance is delayed by half an hour behind the primary instance, and system B is being linked only to primary instance so the conflict shouldnt arise. More testing has to be done to verify this. any feedback?thanksEricThanks |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-04-12 : 00:12:11
|
quote:
Originally posted by cire...the pop up was my error handler's msg
So, what was the original error message/code?quote:
Originally posted by cireMy adminstrator says if we use a separate instance of the database solely for the system A, it would probably solve the problem as that instance is delayed by half an hour behind the primary instance, and system B is being linked only to primary instance so the conflict shouldnt arise.
Should work, but should by no means be necessary.How many records are you returning with your OPENQUERY() command?How many records are in the underlying dataset? |
|
|
|
|
|
|
|
|
can this error be avoided?