Large Data Table Locking up....

[GComyn]

hello...

I've been trying to append a table with 6,000 records to a table with 180,000, and I have it set to append only 100 accounts that are not already in the large table (the large table also had primary keys to stop duplication).

Now, I can run it the first time, but after that, I keep getting this error:

Couldn't read the record; currently locked by another user.

I'm the only one using the large table. I have the record locks set to 'All Records' in the append query (I did have it 'Edited Records' but got the same thing).

I'm at a loss to figure this out.

And before anyone suggests porting to SQL Server, I'm trying, but you get a large Corporation to buy something that is 'not absolutely necessary', and see where you get.... (unless you have some ideas as to how to propose this change... I'm not very good at proposals...)

If anyone could help me with this matter...

BTW... I'm using Microsoft Access 97.

GComyn

 

[ToeShot]

I will assume you are doing this in code of some sort. So I am going to ask the Obvious questions 1)after run the querydo you update the table in code? 2) do you release the recordset? Of course if you ar not doing this in code then these questions are of no use.

 

[ssa]

I saw this same fault 3 weeks ago,
There is an error in that you are appending from, You will have to manually look for these errors delete that record, I found repairing the database did not fix the problem.
Sorry

 

[GComyn]

Ok... I"m apending from a query... there is no code involved at all...

The table I'm appending from is correct, I added the same keys to it that the table I'm appending to has, and got no error....

As I said before, the first time I run the query after opening the database it runs ok (with only 100 records appending, because that is what I set it to do), but the second time and all times after that, it locks on me.

GComyn

 

[GComyn]

Ok... here is how I got around it.....

I created another table by using a make table query that was the table I am appending from linked to the table I'm appending to, one to many, putting all records that were in the 'from' table but not in the 'to' table into the new table.

Then I renamed the old 'from' table, changed the name of the just created table tot he same as the 'from' table was, and ran the query, and as long as I didn't do too many records at a time (I was limited to 175 at a time) it worked for the entire rest of the records (about 3000).

I don't know why this worked, but it did.....

Gcomyn