ADO vs DAO - worth converting ?

[Tony Epton]

Access 2003, SQL server 2000

I know I should walk a bit further down the corridor to .odbcclientsvr
but there seems to be a lot more activity in this room - so I hope no
one minds.

I'm a bit of a Luddite and I have used DAO since version 1.0

Recently a 3rd party did a review of one of my systems and suggested I
recode it to ADO and use a single connection object throughout the
program.

1) Would this be noticeably faster than my existing DAO (I believe
that all my existing table indices and query structures are already as
efficient as possible)

2) I am a bit reluctant to carry the connection around in a public
variable as a single untrapped bug in the program will generate an
avalanche of error 91's (users will always try to keep going after a
bug - all they report to me is the error 91's and not the situation
that precipitated the bug in the first place)
Any comments ?

Many thanks in advance

Tony Epton

 

[Albert D. Kallal]

Access 2003, SQL server 2000

Recently a 3rd party did a review of one of my systems and suggested I
recode it to ADO and use a single connection object throughout the
program.

I guess the question centers around is your performance OK at this point in
time? For your general record set coding that you have in your application,
using linked tables for the most part is not such a big deal, and as far as
I know SQL server will pool some of those connections for you automatically
anyway. It's not clear if you're suggesting to change all forms to using a
single connection, or just simple and general record set code that you have
(and presumably uses linked tables). There's really not a lot of benefit in
terms of performance here to do as such, the only exception is if you have
some really lousy joins and some to SQL that performs a bit slow (in this
case if the query is not a pass through, often dao will do a very poor job
if your query is using several linked tables).

1) Would this be noticeably faster than my existing DAO (I believe
that all my existing table indices and query structures are already as
efficient as possible)

For the most part the difference in performance between using dao (linked
tables), and creating a connection object and then executing SQL is not
gonna be a benefit all. Furthermore for any of the report's and certain
queries were you don't need update ability, simply converting the query in
the query builder tool to a pass through will yield the same performance as
ado anyway. As mentioned if you can make a query pass through, then using
ADO, or DAO is not gonna make hill of a beans a difference.

2) I am a bit reluctant to carry the connection around in a public
variable as a single untrapped bug in the program will generate an
avalanche of error 91's (users will always try to keep going after a
bug - all they report to me is the error 91's and not the situation
that precipitated the bug in the first place)

The issue of losing scope, or variables being lost to due to untrapped
errors is a completely different problem than that of the connection object.
However if you're like me, and for years and years always distributed a mde
to EACH the user's desktop. A mde is better becuase unhandled errors DO NOT
blow out local, or even global variables. This is one of many reasons why
it's a good suggestion to use a mde for you end users - mde's load faster ,
run faster, code can't be uncompiled, and furthermore you get the added
bonuses in that your variables NEVER get blasted out due to handled errors -
all local and global variables will always remain intact when you use
(distribute) a mde for you end users.

Furthermore you could create a global dao connection object and continue to
use dao throughout your application anyway. because SQL server will Poole
the connections for you automatically, I'm not really sure if there's a big
benefit here SIMPLY of having a goal to pooled connections. the real
benefits here are two Chinese pass through queries when you can, and really
ADO, or DAO is not gonna make any difference here. You'll have to look at
each query and recordset code on a case by case basis to make a decision as
there being any benefits in terms of performance to convert the query to a
pass through query.

You'll get far more benefits by converting the problematic slow running
queries to pass-through queries whereever you can , and this is going to be
a considerable amount of less work than that of simply just card blank
rewriting everything as ADO (and just blindly converting reocrdset code to
ADO will NOT yield performance benefits at all if the queries are running
fine right now.

Without question ADO is a cleaner object model then DAO. And, it works
somewhat better with SQL server (but not necessarily from a performance
point of view, it's just cleaner to work with with a SQL server). So, the
benefits and performance issues are not generally noticeable in practice at
all...

 

[Tony Epton]

A mde is better becuase unhandled errors DO NOT
blow out local, or even global variables.

I didn't know that - thanks

Furthermore you could create a global dao connection object and continue to
use dao throughout your application anyway. because SQL server will Poole
the connections for you automatically, I'm not really sure if there's a big
benefit here SIMPLY of having a goal to pooled connections. the real
benefits here are two Chinese pass through queries when you can, and really
ADO, or DAO is not gonna make any difference here. You'll have to look at
each query and recordset code on a case by case basis to make a decision as
there being any benefits in terms of performance to convert the query to a
pass through query.

I've reworked a few queries to make them pass thru, but everything
else seems pretty snappy.

Mind you - part of this exercise was also to move from an mdb backend
to SQL. Preliminary testing out at the site gave at least an 8 fold
improvement in speed with 5 concurrent users after moving the backend
to SQL - so anything will look fast to the users at the moment - I
just hope it scales up gracefully as more users are added.

I am also reworking the front end database to avoid any bound input
forms - so hopefully that will improve speed and reliability.

Many thanks for the advice
Tony

 

[david]

I'm just going to remind you -- because it bugs me so much --
that dao linked table transactions are broken against sql server
in Jet 4. Because DAO maintains a connection pool and in
Jet 4, the connections block each other.

(david)

 

[Albert D. Kallal]

I am also reworking the front end database to avoid any bound input
forms - so hopefully that will improve speed and reliability.

Actually you can will get away with bound forms directly to tables, even
when you use SQL server. the very simple trick here is to make sure your
designs minimized the records pulled into the form.

for example you'll often find a lot of MS access applications that simply
launch a form that's attached to a query or table. This for the most part is
a very bad design, because you know this form that can potentially pull
thousands of records into the form, and yet we not done ONE BIT of useful
work at all of that point in time.

The very simple solution is that bound forms will perform extremely well
with SQL server as long as you restrict the records loaded. In other words
never ever simply load up a form that is blindly attached to some large
table in SQL server, as that can be a potential disaster from a performance
point of view. (in fact by the way this design suggestion works equally well
when you're not even using SQL server,

Never simply load up a form attached to a large table, is not only bad
design, but it doesn't make sense to do as such. For example can you imagine
an instant teller machine that would download all of the accounts into the
teller machine, and then ask you to identify yourself?

So you can well get excellent performance in access with bound forms to the
table, or query, just ensure you have a "where" restriction in the where
clause of the openform command. You will find that only that ONE record will
be pulled into the form...

So, the very simple solution here is to simply ask the user before you
launch the form, and use the forms where clause to restrict the form to one
record.

I give a series of suggestions and screen shots here as to how you can
obtain this type of flow where you ask the end user for say a invoice
number, load the form....let the user edit and do his work, and then close
the form and you data is saved. So, using the where clause to open that form
to the one record, your application open form with the same perfomance on a
table of one record, or 10 million records.

here is the screen shots:
http://www.members.shaw.ca/AlbertKallal/Search/index.html

So once again you do not have to rewrite your application to avoid bound
forms, but what you must do is restrict the number of records loaded *into*
that form to the *one* particular record you need. If you follow this very
simple rule in your applications, then using bound forms and MS access will
absolutely screm in terms of performance ....even with very large tables in
SQL server.

After all if you're only transferring one record from the table into the
form, then how can it be slow?

 

[Tony Epton]

I'm just going to remind you -- because it bugs me so much --
that dao linked table transactions are broken against sql server
in Jet 4. Because DAO maintains a connection pool and in
Jet 4, the connections block each other.

Maybe what you are saying explains the problem I am seeing with the
following piece of code.

The line marked >>> hangs indefinitely.
When I check Locks & Processes in SQL Enterprise manager I find that
the select query is blocked by "sp_execute;1"

The problem only occurs when I wrap a BeginTrans and CommitTrans
around the updates.

I'm guessing that the problem is occuring beause I am accessing the
same table using several different recordsets.

The code below is horrific (and I don't expect anyone to wade though
it) - I just about had blood coming out of my ears by the time I
finished writing it.

Private Sub Form_AfterUpdate()
Dim lngTemplateBookingId As Long
Dim lngParticipantId As Long
Dim rs As Recordset
Dim strSQL As String
Dim rsCS As Recordset
Dim lngSessionBookingId As Long
Dim CompId As Long
Dim boolInclude As Boolean
Dim boolExclude As Boolean
Dim varPrompt As Variant
Dim strWhere As String
Dim boolAlreadyNone As Boolean
Dim boolNullComp As Boolean
Dim lngBackwardAuditLinkId As Long
Dim boolWholeChange As Boolean
Dim frm As Form
Dim rsComp As Recordset
Dim boolAnyCompLeft As Boolean
Dim rsEL As Recordset
Dim rs2 As Recordset
Dim rsCS2 As Recordset
Dim rsCS3 As Recordset
Dim wrkDefault As Workspace
Dim strError As String
Dim rsDel As Recordset

boolWholeChange = False
lngTemplateBookingId = Me.Parent![TemplateBookingId]
lngParticipantId = Me.Parent![ParticipantID]
lngSessionBookingId = Me.SessionBookingId
lngCompId = Me.CompId
boolInclude = Me.Include
varPrompt = Me.Parent!ParticipantBookingSubCtl.Form![Prompt]

'
' Get any of the tblBkSessionCompParticipantLink records
'
strSQL = "Select * from tblBkSessionCompParticipantLink " & _
"WHERE ([TemplateBookingId] = " & lngTemplateBookingId &
") " & _
"AND ([ParticipantId] = " & lngParticipantId & ")"


Set wrkDefault = DBEngine.Workspaces(0)
'
' Beginning of Transaction
'
wrkDefault.BeginTrans

On Error GoTo CompSelectErr

Set rs = CurrentDb().OpenRecordset(strSQL, dbOpenDynaset,
dbSeeChanges)
'
' check no comps left
'
If rs.BOF And rs.EOF Then
boolWholeChange = True 'going from none to part
If boolInclude Then
rs.AddNew
rs![SessionBookingId] = lngSessionBookingId
rs![TemplateBookingId] = lngTemplateBookingId
rs![CompId] = lngCompId
rs![ParticipantID] = lngParticipantId
If IsNull(varPrompt) = False Then
rs![Prompt] = varPrompt
End If
rs.Update
rs.Close
Set rs = Nothing
End If
Exit Sub
Else
boolNullComp = IsNull(rs![CompId])
lngBackwardAuditLinkId = Nz(rs!BackwardAuditLinkId, 0) 'grab its
backward audit link if it has one
End If
'
' got just one record with a null CompId
' or multiple detailed records already ?
'

'just have a single tblBkSessionCompParticipantLink record with
CompId null (ie means include all session comps)

If boolNullComp Then
'delete the single null record
rs.Delete
boolWholeChange = True 'going from whole to part

If boolInclude Then 'we just ticked the box
'should not get here
Else 'we just unticked the box
'having deleted the single tblBkSessionCompParticipantLink
record with the null CompId
'need to now add in multiple records for every Session, Comp
except the specific one we just unticked
Set rsCS = CurrentDb().OpenRecordset("tmpCompSelect")
Do While Not rsCS.EOF
boolExclude = (rsCS!SessionBookingId =
lngSessionBookingId) And (rsCS!CompId = lngCompId)
If Not boolExclude Then
rs.AddNew
rs![SessionBookingId] = rsCS![SessionBookingId]
rs![TemplateBookingId] = lngTemplateBookingId
rs![CompId] = rsCS![CompId]
rs![ParticipantID] = lngParticipantId
If IsNull(varPrompt) = False Then
rs![Prompt] = varPrompt
End If
rs!BackwardAuditLinkId = lngBackwardAuditLinkId
'attempt to patch up backward link if it exists
rs.Update
End If
rsCS.MoveNext
Loop
rsCS.Close
Set rsCS = Nothing

'need to now add in multiple records for every comp element
except the specific comp one we just unticked
'need to examine boolAnyCompLeft - just because we unticked
the competency once it is not necessarily completely gone
'it could still exist in another session

Set rsCS2 = CurrentDb().OpenRecordset("select * from
tmpCompSelect where (tmpCompSelect.[CompId] = " & lngCompId & ") and
(tmpCompSelect.[Include] = True)")
boolAnyCompLeft = Not (rsCS2.BOF And rsCS2.EOF)
rsCS2.Close
Set rsCS2 = Nothing

Set rsEL = CurrentDb().OpenRecordset("tmpElementSelect")
If Not (rsEL.BOF And rsEL.EOF) Then
If boolAnyCompLeft = False Then
Do While Not rsEL.EOF
If rsEL!CompId = lngCompId Then
rsEL.Edit
rsEL![Include] = False
rsEL.Update
End If
rsEL.MoveNext
Loop
End If
rsEL.MoveFirst
Set rs2 =
CurrentDb().OpenRecordset("tblBkCompElementParticipantLink",
dbOpenDynaset, dbSeeChanges)
Do While Not rsEL.EOF
If rsEL![Include] = True Then
rs2.AddNew
rs2![TemplateBookingId] = lngTemplateBookingId
rs2![ParticipantID] = lngParticipantId
rs2![CompElementId] = rsEL![CompElementId]
rs2.Update
End If
rsEL.MoveNext
Loop
rs2.Close
Set rs2 = Nothing
End If
rsEL.Close
Set rsEL = Nothing
End If

'have multiple tblBkSessionCompParticipantLink records already (ie
means at least one session comp has been unticked)

Else
If boolInclude Then 'we just ticked the box
rs.AddNew
rs![SessionBookingId] = lngSessionBookingId
rs![TemplateBookingId] = lngTemplateBookingId
rs![CompId] = lngCompId
rs![ParticipantID] = lngParticipantId
If IsNull(varPrompt) = False Then
rs![Prompt] = varPrompt
End If
rs!BackwardAuditLinkId = lngBackwardAuditLinkId
rs.Update
'
' If we just ticked a box - have we managed to retick all of
them ?
'
Set rsCS = CurrentDb().OpenRecordset("select * from
tmpCompSelect where [Include] = false")
Set rsCS3 = CurrentDb().OpenRecordset("select * from
tmpElementSelect where [Include] = false")

If (rsCS.BOF And rsCS.EOF) And (rsCS3.BOF And rsCS3.EOF) Then
'everything is ticked
'so delete all the little
tblBkSessionCompParticipantLink,tblBkCompElementParticipantLink
records for this person, this booking
boolWholeChange = True 'going from part to Whole
strSQL = "select * from tblBkSessionCompParticipantLink "
& _
"WHERE ([TemplateBookingId] = " &
lngTemplateBookingId & ") " & _
"AND ([ParticipantId] = " & lngParticipantId &
")"Do While Not rsDel.EOF
rsDel.Delete
rsDel.MoveNext
Loop
rsDel.Close

strSQL = "select * from tblBkCompElementParticipantLink "
& _
"WHERE ([TemplateBookingId] = " &
lngTemplateBookingId & ") " & _
"AND ([ParticipantId] = " & lngParticipantId &
")"
Set rsDel = CurrentDb().OpenRecordset(strSQL,
dbOpenDynaset, dbSeeChanges)
Do While Not rsDel.EOF
rsDel.Delete
rsDel.MoveNext
Loop
rsDel.Close
Set rsDel = Nothing
'
'now add back in a single tblBkSessionCompParticipantLink
record with null CompId
'
Set rs =
CurrentDb().OpenRecordset("tblBkSessionCompParticipantLink",
dbOpenDynaset, dbSeeChanges)
rs.AddNew
rs![TemplateBookingId] = lngTemplateBookingId
rs![ParticipantID] = lngParticipantId
If IsNull(varPrompt) = False Then
rs![Prompt] = varPrompt
End If
rs!BackwardAuditLinkId = lngBackwardAuditLinkId
rs.Update
End If
rsCS.Close
Set rsCS = Nothing
rsCS3.Close
Set rsCS3 = Nothing

Else 'we just unticked the box
strWhere = "([SessionBookingId] = " & lngSessionBookingId &
") AND ([CompId] = " & lngCompId & ")"
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Something wrong - cannot find
tblBkSessionCompParticipantLink record being unticked"
End If
rs.Delete
'
' any of this comp left at all
'
Set rsCS2 = CurrentDb().OpenRecordset("select * from
tmpCompSelect where (tmpCompSelect.[CompId] = " & lngCompId & ") and
(tmpCompSelect.[Include] = True)")
boolAnyCompLeft = Not (rsCS2.BOF And rsCS2.EOF)
rsCS2.Close
Set rsCS2 = Nothing
'
' if none of this comp left then
' untick any of its elements
'
If boolAnyCompLeft = False Then
Set rsEL = CurrentDb().OpenRecordset("tmpElementSelect")
If Not (rsEL.BOF And rsEL.EOF) Then
Set rs2 =
CurrentDb().OpenRecordset("tblBkCompElementParticipantLink",
dbOpenDynaset, dbSeeChanges)
Do While Not rsEL.EOF
If rsEL!CompId = lngCompId Then
rsEL.Edit
rsEL![Include] = False
lngCompElementId = rsEL![CompElementId]
rsEL.Update
strWhere = "([TemplateBookingId] = " &
lngTemplateBookingId & ") " & _
" AND ([CompElementId] = " &
lngCompElementId & ") " & _
" AND ([ParticipantId] = " &
lngParticipantId & ")"
rs2.FindFirst strWhere
If Not rs2.NoMatch Then
rs2.Delete
End If
End If
rsEL.MoveNext
Loop
rsEL.MoveFirst

rs2.Close
Set rs2 = Nothing

End If
rsEL.Close
Set rsEL = Nothing
End If

End If
End If

rs.Close
Set rs = Nothing

wrkDefault.CommitTrans
'
' End of Transaction
'
On Error GoTo 0

If boolWholeChange Then
Set frm = Me.Parent
BuildParticipantListBkGeneric lngTemplateBookingId, "GRID", True
'Rebuild tmpParticipantList so that the list box will show the new
participant
frm!lstParticipants.Requery 'refresh the list
box
frm!lstParticipantsMulti.Requery 'refresh the list
box

HighLightSelectedParticipant frm, frm!ParticipantID

Me.Parent!lstParticipants.SetFocus
DoMaintainParticipantVisibility Me.Parent
frm![CompSelectSubCtl].Visible = True
frm![ElementSelectSubCtl].Visible = True
frm![CompSelectSubCtl].SetFocus
End If

RecalcNumberEnrolled (lngTemplateBookingId)

Exit Sub

'
' Transaction error trap
'
CompSelectErr:
wrkDefault.Rollback
strError = Err.Description
On Error GoTo 0
MsgBox strError & vbCrLf & "While attempting to select / unselect
Competencies" & vbCrLf & _
"Changes completely rolled back"
Exit Sub

End Sub

 

[Tony Epton]

Actually you can will get away with bound forms directly to tables, even
when you use SQL server. the very simple trick here is to make sure your
designs minimized the records pulled into the form.

Many thanks Albert

Food for thought

Tony

 

[David W. Fenton]

The answer to the question in your subject is:

NO.

NO.

NO.

People who recommend this without providing specific examples of
which code will be improved, how and why should be ignored.

 

[david]

Yes, and No :~) Yes, it probably explains what you are seeing,
and No, and I can't follow it :~). I don't know enough SQL Server
to say what sp_execute; is doing. It may also be blocking against
a bound form or combo box.

Generally speaking, you need to be careful about what else is open,
because you can block against forms and combo boxes, but that
won't solve the problem. When you have fixed everything, you
will still find that the system is fundamentally broken.

(david)

I'm just going to remind you -- because it bugs me so much --
that dao linked table transactions are broken against sql server
in Jet 4. Because DAO maintains a connection pool and in
Jet 4, the connections block each other.

Maybe what you are saying explains the problem I am seeing with the
following piece of code.

The line marked >>> hangs indefinitely.
When I check Locks & Processes in SQL Enterprise manager I find that
the select query is blocked by "sp_execute;1"

The problem only occurs when I wrap a BeginTrans and CommitTrans
around the updates.

I'm guessing that the problem is occuring beause I am accessing the
same table using several different recordsets.

The code below is horrific (and I don't expect anyone to wade though
it) - I just about had blood coming out of my ears by the time I
finished writing it.

Private Sub Form_AfterUpdate()
Dim lngTemplateBookingId As Long
Dim lngParticipantId As Long
Dim rs As Recordset
Dim strSQL As String
Dim rsCS As Recordset
Dim lngSessionBookingId As Long
Dim CompId As Long
Dim boolInclude As Boolean
Dim boolExclude As Boolean
Dim varPrompt As Variant
Dim strWhere As String
Dim boolAlreadyNone As Boolean
Dim boolNullComp As Boolean
Dim lngBackwardAuditLinkId As Long
Dim boolWholeChange As Boolean
Dim frm As Form
Dim rsComp As Recordset
Dim boolAnyCompLeft As Boolean
Dim rsEL As Recordset
Dim rs2 As Recordset
Dim rsCS2 As Recordset
Dim rsCS3 As Recordset
Dim wrkDefault As Workspace
Dim strError As String
Dim rsDel As Recordset

boolWholeChange = False
lngTemplateBookingId = Me.Parent![TemplateBookingId]
lngParticipantId = Me.Parent![ParticipantID]
lngSessionBookingId = Me.SessionBookingId
lngCompId = Me.CompId
boolInclude = Me.Include
varPrompt = Me.Parent!ParticipantBookingSubCtl.Form![Prompt]

'
' Get any of the tblBkSessionCompParticipantLink records
'
strSQL = "Select * from tblBkSessionCompParticipantLink " & _
"WHERE ([TemplateBookingId] = " & lngTemplateBookingId &
") " & _
"AND ([ParticipantId] = " & lngParticipantId & ")"


Set wrkDefault = DBEngine.Workspaces(0)
'
' Beginning of Transaction
'
wrkDefault.BeginTrans

On Error GoTo CompSelectErr

Set rs = CurrentDb().OpenRecordset(strSQL, dbOpenDynaset,
dbSeeChanges)
'
' check no comps left
'
If rs.BOF And rs.EOF Then
boolWholeChange = True 'going from none to part
If boolInclude Then
rs.AddNew
rs![SessionBookingId] = lngSessionBookingId
rs![TemplateBookingId] = lngTemplateBookingId
rs![CompId] = lngCompId
rs![ParticipantID] = lngParticipantId
If IsNull(varPrompt) = False Then
rs![Prompt] = varPrompt
End If
rs.Update
rs.Close
Set rs = Nothing
End If
Exit Sub
Else
boolNullComp = IsNull(rs![CompId])
lngBackwardAuditLinkId = Nz(rs!BackwardAuditLinkId, 0) 'grab its
backward audit link if it has one
End If
'
' got just one record with a null CompId
' or multiple detailed records already ?
'

'just have a single tblBkSessionCompParticipantLink record with
CompId null (ie means include all session comps)

If boolNullComp Then
'delete the single null record
rs.Delete
boolWholeChange = True 'going from whole to part

If boolInclude Then 'we just ticked the box
'should not get here
Else 'we just unticked the box
'having deleted the single tblBkSessionCompParticipantLink
record with the null CompId
'need to now add in multiple records for every Session, Comp
except the specific one we just unticked
Set rsCS = CurrentDb().OpenRecordset("tmpCompSelect")
Do While Not rsCS.EOF
boolExclude = (rsCS!SessionBookingId =
lngSessionBookingId) And (rsCS!CompId = lngCompId)
If Not boolExclude Then
rs.AddNew
rs![SessionBookingId] = rsCS![SessionBookingId]
rs![TemplateBookingId] = lngTemplateBookingId
rs![CompId] = rsCS![CompId]
rs![ParticipantID] = lngParticipantId
If IsNull(varPrompt) = False Then
rs![Prompt] = varPrompt
End If
rs!BackwardAuditLinkId = lngBackwardAuditLinkId
'attempt to patch up backward link if it exists
rs.Update
End If
rsCS.MoveNext
Loop
rsCS.Close
Set rsCS = Nothing

'need to now add in multiple records for every comp element
except the specific comp one we just unticked
'need to examine boolAnyCompLeft - just because we unticked
the competency once it is not necessarily completely gone
'it could still exist in another session

Set rsCS2 = CurrentDb().OpenRecordset("select * from
tmpCompSelect where (tmpCompSelect.[CompId] = " & lngCompId & ") and
(tmpCompSelect.[Include] = True)")
boolAnyCompLeft = Not (rsCS2.BOF And rsCS2.EOF)
rsCS2.Close
Set rsCS2 = Nothing

Set rsEL = CurrentDb().OpenRecordset("tmpElementSelect")
If Not (rsEL.BOF And rsEL.EOF) Then
If boolAnyCompLeft = False Then
Do While Not rsEL.EOF
If rsEL!CompId = lngCompId Then
rsEL.Edit
rsEL![Include] = False
rsEL.Update
End If
rsEL.MoveNext
Loop
End If
rsEL.MoveFirst
Set rs2 =
CurrentDb().OpenRecordset("tblBkCompElementParticipantLink",
dbOpenDynaset, dbSeeChanges)
Do While Not rsEL.EOF
If rsEL![Include] = True Then
rs2.AddNew
rs2![TemplateBookingId] = lngTemplateBookingId
rs2![ParticipantID] = lngParticipantId
rs2![CompElementId] = rsEL![CompElementId]
rs2.Update
End If
rsEL.MoveNext
Loop
rs2.Close
Set rs2 = Nothing
End If
rsEL.Close
Set rsEL = Nothing
End If

'have multiple tblBkSessionCompParticipantLink records already (ie
means at least one session comp has been unticked)

Else
If boolInclude Then 'we just ticked the box
rs.AddNew
rs![SessionBookingId] = lngSessionBookingId
rs![TemplateBookingId] = lngTemplateBookingId
rs![CompId] = lngCompId
rs![ParticipantID] = lngParticipantId
If IsNull(varPrompt) = False Then
rs![Prompt] = varPrompt
End If
rs!BackwardAuditLinkId = lngBackwardAuditLinkId
rs.Update
'
' If we just ticked a box - have we managed to retick all of
them ?
'
Set rsCS = CurrentDb().OpenRecordset("select * from
tmpCompSelect where [Include] = false")
Set rsCS3 = CurrentDb().OpenRecordset("select * from
tmpElementSelect where [Include] = false")

If (rsCS.BOF And rsCS.EOF) And (rsCS3.BOF And rsCS3.EOF) Then
'everything is ticked
'so delete all the little
tblBkSessionCompParticipantLink,tblBkCompElementParticipantLink
records for this person, this booking
boolWholeChange = True 'going from part to Whole
strSQL = "select * from tblBkSessionCompParticipantLink "
& _
"WHERE ([TemplateBookingId] = " &
lngTemplateBookingId & ") " & _
"AND ([ParticipantId] = " & lngParticipantId &
")"Do While Not rsDel.EOF
rsDel.Delete
rsDel.MoveNext
Loop
rsDel.Close

strSQL = "select * from tblBkCompElementParticipantLink "
& _
"WHERE ([TemplateBookingId] = " &
lngTemplateBookingId & ") " & _
"AND ([ParticipantId] = " & lngParticipantId &
")"
Set rsDel = CurrentDb().OpenRecordset(strSQL,
dbOpenDynaset, dbSeeChanges)
Do While Not rsDel.EOF
rsDel.Delete
rsDel.MoveNext
Loop
rsDel.Close
Set rsDel = Nothing
'
'now add back in a single tblBkSessionCompParticipantLink
record with null CompId
'
Set rs =
CurrentDb().OpenRecordset("tblBkSessionCompParticipantLink",
dbOpenDynaset, dbSeeChanges)
rs.AddNew
rs![TemplateBookingId] = lngTemplateBookingId
rs![ParticipantID] = lngParticipantId
If IsNull(varPrompt) = False Then
rs![Prompt] = varPrompt
End If
rs!BackwardAuditLinkId = lngBackwardAuditLinkId
rs.Update
End If
rsCS.Close
Set rsCS = Nothing
rsCS3.Close
Set rsCS3 = Nothing

Else 'we just unticked the box
strWhere = "([SessionBookingId] = " & lngSessionBookingId &
") AND ([CompId] = " & lngCompId & ")"
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Something wrong - cannot find
tblBkSessionCompParticipantLink record being unticked"
End If
rs.Delete
'
' any of this comp left at all
'
Set rsCS2 = CurrentDb().OpenRecordset("select * from
tmpCompSelect where (tmpCompSelect.[CompId] = " & lngCompId & ") and
(tmpCompSelect.[Include] = True)")
boolAnyCompLeft = Not (rsCS2.BOF And rsCS2.EOF)
rsCS2.Close
Set rsCS2 = Nothing
'
' if none of this comp left then
' untick any of its elements
'
If boolAnyCompLeft = False Then
Set rsEL = CurrentDb().OpenRecordset("tmpElementSelect")
If Not (rsEL.BOF And rsEL.EOF) Then
Set rs2 =
CurrentDb().OpenRecordset("tblBkCompElementParticipantLink",
dbOpenDynaset, dbSeeChanges)
Do While Not rsEL.EOF
If rsEL!CompId = lngCompId Then
rsEL.Edit
rsEL![Include] = False
lngCompElementId = rsEL![CompElementId]
rsEL.Update
strWhere = "([TemplateBookingId] = " &
lngTemplateBookingId & ") " & _
" AND ([CompElementId] = " &
lngCompElementId & ") " & _
" AND ([ParticipantId] = " &
lngParticipantId & ")"
rs2.FindFirst strWhere
If Not rs2.NoMatch Then
rs2.Delete
End If
End If
rsEL.MoveNext
Loop
rsEL.MoveFirst

rs2.Close
Set rs2 = Nothing

End If
rsEL.Close
Set rsEL = Nothing
End If

End If
End If

rs.Close
Set rs = Nothing

wrkDefault.CommitTrans
'
' End of Transaction
'
On Error GoTo 0

If boolWholeChange Then
Set frm = Me.Parent
BuildParticipantListBkGeneric lngTemplateBookingId, "GRID", True
'Rebuild tmpParticipantList so that the list box will show the new
participant
frm!lstParticipants.Requery 'refresh the list
box
frm!lstParticipantsMulti.Requery 'refresh the list
box

HighLightSelectedParticipant frm, frm!ParticipantID

Me.Parent!lstParticipants.SetFocus
DoMaintainParticipantVisibility Me.Parent
frm![CompSelectSubCtl].Visible = True
frm![ElementSelectSubCtl].Visible = True
frm![CompSelectSubCtl].SetFocus
End If

RecalcNumberEnrolled (lngTemplateBookingId)

Exit Sub

'
' Transaction error trap
'
CompSelectErr:
wrkDefault.Rollback
strError = Err.Description
On Error GoTo 0
MsgBox strError & vbCrLf & "While attempting to select / unselect
Competencies" & vbCrLf & _
"Changes completely rolled back"
Exit Sub

End Sub