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]
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/
