VB6, ADO to acess DB occasional locking problem

[uczmeg]

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

[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.

[uczmeg]

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.

[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.

[uczmeg]

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...

[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.

[uczmeg]

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