VB6, ADO to acess DB occasional locking problem

Hi,

I am getting a seemingly random locking problem on several of my applications when adding rows to an access database table from VB6 using ADO.

Below is an example of what I am doing, but it is basically looping through an array of data and adding rows to a table using a recordset.

The error is '-2147217887: could not update; currently locked' and I've had it happen on my development machine with no one else accessing the database.

I've tried different combinations of lock type and cursor type with no luck.

Dim rsData As New Recordset
Dim x As Integer


sProvider = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & sDBPath

rsData.Open "stock", sProvider, adOpenDynamic, adLockPessimistic

For x = 1 To xaData.Count(1)

rsData.AddNew
rsData!item_code = sData(x,1)
rsData!description = sData(x,2)
rsData.Update

Next

Is there a better way to add the data?

Cheers
Marc

You could try using an Insert SQL statement instead. See the Database FAQ (link below) for a variety of methods to add data to a table.

Quote:

---

Originally Posted by si_the_geek

You could try using an Insert SQL statement instead. See the Database FAQ (link below) for a variety of methods to add data to a table.

---

I used to use inserts, but with large amounts of data it was incredibly slow.

In that case try using a command object (also in that FAQ thread), as it is much quicker than using a "plain" Insert for multiple rows of data.

As you have tried all the cursor/lock types I see no way of "fixing" the recordset version.

Quote:

---

Originally Posted by si_the_geek

In that case try using a command object (also in that FAQ thread), as it is much quicker than using a "plain" Insert for multiple rows of data.

As you have tried all the cursor/lock types I see no way of "fixing" the recordset version.

---

Okay, will try that. Thanks.

It doesn't make any sense, and it is something I've been doing for ages. So why I'm occasionally seeing it now, I have no idea...

I see.. in that case have you tried running a "Compact & Repair" on the database (from within Access)?

Unfortunately Access databases get corrupted over time, and that is the way to fix them.

Quote:

---

Originally Posted by si_the_geek

I see.. in that case have you tried running a "Compact & Repair" on the database (from within Access)?

Unfortunately Access databases get corrupted over time, and that is the way to fix them.

---

I know all about the joys of access!

These are new projects with fresh databases, so it isn't that.

And my programs all now tend to backup and force a compact and repair regularly. It's the only way to be sure.

Thanks for the suggestions.

Cheers
Marc