Oh, the semi colon is not necessary in ADODB
________________________________ 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] 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/
