In that case, put in MSDE. It is free and is a huge step up from Access. You can utilize stored procedures, security, etc., etc. And, keep Access as the front end. Life would be easier for all concerned that way.
________________________________ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Bryant Sent: Tuesday, July 19, 2005 11:43 AM To: [email protected] Subject: Re: [Access VBA Central] Different results from DAO and ADO recordsets I won't argue that point. But better does not always equal affordable when your client is a struggling nonprofit -- considering the cost of implementation PLUS rewriting. One of the reasons to use ADO is for that day a y ear or so down the road when they're ready to make that step, thought I'd make it less of a financial burden spread out plus having other immediate benefits. Bryant --- In [email protected], "Roy Forkner" <[EMAIL PROTECTED]> wrote: > That is what we run into here. However, we are moving a great many > tables to SQL and are trying to limit Access development. > > Showing how much faster, more secure, etc SQL Server is over Access has > changed some minds here. > > > > > > ________________________________ > > From: [email protected] > [mailto:[EMAIL PROTECTED] On Behalf Of Bryant > Sent: Tuesday, July 19, 2005 11:33 AM > To: [email protected] > Subject: Re: [Access VBA Central] Different results from DAO and ADO > recordsets > > > > Because that's not a realistic scenario for the clients :) > > Bryant > > --- In [email protected], "Roy Forkner" <[EMAIL PROTECTED]> > wrote: > > Just a question; why don't you migrate the data to SQL Server and use > VB > > or VB.Net as the front end? > > > > Roy > > > > > > > > > > > > ________________________________ > > > > From: [email protected] > > [mailto:[EMAIL PROTECTED] On Behalf Of Bryant > > Sent: Tuesday, July 19, 2005 10:46 AM > > To: [email protected] > > Subject: Re: [Access VBA Central] Different results from DAO and ADO > > recordsets > > > > > > > > I found out today that some users are still on Access 2000, when > > earlier I was told everyone was on 2003 ... guess that was too > > difficult of a question for them to answer (grrr). So for the things > > that ARE working in ADO, I have to make sure I set the right reference > > to ADO version, to make sure users of both version are able to open > > the database (several functions fire on opening). > > > > SO for right now I'm having to contend with that, and guess I'll worry > > about other things later as I have time. > > > > Thanks to one and all. > > > > Bryant > > > > --- In [email protected], "owen_group_profile" > > <[EMAIL PROTECTED]> wrote: > > > 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 > > > > > > > > * 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] 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/
