What I am saying is when you do the debug,print command and copy and paste to SQL design view, the wizard may be correcting space problems.
If you posted directly from the debug.print I did not see it. I assume the connection is correct or you would most likely get an error. That said, I would do a debug.print on the cnn.connectiontext and make sure it is correct. >From my time (in hell) working with Access, I found that ADO was much easier to use and always returned exactly what it asked for. If the query is not formed correctly you will not get the correct result. You need to make sure that there are spaces, in the query, where there should be spaces. Etc. HTH, Roy ________________________________ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Bryant Sent: Monday, July 18, 2005 3:32 PM To: [email protected] Subject: Re: [Access VBA Central] Different results from DAO and ADO recordsets My connection is set to CurrentProject.Connection: Set cnn = CurrentProject.Connection I'm not sure how that could be an issue, as no string is being defined? Also not sure what you're wanting, in terms of seeing SQL, beyond what I've already posted? I have entered a Debug.Print command, and reviewed the SQL that is produced by concatening strSQL, and verified that the SQL portion is identical when executed by DAO or ADO, and that when pasted into SQL design view of a query, it works. Is this what you referred to? FWIW I'm not doing ADO and DAO together. I'm moving my database code to ADO, and it only makes sense to compare whether or not identical outcomes are being produced by each, before giving my users a live front-end that doesn't work the same. At present I'm making an ADO version of existing DAO functions, comparing outcomes, and if it works properly commenting out DAO. In 90% of the occasions, it's worked fantastic. Thanks to all who are contributing! Bryant --- In [email protected], "Roy Forkner" <[EMAIL PROTECTED]> wrote: > 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 * 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/
