DB Problem

I'm having a very strange problem. Last week, this program was working almost 100%, now I'm getting errors when retrieving data from tables both on my computer and on other computers that were at one time working. I've done nothing new to the code in the areas that are now not working, I've compacted and repaired the database, I've tried running it on a local database as opposed to the server on the database so I know no new changes were made to the database, I've tried reinstalling MDAC, nothing works.

Here's a piece of code where it's retrieving data properly:

Set adoSearchRecordset = New ADODB.Recordset
adoSearchRecordset.Open "Select * from donor_file where ID = '" & strSearchFind & "'", adoDonorConnect, adOpenKeyset, adLockOptimistic, adCmdText
BindDonorFields

Now, this piece of code is suddenly not working:

strSQL = "SELECT TransID, PledgeDate, CampaignID, CreditAmount, DebitAmount, PayType FROM pledge_file WHERE DonorID = '01064003'"
Set adoPledgeRecordset = New ADODB.Recordset
adoPledgeRecordset.Open strSQL, adoDonorConnect, adOpenDynamic, adLockOptimistic, adCmdText
Set frmDonor.grdPledge.DataSource = adoPledgeRecordset
frmDonor.grdPledge.Refresh
frmDonor.grdPledge.Layout.Override.HeaderClickAction = ssHeaderClickActionSortMulti
frmDonor.grdPledge.Bands(0).Columns(0).Width = 1490

If I do a recordcount for the recordset it returns a -1. If I run that same query directly in access I do get records returned. Also, none of this code was touched and before it was running just fine. It errors out by saying "provider does not support minimum binding requirements". I've tried changing that so it would just open up the whole table:

adoPledgeRecordset.Open pledge_file, adoDonorConnect, adOpenDynamic, adLockOptimistic, adCmdTable

or

adoPledgeRecordset.Open "Select * from pledge_file", adoDonorConnect, adOpenDynamic, adLockOptimistic, adCmdText


With that code I get an error saying "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another" when I just try to open it as a table. When I try to open the whole table using Select I get an error and a recordcount of -1. Any help would be greatly appreciated.

Thanks!

Hi,

>If I do a recordcount for the recordset it returns a -1
Try to use open adOpenKeyset instead of adOpenDynamic

Helllo BoSelecta,
In addition to Shurik12's answer. Have your RecordSet CursorLocation = AdUseClient :)


Set adoSearchRecordset = New ADODB.Recordset
adoSearchRecordset.CursorLocation = AdUseClient

Thanks,
I did add the client-side cursor and it seems to be working again. I'm almost positive I never had that in there when it was working in the first place but regardless, it is working now.
Thanks again.

Access always uses a client-side cursor whether you don't specify it or not. Access isn't a server so it can't use a server-side cursor. The result was likely what Shurik12 advised. Also, a -1 record count does not necessarily mean that there are no records in the recordset. If you don't use adOpenKeyset or adOpenStatic, you will always get a -1 record count, even if there are 10000 records in the recordset. -1 simply means .BOF (beginning of file). I think -3 means both BOF and EOF (no records).