Delete Query takes more than 1 hour to execute

[Guest]

Hi

Recently we have migrated an MS Application to Citrix Environment.

Following are few lines of code:-
'Begin code
DoCmd.DeleteObject acTable, "tblReferenceBusinessUnitRegion"

DoCmd.TransferDatabase acLink, "Microsoft Access", strRemoteUpdate & "\" &
gblApplicationName & "Data.mdb", acTable, "tblReferenceBusinessUnit",
"tblReferenceBusinessUnitRegion"

DoCmd.RunSQL "DELETE tblReferenceBusinessUnitRegion.* FROM
tblReferenceBusinessUnitRegion WHERE
tblReferenceBusinessUnitRegion.BusinessUnitID <> " & gblBusinessUnitID & ";"

'End Code

this execution of the delete query is taking an hour to complete. Even
though the linked table tblReferenceBusinessUnitRegion is having 10 rows in
the table.
The most weird problem is that this behavior does not occur on local
environment.
Is there any know issue on Citrix environment with MS Acces?
Please help! its really very urgent...
thanks
ritesh

 

[Guest]

How are you executing this? From a form. I so are you using maybe a
progressbar to show progress. If you do use some sort of indicator on the
form disable it and try again.

 

[Guest]

Hi Maurice

We are using a timer, this piece of code gets executed as part of the
evening refresh process.
Also we are not using any kind of progress indicator.
Weird part is that this problem occurs only when we run it on Cirix
environment.

Please note: before we run this delete query we are using filecopy command
to copy the database and then delete the linked table and relink it.
can this cause any issue?
Thanks
Ritesh

 

[Guest]

Hi Maurice
we had put msgbox and now we have replaced them but that still it takes 1 hr
time (
any other pointers...
ritesh

 

[Albert D. Kallal]

Please note: before we run this delete query we are using filecopy command
to copy the database and then delete the linked table and relink it.
can this cause any issue?
Thanks
Ritesh

Well, does the linking take a huge time??? If you using a split database,
keep in mind you want to have a persistent connection between the front end
and the back end.....

Also, do note that even in a citrix/ terminal server environment you still
do NOT want to allow more then one user into the same front end....

Anyway, the 1st thing I would try is using a persistent connection (and, I
also assuming your front end is a mde).

A persistent connection simply means that the front end opens up a table in
the back end..and keeps it open at all times, and then you run your update
process. This "open" table does not need to be involved, or related to any
code that updates any other table....but, just force a connection to be open
at all times...

So, presumable, this "persisting" connection would be done after you link
the 1st table (do that, so additional linking will occur at high speed).

keep that persisting connecting.and *then* try running your code....

And, the 'list' of things to check is here:
http://www.granite.ab.ca/access/performancefaq.htm

 

[Guest]

Please note: before we run this delete query we are using

filecopy command can this cause any issue?

Yes it can, but not your problem is really unusual, I
can't blame it on anything because I haven't seen it before.

Network file copy often uses BITS, the Windows
Background Intelligent Transfer Service. If you look at
running services on your server you will see it listed.

BITS happens in the background. You think you have
copied the file, It looks like you have copied the file,
but Windows is just pretending to have copied the file,
actually the file copy is happening in the background,
and won't complete until later.

Normally, more noticeable when you are using Windows
explorer to copy gigabytes of data between two other
computers.

But I don't know that is your problem.

(david)

 

[David W. Fenton]

A persistent connection simply means that the front end opens up a
table in the back end..and keeps it open at all times, and then
you run your update process.

I think it's unwise to define this in this way. The reason a
"persistent connection" makes a difference is because it keeps the
LDB file there, instead of it being deleted and recreated each time
a connection is closed and open.

And that can be accomplished with nothing more than an initialized
database variable pointing to the back end -- no need to open a
recordset on a table.

 

[Albert D. Kallal]

I think it's unwise to define this in this way. The reason a
"persistent connection" makes a difference is because it keeps the
LDB file there, instead of it being deleted and recreated each time
a connection is closed and open.

And that can be accomplished with nothing more than an initialized
database variable pointing to the back end -- no need to open a
recordset on a table.

A few regulars here (including you) have got me on to that you can simply
open the database, and not have to use a table. I not only think that is a
suggestion, but is a *better* suggestion then picking a table out of the
blue...

Out of habit, I tend to suggest opening a table, but really, a db open is
better choice here...

 

[David W. Fenton]

A few regulars here (including you) have got me on to that you can
simply open the database, and not have to use a table. I not only
think that is a suggestion, but is a *better* suggestion then
picking a table out of the blue...

Well, it is *harder*, as you have to check the connect string,
whereas with a table you can just open a recordset on the linked
table within the front end.

Out of habit, I tend to suggest opening a table, but really, a db
open is better choice here...

I think so, but others disagree. I would think a table would consume
more resources, but probably not enough to matter, beyond the
esthetics of the matter.