In your ado, notice that there is no space between ='
Copy the following and have strSQL = (the following) and run and see if it works. If so, you will know that the format of the sql statement was in error. Or you can copy what the result of strSQL and put it in the sql design and see have it return a record. Then copy from the SQL design and (in your code after the current strSQL = put this in Strsql = "SELECT LateFeesCharged FROM qselTransactions_LateFeesCharged WHERE CompanyID = 'GMDC' AND TenantID = 'BABET01'" I hope I made myself clear. To expound further, I think that the spacing in your query is incorrect. When you put it in SQL design, the spacing is corrected and returns a value. DAO is not as picky as ADO. HTH, Roy ________________________________ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Bryant Sent: Monday, July 18, 2005 4:13 PM To: [email protected] Subject: Re: [Access VBA Central] Different results from DAO and ADO recordsets SQL generated in Debug.Print, when using DAO: SELECT LateFeesCharged FROM qselTransactions_LateFeesCharged WHERE CompanyID ='GMDC' AND TenantID ='BABET01' Pasting that into SQL design view of a query returns a row with the proper LateFeesCharged amount. SQL generated in Debug.Print, when using ADO: SELECT LateFeesCharged FROM qselTransactions_LateFeesCharged WHERE CompanyID ='GMDC' AND TenantID ='BABET01' Pasting that into SQL design view of a query also returns a row with the proper LateFeesCharged amount. Connection string in Debug.Print (cnn.ConnectionString): Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\tis\gmdc_tenants.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database=C:\PROGRA~1\COMMON~1\System\SYSTEM.MDW;Jet OLEDB:Registry Path=Software\Microsoft\Office\11.0\Access\Jet\4.0;Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False Connection string from Immediate Window, when entering ?CurrentProject.Connection: Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\tis\gmdc_tenants.mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database=C:\PROGRA~1\COMMON~1\System\SYSTEM.MDW;Jet OLEDB:Registry Path=Software\Microsoft\Office\11.0\Access\Jet\4.0;Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False HTH, Bryant --- In [email protected], "Roy Forkner" <[EMAIL PROTECTED]> wrote: > 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] 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/
