Deleting a record from a record set?

[andy.novak]

I have select a list of projects that are in the rs4 recordset via a
query against the Reporting DB connection. But before I process the
list (open each project, do stuff to it, save, and close), I want to
make sure none of the project names in the record set are checked out
(why attempt to open a project that's checked out and have the VBA
script croak?). What I think is the best approach is to first loop
through the record set and for each one, read the Draft database where
the Project Names are equal and the PROJ_CHECKOUTBY is NULL. If the
NEW record set has no records in it, then I'll delete the current
record in the former record set. Once all done, then I'll move to the
first record in the former record set and start processing.

What I'm thinking is that I need to do a "MovePrevious" after the
delete and before the next "MoveNext" - imagining that once the delete
happens, you are actually sitting on the next record in the set. I
hope this make sense. Heck, the rs4.Delete may not accomplish what I
think it does Any and all suggestions are very very welcome.
See below.

Thanks,
Andy Novak
UNT


Do Until rs4.EOF
rs5.Open "Select ProjectName FROM MSP_Projects WHERE
PROJ_CHECKOUTBY IS NULL AND ProjectName =" & rs4!ProjectName, Conn2
If rs5.RecordCount = 0 Then
rs4.Delete
rs4.MovePrevious
End If
rs4.MoveNext
Loop

rs4.MoveFirst
'Process the entire list now that the checked out project records are
no long there

 

[Rod Gill]

Delete the record in the recordset and you delete the data from the db as
well! If protection prevents this, the record won't be deleted and an error
is raised. Instead you need to link the Draft db Project table into your
query and filter on the PROJ_CHECKOUTBY field to exclude Not null values.
Now your recordset only includes checked in records

--

Rod Gill
Microsoft MVP for Project - http://www.project-systems.co.nz

Author of the only book on Project VBA, see: http://www.projectvbabook.com

I have select a list of projects that are in the rs4 recordset via a
query against the Reporting DB connection. But before I process the
list (open each project, do stuff to it, save, and close), I want to
make sure none of the project names in the record set are checked out
(why attempt to open a project that's checked out and have the VBA
script croak?). What I think is the best approach is to first loop
through the record set and for each one, read the Draft database where
the Project Names are equal and the PROJ_CHECKOUTBY is NULL. If the
NEW record set has no records in it, then I'll delete the current
record in the former record set. Once all done, then I'll move to the
first record in the former record set and start processing.

What I'm thinking is that I need to do a "MovePrevious" after the
delete and before the next "MoveNext" - imagining that once the delete
happens, you are actually sitting on the next record in the set. I
hope this make sense. Heck, the rs4.Delete may not accomplish what I
think it does Any and all suggestions are very very welcome.
See below.

Thanks,
Andy Novak
UNT


Do Until rs4.EOF
rs5.Open "Select ProjectName FROM MSP_Projects WHERE
PROJ_CHECKOUTBY IS NULL AND ProjectName =" & rs4!ProjectName, Conn2
If rs5.RecordCount = 0 Then
rs4.Delete
rs4.MovePrevious
End If
rs4.MoveNext
Loop

rs4.MoveFirst
'Process the entire list now that the checked out project records are
no long there

__________ Information from ESET Smart Security, version of virus
signature database 4937 (20100311) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4937 (20100311) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[andy.novak]

Delete the record in the recordset and you delete the data from the db as
well! If protection prevents this, the record won't be deleted and an error
is raised. Instead you need to link the Draft db Project table into your
query and filter on the PROJ_CHECKOUTBY field to exclude Not null values.
Now your recordset only includes checked in records

--

Rod Gill
Microsoft MVP for Project -http://www.project-systems.co.nz

Author of the only book on Project VBA, see:http://www.projectvbabook.com













__________ Information from ESET Smart Security, version of virus signature database 4937 (20100311) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Rod, I just learned that and luckily the embedded userid and password
I had was "read only" whew! I was thinking of the record set kind of
like an array which was incorrect.

Since the draft and reporting databases require two different
connections, how exactly would the rsx.open statement work - use to
Conn parameters at the end?

 

[Rod Gill]

The rsx.open statement needs to be passed a SQL statement that uses the
Project GUID to join the two tables. The best way to create the View is in
the SQL Management studio or get one of your SQL Server dba's to do it for
you.

--

Rod Gill
Microsoft MVP for Project - http://www.project-systems.co.nz

Author of the only book on Project VBA, see: http://www.projectvbabook.com

Rod, I just learned that and luckily the embedded userid and password
I had was "read only" whew! I was thinking of the record set kind of
like an array which was incorrect.

Since the draft and reporting databases require two different
connections, how exactly would the rsx.open statement work - use to
Conn parameters at the end?

__________ Information from ESET Smart Security, version of virus
signature database 4940 (20100312) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4940 (20100312) __________

The message was checked by ESET Smart Security.

http://www.eset.com