1
Hi guys,
I've been stuck on this problem for days now at work and its driving me insane. Basically there is a system in place at multiple locations that is an open source project running with ms access. This system holds customer records and order data, and its written in asp.
We have a web service that sits at each of these locations written in vb.net and it is called by our system, on one of our servers. The web service is used online, customers create orders, most of this data gets stored in our sql server database (which omg i wish they would use instead of access... ), and then gets sent over the web service. I open one connection to the database for the duration of the call, which inserts multiple rows into two tables.
At the same time, there is an interface running on the local sites, which refreshes every 10 seconds or so. I'm not entirely sure what happens in this code, because of the poor coding and many include files, but I can gather that it is at least doing a read of the database, and there is possibly and update being made too.
It seems as though if that interface is refreshing at the time of the order being placed with the web service, I will get a database lock error through the web service.
Is it possible in access to specify NOLOCK, or only row locks for updates? I Thought that access allows a number of connections to the database before it starts locking like this. I have that default lock setting in access to be 'no locks', i've, I found online some extra properties to add to the connection string in the web service that allow shared mode and no locking.
In asp with a record set, can i stop a table being locked with select and/or update statements? I feel like i've made a number of changes to all sides of things and nothing helps.
I've also been told they dont ever encounter problems instore, when staff create new orders locally. Multiple members can create orders at the same time too. This makes me feel as though its a problem with the web service, however I never receive the locking issue unless I have that interface running too.
I know its a fairly long post and may not make a whole lot of sense, but if you have any questions/answers for me, ill give you a clearer understanding.
Thanks guys!
