Delay with opened mdb file

[kyrkon]

Hi there,

I am a new here and for a long time i am trying to find out what is the problem on that scenario:

I have o FE/BE access 2003 environment with about 8 users that they use the application concurrently.

When they are in the system and i try to insert some date from a xls file into some tables i have very big delay, lets say about 30 minutes. If i ask them to close the application the above procedure takes about 3 minutes.

The xls file is sometimes 50,000 records or less but it does not make any difference how many records the xls file has.

Is there any problem with tables indexing, with my server witch has RAID 0 or something else.

My server is MS 2003 with 2 GB RAM and RAID 0 and my clients are with XP SP3.

Please tell all possible issues.

Regards,

Kyriakos

[SoftwareMatt]

Are you using code or a query?
If a query is it an update or append ?

[kyrkon]

Thanks for the reply.

I am using code like this:

DoCmd.SetWarnings False
DoCmd.TransferText acImportDelim, "spec1", "cus1", "c:\cus1.csv", False
DoCmd.TransferText acImportDelim, "spec2", "car1", "c:\car1.csv", False
DoCmd.RunMacro "Insert From cus_car"
...
...
...
...
DoCmd.RunMacro "Delete cus_car"
DoCmd.SetWarnings True



I have two xls files...
Firstly, i create the two tables cus1 and car1 in which the two xls files stores. Then i insert the new records of that two tables into two others with DoCmd.RunMacro "Insert From cus_car"
and then i have some SQL statements for updates some records by a FLAG field like this:

DoCmd.RunSQL "UPDATE Customer_Info INNER JOIN cus1 ON Customer_Info.Customer_ID=cus1.Customer_ID SET ........

Thanks again!!!!

[SoftwareMatt]

Do you know which part of your import routine is causing the delay i.e. is it importing the data or running the update queries or a bit of both?

You could add some break points inbetween sections of your code to see exactly how long each section takes.

[kyrkon]

Thanks for your reply, i will check it with break points and i will let you know.

I think that is possible to have some locks records when i try to insert or update my tables. Is this a problem or not?

Thanks

[SoftwareMatt]

If you are worried about the record locking then comment out the line 'DoCmd.SetWarnings False' and then if there are records that don't get updated it will warn you.

I don't think that recordlocking would affect the speed.