adUseClient vs adUseServer

[mikemcginty]

Is it true that I cannot add a new record when .cursorlocation = adUseClient?

What is the difference between the two then?

The code below works for adUseServer but the autonumber returned is null for adUseClient

Set rsCertificates = New ADODB.Recordset
ADODBCheckConnection
Set rsCertificates.ActiveConnection = dbQADMObject


With rsCertificates

' retrieve all existing certificate records for this document
.source = "SELECT * from UtblQADM" & sCategoryTableName & _
" where fCategoryID = " & lCategoryID & _
" ORDER BY CertificateID"

.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic

dbQADMObject.Errors.Clear

.Open

' create the new certificate
.AddNew

' retrieve the new certificate ID (autonumber) field
' this is the next autonumber using adUseServer and null if using adUseClient
lNewCertificateID = rsCertificates ("CertificateID&quot.value

Thanks

Mike




When you call out for help in the darkness, and you hear a voice in return, you're probably just talking to yourself again!

 

[TomKane]

When you do use client it makes it a disconnected recordset. You'll probably find a better explanation in msdn.microsoft.com

 

[CCLINT]

Try:

.LockType = adLockBatchOptimistic


and


.AddNew
.
.
.
.UpdateBatch

But you will not be able to get the Identity value that way.
Try Set Rs = Conn.Execute(SELECT @@IDENTITY)
lNewCertificateID = Rs(0)

This will only work with JET 4 and a 2000 MDB.

 

[mikemcginty]

Thanks fellas

CCLINT
I found that I must use .update before I assign the identity value rsCertificates ("CertificateID&quot.value
otherwise it is null. This seems to be a "feature" of Access

I am only adding 1 record then closing the recordset. What difference does adLockBatchOptimistic make?

I also found that when using adUseClient if I use

"Select DataField from Table where FieldID = ID" instead of
"Select * from Table where FieldID = ID"

when I update DataField for the single returned record it updates all records DataField in the table with the new data and I get an error that suggests Jet overran the table in trying to update all table records not just the returned record.


TomKane
I didn't want to use disconnected recordsets because I am not batching or using transactions. Should I stick to adUseServer then? Which is best then for a multiuser network?

Thanks

Mike


When you call out for help in the darkness, and you hear a voice in return, you're probably just talking to yourself again!