can anyone help with this error, i'm loading records from a .csv file into an .mdb, I get this error after about 3500 records.
seems to be completely random but always after 3500 records and i have 140000 to import.

runtime error 3260

could not update; currently locked by user 'admin' on machine 'xp2000'.

any help appreciated.

Chris

Assuming you're using Access (you haven't given us any clues :)) try opening the mdb in exclusive mode, and loading the csv then.

There are two secrets to success in this world:
1. Never tell everything you know

Yes, I would tend to agree with the last post. The error number has the flavor of DAO. I kind of miss those error numbers versus what we now get back from ADO. However, I have seen this error happen a time or two when testing my application and Access has the database open.

Do any other computers have access to your database?

Do you have the database open when executing your code?

Are you making any other calls to the database in your code?

Are you actually executing the update command at the point in time when the error occurs?

Can you give us an example of your code that is performing the update from the csv file?



Larry Asher

Hi, thanks for your help.
no other computers have access to the database.
the database is not open when running the code
no other calls are made to the database
yes it's the .update that causes the error
eg
With DatPrice.Recordset
    .AddNew
    !subkey1 = Spart
    !a2 = scost
    !Prefix = "C" ' cost price
    .Update
    .AddNew
    !subkey1 = Spart
    !subkey2 = "1" ' trade price
    !a1 = strade
    !Prefix = "S"
    .Update
End with

What I think is happening is the repeated calls to .AddNew and .Update are locking the records and not unlocking them fast enough. I recall reading something similar in a Microsoft Tech Note. The tech note I think was more related to opening and closing the database, but I wouldn't be surprised if it wasn't a similar issue especially since you call the .AddNew and .Update methods back to back in a loop containing aprox 140000 records. It looks to me like you are using DAO as a means of data access along with the data control. What I would recommend is using ADO or at the very least get away from the data control and write your code against a recordset object using a batch update method.

I know this can be done in DAO although I never used it when I was programming with DAO. In ADO you can create a disconnected recordset that works with your data in memory when you call the batch update method it will update your database with one call versus the repeated calls you are now making. I would suggest looking at ADO, Batch Updates, and Disconnected recordsets.

Question: Are you going to be importing csv files of this size routinely into your database or is this a one time effort to populate the database with legacy data?

Thoughts:
Routine: Calculate the size of the data for each record and how much memory each import will consume. Access database don't perform well past 100 MB. Especially if there is a lot of record manipulation like deletion of data. You will need to compact and repair the database often and even that can be time consuming with a large database.

One time effort: If you don't want to go to the trouble of changing your code break the file down into bite size parts. Or create an intentional delay between calling the .Update method I find most issue like that are resolved with about a three second delay, That should only take you around 116 hours to complete your import, probably not a good idea.

Let me know what you think I will try to help as I can.

Larry Asher