Insufficient key column problem on discon rs

MArgente · June 21st, 2007, 03:30 PM

Hello all.

I have a disconnected recordset called rs. After I tried reconnecting and using the .UpdateBatch command, I get this error "Insufficient key column for updating and refreshing."

I was reading around so I decided to add an auto number from my main table where edits are being made. This is my sql statement:

Code:

    Dim strSQL As String
    strSQL = "SELECT deceasedHolder.[AutoNumber], Company.[Company Short Name], deceasedHolder.[Company ID], Cusip.[InterestPaymentCycle], " & _
    " deceasedHolder.[CUSIP Number], deceasedHolder.Interest, Broker.[Broker Short Name], deceasedHolder.[Date Received], " & _
    " deceasedHolder.Name, deceasedHolder.Quantity, deceasedHolder.[Approved by Issuer], deceasedHolder.[Number Day], " & _
    " deceasedHolder.[Next Put Date], deceasedHolder.[Quantity Approved], deceasedHolder.[Accrued Interest] " & _
    " FROM (Cusip INNER JOIN (Company INNER JOIN deceasedHolder ON " & _
    " Company.[Company ID] = deceasedHolder.[Company ID]) " & _
    " ON (deceasedHolder.[Company ID] = Cusip.[Company ID]) " & _
    " AND (Cusip.[CUSIP Number] = deceasedHolder.[CUSIP Number])) INNER JOIN Broker " & _
    " ON deceasedHolder.[Broker ID] = Broker.[Broker ID] " & _
    " WHERE deceasedHolder.[Company ID] ='" & Me.Company_ID & "'" & _
    " AND deceasedHolder.[Next Put Date] = #" & Me!txtPutDate & "#" & _
    " ORDER BY deceasedHolder.[Approved by Issuer] DESC , deceasedHolder.[CUSIP Number], deceasedHolder.[Date Received];"

However, I'm still receiving the error. The data that I'm updating is only from 1 table (but it gets the error on refresh, so I guess that doesn't matter). I found this article: http://support.microsoft.com/kb/815542

but that will not work in my case since I'm using a disconnected recordset. Any suggestions?

robzyc · June 21st, 2007, 03:41 PM

MAargente,

Good man for checking out the KB, not something you see often!
As far as I can remember, UpdateBatch will only work on a adKeySet cursor, which is not the
default (the default is adStatic). This is basically what the DB provider is saying.
"I cant update a batch because I dont know which record is which". Where most people fall
over is that they dont have an ID field and cant set the cursor to adKeyset. But you have
that taken care of!

Try setting the rs.CursorType to adKeyset and let us know what happens...

Best Regards,
Rob

MArgente · June 22nd, 2007, 08:17 AM

Code:

rs.CursorType = adOpenKeyset
rs.CursorLocation = adUseClient
rs.LockType = adLockBatchOptimistic
rs.Open strSQL, CurrentProject.Connection

Set rs.ActiveConnection = Nothing
Set Me.Recordset = rs

I actually already had been using adOpenKeyset (I tried using adKeyset, but that didn't work either. Any other suggestions?

robzyc · June 22nd, 2007, 08:39 AM

Does the RS actually Open as a keyset cursor? Check the CursorType once the recordset has been opened..

Regards,
Rob

MArgente · June 22nd, 2007, 09:10 AM

For rs.cursortype = adKeyset
it returned 0

For rs.cursortype = adOpenKeyset
it returned 1.

If I can't get this to work, I'm probably just going to loop through the disconnected recordset and use a command object to update my table. I need to figure that out since I'm relatively new at Access/SQL

robzyc · June 22nd, 2007, 09:35 AM

MArgente,

Sorry, what I meant was:

Code:

rs.CursorType = adOpenKeyset
rs.CursorLocation = adUseClient
rs.LockType = adLockBatchOptimistic
rs.Open strSQL, CurrentProject.Connection
Debug.Print rs.CursorType  = CursorTypeEnum.adOpenKeyset
Set rs.ActiveConnection = Nothing
Set Me.Recordset = rs

Try that and see whats output to the debug window.

Regards,
Rob

MArgente · June 22nd, 2007, 09:47 AM

returned False..

It turns out that it is setting the CursorType to 3 (adOpenStatic)

robzyc · June 22nd, 2007, 09:59 AM

Yeah I have had this before, I actually have batch updating in a project i working on, and just double checked
and it actually works with adOpenStatic cursor, but then again I am generating the recordset on the fly,
not using existing data.

Are the ID field set as an actual Index/PK field? Which field is it?

Regards,
Rob

MArgente · June 22nd, 2007, 10:14 AM

I do not know how to set an ID field to a (disconnected) recordset. I'm fairly new at coding vba/access. This is the info I can tell you:

The recordset pulls information from three tables. The data that is actually being updated is only on one table. The other information is just displayed and locked. The information being updated are not PKs.

robzyc · June 23rd, 2007, 03:22 AM

If you are updating information, would it not be better to create SQL UPDATE commands
rather than use a recordset, this will perform faster than using recordsets to cursor
through.

Once the changes are made to the recordset, what are you doing with the new information?

Regards,
Rob