Re: MDAC 2.8 vs MDAC 2.7

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Val Mazur (group51a_at_hotmail.com)
Date: 10/30/04 

Date: Fri, 29 Oct 2004 21:45:23 -0400

Hi,

Actually RecordCount has to do with the provider, because it directly to the
provider if it is able to return actual record count for the specified
cursor. But in a case if provider is not able to return record count, it
will return -1, not 0. In a case of 0 could be two issues: there are no
records in a recordset or it is a bug.
If just changing of the connection string to use OLEDB provider solves this
issue, then it looks like a bug in OLEDB for ODBC provider or in ODBC
driver. But do you get an actual recordset opened in your case?
Initially it was not a good idea to use DSN, because ADO does NOT work with
ODBC drivers directly and has to place OLEDB for ODBC driver in between ADO
and ODBC driver. It just add additional layer with potential bugs in it.
Also you would need to separate declaration and instantiation of the
connection into two separate lines of code. Otherwise it would lead to the
connection and memory leaks. Your code should look like

Dim cn1 as adodb.connection

Set cn1 as new adodb.connection 

-- 
Val Mazur
Microsoft MVP
"Jiho Han" <jhan@infinityinfo.com> wrote in message 
news:%23hHjvrdvEHA.716@TK2MSFTNGP10.phx.gbl...
> Your problem with RecordCount has nothing to do with OLEDB vs. ODBC nor 
> MDAC 2.8 vs MDAC 2.7 as the subject suggests.
>
> Try setting:
>
> rst.CursorLocation = adUseClient
>
> before you open the recordset.
>
>> I have written several vb6 applications with MDAC 2.7 SP1 Refresh.
>> This application connects to a SQL Server 2000 SP3a on Windows 20003
>> Server.
>> I run this application on a WXP Pro client.
>> I have a DSN defined to connect to SQLServer via ODBC
>> All works ok with this code:
>> Dim cn1 as new adodb.connection
>> Dim rst as ADODB.Recordset
>> Dim lngNumber as long
>> cn1.ConnectionTimeout = 60
>> cn1.CursorLocation = adUseClient
>> cn1.Open "DSN=" & sName, sUser, sPwd
>> rst.Open "SELECT ID FROM TABLE", cn1, adOpenKeyset, adLockOptimistic
>> lngNumber = rst.Recordcount
>>
>> ' Note that lngNumber = 3567
>> if lngNumber=0 then MsgBox "Error"
>> .....
>> If i put the application on Windows 2003 server i get:
>> ' The same table with 3567 records return 0
>> if lngNumber=0 then MsgBox "Error"
>> and the Box "Error" appears!!!!!
>>
>> I changed to OleDB and It works but the problem is:
>> when all my server will be Win 2003 I will have lots of
>> program with problems !!!!!!
>> How can i continue in using DSN with ODBC with Windows 2003 server ?
>> My customers are not enthusiastic to pay me to
>> change all programs !
>> Help me please
>> Thank you
>

Relevant Pages

  • Re: Providers: DSN vs DSNLess. Data Returns are different.
    ... DSN-less connection you probably using OLEDB provider directly. ... > DSNLess connection or a DSN does make a difference. ... Using ASP I found that the recordset was always closed even the ...
    (microsoft.public.data.ado)
  • Re: MDAC 2.8 vs MDAC 2.7
    ... I disagree with the blanket statement that choosing ODBC is in general a bad ... I have observed cases where the ODBC driver is more stable and ... performs better than the native OLE DB provider. ... But do you get an actual recordset opened in your case? ...
    (microsoft.public.data.ado)
  • Re: Error -2147418113 with AddNew in transaction
    ... > Microsoft OLE DB Provider for SQL Server 7.01 ... > Any operation following a rollback or a commit transaction on a recordset ... > The OLE DB provider must support preserving cursors. ... > SQL Server supports preserving cursors on Commit and Abort. ...
    (microsoft.public.data.ado)
  • Re: Data provider or other service returned an E_FAIL status every
    ... 'repeat each merge above for each recordset ... SQLOLEDB provider and that the stream itself is using a TEXT provider. ... For Each objField In recSourceClone.Fields ... I am thinking I could run a connection ...
    (microsoft.public.data.ado)
  • Re: Newbie Q. for referencing DAO recordsets with VBA in Access front
    ... recordset. ... designer, keep in mind that sometimes the columns are renamed to something ... isn´t supported by that provider. ... HTH, Jens Suessmeyer. ...
    (microsoft.public.sqlserver.msde)