Hi, I still tend to use DAO instead of ADO, but I was wondering about the properties of the connection string: is it possible to get that auto-generated, or does it all need to be typed out in full each time?
Also, if you've upgraded your MS Jet to 4.7, say, then will the connection string still work if it reads '4.0', or do you need to monify all the existing databases? Thanks, Owen --- In [email protected], "Bryant" <[EMAIL PROTECTED]> wrote: > 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/
