Problem with Deletes in code

[Guest]

I have a bit of code that is giving me problems of migraine proportion, and
hoping for some assistance here. I am runnning a procedure to update a table
with data gathered from an Excel file. This procedure loops through each
record from the external source, updates the table in the database, and sets
a flag to mark that the record was processed. After this is done, all records
that were not flagged are removed from the table. Each run is between 5 and
20 records, updating a table of 200+.

This is where the problem occurs. Anytime a record already exists in the
table, and is updated from the existing table, it gets deleted. If I step
through the code there are 0 problems - all existing records that are
supposed to stay are fine, all new record are fine, all existing records that
are supposed to go away are deleted. However, if I let the code run without
any breaks or manual stepping, then the unintended deletions start. I believe
it's a way I'm using the recordset that the table does not register the
changes before making the deletion, but I can't trace it down. Any help would
be appreciated.

The table that is being updated is set up as follows (shorthand)
ProjectNumber (Text(6), PrimaryKey)
Bunch of Data fields
Check (Integer)

The "Check" field is the flag to determine which records to delete after the
procedure executes.

The code that is executed is as follows (hybrid skeleton code and VBA, bear
with me, I'm in a rush):
Private Sub UpdateProjects (ByRef conDB As ADODB.Connection)
Dim rsProjects As ADODB.Recordset 'Recordset of all records in tblProjects
Dim rsSourceProjects As ADODB.Recordset 'Recordset of all records in
tblSource-ProjectList
Dim strProjectNumber as String
Import Excel file into tblSource-ProjectList

'Set the check flag of all records to 0
CurrentDB.Execute "Update tblProjects SET Check = 0", dbFailOnError

rsProjects.Open "tblProjects", conDB, adOpenKeyset, adLockOptimistic,
adCmdTableDirect
rsSourceProjects.Open "tblSource-ProjectList", conDB, adOpenStatic,
adLockReadOnly, adCmdTable

rsSourceProjects.MoveFirst
'All records in rsSourceProjects will be for the same project, so record it
for later use
strProjectNumber = rsSourceProjects("ProjectNumber")

For all records in rsSourceProjects
Search rsProjects for the current record of rsSourceProjects
If Not Found Then
rsProjects.AddNew
rsProjects("ProjectNumber") = strProjectNumber
End If
Update each field in rsProjects with data from rsSourceProjects
rsProjects("Check") = 1
rsProjects.Update
Next

rsProjects.Close
rsSourceProjects.Close

'The following command will delete all records in the Project table for the
project that was processed (tracked in strProjectNumber) that was not
processed (Check was left at 0)
CurrentDB.Execute "DELETE * FROM Projects WHERE ProjectNumber = """ &
strProjectNumber & """ AND Check = 0", dbFailOnError
End Sub

Again, there's more to it then just that, but short and sweet, that's what
I'm working with. I know there are different ways to do this procedure, but
this is what I am working with.

I believe the problem occurs when the Delete query is executed. The table,
for one reason or another, is not getting the updates within the loop. The
"Check" field is not being set to 1 for the records that exist in both
tblProjects and tblSource-ProjectList, and they get deleted. Is this a
problem with ADO interacting with the tables, or something more underlying?

 

[Crystal]

Hello Alyindar

After
rsProjects.Close
rsSourceProjects.Close

'release object variables
set rsProjects = nothing
set rsSourceProjects = nothing

'refresh changes made to tables by other processes
currentdb.tabledefs.refresh

'make changes show immediately
DoEvents

THEN do your delete query

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[Guest]

Thank you for the reply Crystal, I'll give the code a shot. So I understand
what is happening, why does the tabledefs collection need refreshed? I was
under the impression with the cmdtabledirect option should be referencing the
table directly within the database. Admittedly, the help contents on the
option was vague and barely listed, so misinterpretation would be very easy.

 

[RoyVidar]

Alyindar wrote in message

I have a bit of code that is giving me problems of migraine proportion, and
hoping for some assistance here. I am runnning a procedure to update a table
with data gathered from an Excel file. This procedure loops through each
record from the external source, updates the table in the database, and sets
a flag to mark that the record was processed. After this is done, all records
that were not flagged are removed from the table. Each run is between 5 and
20 records, updating a table of 200+.

This is where the problem occurs. Anytime a record already exists in the
table, and is updated from the existing table, it gets deleted. If I step
through the code there are 0 problems - all existing records that are
supposed to stay are fine, all new record are fine, all existing records that
are supposed to go away are deleted. However, if I let the code run without
any breaks or manual stepping, then the unintended deletions start. I believe
it's a way I'm using the recordset that the table does not register the
changes before making the deletion, but I can't trace it down. Any help would
be appreciated.

The table that is being updated is set up as follows (shorthand)
ProjectNumber (Text(6), PrimaryKey)
Bunch of Data fields
Check (Integer)

The "Check" field is the flag to determine which records to delete after the
procedure executes.

The code that is executed is as follows (hybrid skeleton code and VBA, bear
with me, I'm in a rush):
Private Sub UpdateProjects (ByRef conDB As ADODB.Connection)
Dim rsProjects As ADODB.Recordset 'Recordset of all records in tblProjects
Dim rsSourceProjects As ADODB.Recordset 'Recordset of all records in
tblSource-ProjectList
Dim strProjectNumber as String
Import Excel file into tblSource-ProjectList

'Set the check flag of all records to 0
CurrentDB.Execute "Update tblProjects SET Check = 0", dbFailOnError

rsProjects.Open "tblProjects", conDB, adOpenKeyset, adLockOptimistic,
adCmdTableDirect
rsSourceProjects.Open "tblSource-ProjectList", conDB, adOpenStatic,
adLockReadOnly, adCmdTable

rsSourceProjects.MoveFirst
'All records in rsSourceProjects will be for the same project, so record it
for later use
strProjectNumber = rsSourceProjects("ProjectNumber")

For all records in rsSourceProjects
Search rsProjects for the current record of rsSourceProjects
If Not Found Then
rsProjects.AddNew
rsProjects("ProjectNumber") = strProjectNumber
End If
Update each field in rsProjects with data from rsSourceProjects
rsProjects("Check") = 1
rsProjects.Update
Next

rsProjects.Close
rsSourceProjects.Close

'The following command will delete all records in the Project table for the
project that was processed (tracked in strProjectNumber) that was not
processed (Check was left at 0)
CurrentDB.Execute "DELETE * FROM Projects WHERE ProjectNumber = """ &
strProjectNumber & """ AND Check = 0", dbFailOnError
End Sub

Again, there's more to it then just that, but short and sweet, that's what
I'm working with. I know there are different ways to do this procedure, but
this is what I am working with.

I believe the problem occurs when the Delete query is executed. The table,
for one reason or another, is not getting the updates within the loop. The
"Check" field is not being set to 1 for the records that exist in both
tblProjects and tblSource-ProjectList, and they get deleted. Is this a
problem with ADO interacting with the tables, or something more underlying?

I think one possible cause stems from your usage of both ADO and DAO.

I think that if you'd done everything on the same connection (ADO), it
might perhaps work. I e, do your executes on the ADO connection
(conDB),
in stead of the "DAO"/Application.currentdb.

conDB.execute "DELETE FROM Projects WHERE ProjectNumber = '" & _
replace(strProjectNumber, "'", "''") & "' AND Check = 0",, _
adcmdtext+adexecutenorecords

Perhaps this explains a bit better
http://support.microsoft.com/kb/q200300/

Just a quick question/comment - your "air code" to me indicates it
could
perhaps be possible to do some more of this with action queries in
stead
of recordsets - if so, you might find out that's better;-)

 

[strive4peace]

Hi Alyindar,

refreshing the tabledefs ensures that changes made by other
processes will be seen

you said, "The table, for one reason or another, is not
getting the updates within the loop."

This could be why

and then, DoEvents makes the changes show up right away.

If this combination works, you can try removing one or the
other... it is always best to remove code that is not necessary.

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[Guest]

Thanks for the response Roy. While the code supplied by Crystal did not
correct the problem, using the ADO connection object to execute the delete
query did fix the problem with the test cases I have been using. In truth it
makes sense - I was mainly using the DAO CurrentDB object to avoid using
DoCmd.RunSQL to execute the delete query.

In regards to your comment about using action queries to perform this
function, that would certainly be my preference, all things being equal.
However, there is more going on in the actual procedure (such as compiling
metrics based on data in some of the fields being drawn in for later use),
and regretably without processing each item row-by-row this data is
overlooked. Thanks again for the suggestion!