By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,301 Members | 3,575 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,301 IT Pros & Developers. It's quick & easy.

Access locks and VB/VB.NET

P: 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!
Oct 29 '07 #1
Share this Question
Share on Google+
1 Reply


nico5038
Expert 2.5K+
P: 3,072
Access hasn't been designed for running on a webserver, so there's some extra risk in using it.
For some background info see:
Access database on the web:
http://members.optushome.com.au/anandasim/asp.htm#howdo
www.brinkster.com
Don't use Access on the web:
http://www.aspemporium.com/aspEmpori...seMSAccess.asp

For the locking problem I can add that when the Access database is opened by a user (or by code) to change the design, then even when the database has been declared "shared" it will get locked.

Nic;o)
Oct 30 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.