If there is a record in DAO and not in ADO then there can only be a couple of things wrong.
I would define your connection string. Chances are it is not correct. I would put a breakpoint and check what strSQL has as a value. Check for proper spacing, etc. In fact, can't you drop the strSQL in the command window and see the real values of strSQL? Post it and maybe someone will see what is wrong. Finally, why are you doing ADO and DAO together? Roy ________________________________ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Bryant Sent: Monday, July 18, 2005 2:43 PM To: [email protected] Subject: Re: [Access VBA Central] Different results from DAO and ADO recordsets This gives a "No current record" error -- cannot move first/last when there is no record retrieved. The data, when correctly queried, produces a single record. DAO produces this record, as does executing the query manually. ADO does not. Bryant --- In [email protected], "Roy Forkner" <[EMAIL PROTECTED]> wrote: > Try : > > Rst.movelast > > Rst.movefirst > > > > The same as the someone's suggestion except use ADO instead of DAO > > > > ________________________________ > > From: [email protected] > [mailto:[EMAIL PROTECTED] On Behalf Of Bryant > Sent: Monday, July 18, 2005 2:24 PM > To: [email protected] > Subject: Re: [Access VBA Central] Different results from DAO and ADO > recordsets > > > > Thanks for the semicolon tip -- it didn't work but that is something I > be careful to put in my code to avoid it being an issue. > > The query runs fine in SQL view - and in DAO via code. That's what > makes it puzzling - lucky I have no hair to pull out :) > > Bryant > > --- In [email protected], Dennis Jensen > <[EMAIL PROTECTED]> wrote: > > For ADO try adjusting the last line as such... > > > > & "AND TenantID ='" & strTenantID & "';" > > > > Not sure if that will fix it but I have found that not > > including the ";" at the in when query an Access > > database can cause the query to fail. > > > > The next best thing is to take a copy of the actual > > query being submitted via code and put that in a > > Queries SQL view and see if you can execute it. If it > > fails it might say why. > > > > > > --- Bryant <[EMAIL PROTECTED]> wrote: > > > > > I get two different results when querying the same > > > data, depending on > > > whether I use DAO 3.6 or ADO 2.1 recordset ... any > > > ideas where I'm > > > going astray? > > > > > > The ADO recordset retrieves zero rows each time. The > > > SQL string is > > > valid, since results are produced via DAO. > > > > > > Thanks in advance! > > > > > > -- Begin ADO -- > > > Dim rst As New ADODB.Recordset > > > Dim cnn As New ADODB.Connection > > > Dim strSQL As String > > > > > > Set cnn = CurrentProject.Connection > > > > > > strSQL = "SELECT LateFeesCharged FROM > > > qselTransactions_LateFeesCharged " _ > > > & "WHERE CompanyID ='" & strCompanyID & "' " _ > > > & "AND TenantID ='" & strTenantID & "'" > > > > > > rst.Open strSQL, cnn, adOpenStatic, adLockReadOnly > > > > > > If Not rst.EOF Then > > > GetLateFeesCharged = rst!LateFeesCharged > > > Else > > > GetLateFeesCharged = 0 > > > End If > > > > > > rst.Close > > > -- End ADO -- > > > > > > -- Begin DAO -- > > > Dim rst As DAO.Recordset > > > Dim strSQL As String > > > > > > strSQL = "SELECT LateFeesCharged FROM > > > qselTransactions_LateFeesCharged " _ > > > & "WHERE CompanyID ='" & strCompanyID & "' " _ > > > & "AND TenantID ='" & strTenantID & "'" > > > > > > Set rst = CurrentDb.OpenRecordset(strSQL, > > > dbOpenDynaset, dbReadOnly) > > > > > > If Not rst.EOF Then > > > GetLateFeesCharged_DAO = rst!LateFeesCharged > > > Else > > > GetLateFeesCharged_DAO = 0 > > > End If > > > > > > rst.Close > > > > > > -- End DAO -- > > > > > > > > > > > > > > > > > > > ____________________________________________________ > > Start your day with Yahoo! - make it your home page > > http://www.yahoo.com/r/hs > > > > > > > ________________________________ > > YAHOO! GROUPS LINKS > > > > * Visit your group "AccessVBACentral > <http://groups.yahoo.com/group/AccessVBACentral> " on the web. > > * To unsubscribe from this group, send an email to: > [EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED] > > > > * Your use of Yahoo! Groups is subject to the Yahoo! Terms of > Service <http://docs.yahoo.com/info/terms/> . > > > > ________________________________ > > > > [Non-text portions of this message have been removed] SPONSORED LINKS Microsoft access database <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+database&w1=Micros oft+access+database&w2=Microsoft+access&w3=Microsoft+access+database+des ign&w4=Microsoft+access+database+training&c=4&s=131&.sig=_NMBmgHqxR8S8tj yGl-Tnw> Microsoft access <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access&w1=Microsoft+acces s+database&w2=Microsoft+access&w3=Microsoft+access+database+design&w4=Mi crosoft+access+database+training&c=4&s=131&.sig=-5Xf2KTU4AmC58h30Cq3wg> Microsoft access database design <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+database+design&w1 =Microsoft+access+database&w2=Microsoft+access&w3=Microsoft+access+datab ase+design&w4=Microsoft+access+database+training&c=4&s=131&.sig=rEvcpxWQ YfjON1btwfI3Aw> Microsoft access database training <http://groups.yahoo.com/gads?t=ms&k=Microsoft+access+database+training&; w1=Microsoft+access+database&w2=Microsoft+access&w3=Microsoft+access+dat abase+design&w4=Microsoft+access+database+training&c=4&s=131&.sig=pILo23 -rKYDuxPwf_IbrRQ> ________________________________ YAHOO! GROUPS LINKS * Visit your group "AccessVBACentral <http://groups.yahoo.com/group/AccessVBACentral> " on the web. * To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED] > * Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service <http://docs.yahoo.com/info/terms/> . ________________________________ [Non-text portions of this message have been removed] Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AccessVBACentral/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
