Runtime Table Deletes Problem

[Vambo]

Hi, I run an Access 2003 system where the reference data and user data are
kept in 2 separate DB's, the ref data is updated monthly and issued to the
users. The user db is updated with the new ref data by linking tables and
then running delete/update queries.

I have discovered this method has stopped working for some users lately,
the previous months data is left intact, the users don't get an error message
of any type, and I can't figure out why. (It also affects certain users when
importing data to a temporary table which should be cleared before each new
import).

It still works perfectly on my test machine (an XP Home machine with only a
Runtime version of 2003 installed).

The users are mostly on XP Pro but there are a couple of Windows 2000 users
(who don't have the problem).

I wondered if there had been an update recently that may be affecting this
operation.

Cheers

V..

 

[Lance]

Are warnings turned off and/or are error messages trapped without returning
any info on the user side?

 

[Vambo]

Hi Lance,

Errors are trapped with:

On Error GoTo ErrHandles
..
..
..
ErrHandles:
MsgBox "Error: " & Err.Number & vbCrLf & Err.Description

But there's no message appearing!

V..

 

[Lance]

What does the delete query look like? Is there any criteria involved?

 

[Vambo]

Lance, delete query is simply:

Field: RefData1.*
Table: RefData1
Delete: From

Which should (and still does for some) remove all data in the target table
(RefData1)

V..

 

[Vambo]

Lance, should have mentioned the query is called from code:

CurrentDb.Execute "ClearRefData1", dbFailOnError

V..

 

[Lance]

If you manually run the query, outside of the function.. does it work?

 

[Vambo]

Hi Lance,

The runtime system that the users have is totally menu driven so there is no
access (excuse the pun) to do this, it works perfectly if run manually from
the Dev install.

V..

 

[Lance]

heh, isn't that always the case. Pity users won't accept "it works on my
machine" as a valid solution

Have you tried running a "clean" copy of the dev install on the user, with
the problems, machine? Before you convert it to an MDE, or whatever, so you
can manually run queries if they're not working..

Also is there anything in the function which could be kicking you out before
it gets a chance to run the delete?

 

[Vambo]

You're right there. I installed a fresh copy of the master on a known
problem machine to no avail (I know it's not the users causing the problem as
it all happens while they stare at an intro screen).

This is literally the 1st thing that happens after the intro screen loads
using the On Open property of the form, so I can't see that anything else may
affect it.

I have a couple of weeks before I need to update the data yet so I'll keep
trawling, thanks for your interest.

If you can think of another way of doing it.........

V..

 

[Lance]

By master do you mean another MDE file, or your dev copy? I would put a copy
of my dev db on that computer so I could test the queries manually 1 at a
time in the environment where the problem is.

Could you post the code from the on load function as well as the SQL from
the delete query?

 

[John W. Vinson]

I have discovered this method has stopped working for some users lately,
the previous months data is left intact, the users don't get an error message
of any type, and I can't figure out why. (It also affects certain users when
importing data to a temporary table which should be cleared before each new
import).

I see you're covering some of the bases but... could you post the code? There
might be something non-obvious that another pair of eyes or two could spot.

John W. Vinson [MVP]