SQL Crashing

[andrew_sampson]

I'm runnning a SQL statement that inserts data from one linked table in one database into another linked table in a seperate database.

All three databases (the two linked and the main one running the SQL) are located on my desktop.

Occassionally, the SQL appears to hang, my CPU usage from MSACCESS jumps to around 60% and when I try and break to VBA, I get the yellow highlight over the SQL but cannot F5, F8 or stop the code......the only option I have is to kill MSACCESS and restart the code.

Has anyone else had this problem and is there a way to resolve it?

Ta

 

[Norie]

Are you trying to do this in one query?

How are the 2 tables linked?

Is there a 'local' table involved anywhere?

 

[andrew_sampson]

The SQL is running as part of a DoCmd.RunSQL action and it is only one SQL Insert.

The tables are linked by common fields (Month in MMM-YY format and ID) that are of the same type (Text and Long Integers) and there is no 'local' table. It is purely a manipulation of data from one linked table to another.

 

[Norie]

Well I can only think it's some problem with the linked table(s) and whatever database(s) they are in, perhaps something that's intermittent.

What I was going to suggest was instead of doing everything in one go/query try doing it in stages.

eg get data from one table, store that locally eg temporary table, and then update the other table from the stored data

So you wouldn't be accessing the 2 linked tables/databases at the same time.

 

[andrew_sampson]

I appreciate the help.

Have considered pulling the data out into local table/variables before inserting into the final linked table but I'm concerned that it would slow down processing, which is vital in this process.

The reason for everything being on the desktop was that it's so much quicker to process there than on a network......again showing how important processing speed is in this process

 

[Norie]

I don't quite follow, I thought you were dealing with remote databases on a network,

That's why I assumed there could be a problem linking to the 2 databases simultaneously.

As for slowing down the process, what data manipulation are you doing exactly?

Also, how are you linking the tables by ID and date?

PS What variables?

 

[xenou]

Have considered pulling the data out into local table/variables before inserting into the final linked table but I'm concerned that it would slow down processing, which is vital in this process.

The reason for everything being on the desktop was that it's so much quicker to process there than on a network......again showing how important processing speed is in this process

How much processing exactly are we talking about here?