Max Wanadoo
max.wanadoo at gmail.com
Mon Mar 9 02:17:23 CDT 2009
Yes, that is true. For inserts it can be more cumbersome that what it is worth (but I have found that if you line all your ducks up so that all text fields come first in the params list and longs follow along (!) behind then it is a teensy bit easier to construct. However, update are pretty straight forward. I also find that with currentdb.execute (sql) save all that opening/closing of database objects etc And..like you, I do then to restrict it to single record updates. Anything other then it is DAO for moi. Max -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: 09 March 2009 00:33 To: Access Developers discussion and problem solving Subject: Re: [AccessD] ...building, managing, and using a page favorites list Max, It is just one line but you have to assemble a SQL statement on the fly, which if you parse it out to be readable turns into a bunch of lines also. The "block of code" is immanently readable already. Anyone who knows dao can just read the table that is being modified, the field names and so forth. The bigger issue in this case though is that you are doing an append for each control that changed. While that may only be 1 control, it could be 20. Don't get me wrong, I do both, but I only do the SQL version when it is one record. John W. Colby www.ColbyConsulting.com Max Wanadoo wrote: > Thanks Gustav, I guess it is the opening of the recordset which takes the > time and by holding it open in a loop it will beat the sql statement every > time. But nice to get this confirmed. > > As you say, for one record it makes no difference. I *like* the sql because > it is just a one/two liner and not a block of code. > > Max > > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock > Sent: 08 March 2009 17:19 > To: accessd at databaseadvisors.com > Subject: Re: [AccessD] ...building, managing, and using a page favorites > list > > Hi Max > > No. Working with the recordset directly via DAO is always fastest - but many > forget or carry over their bad habits from VB or ASP where you see so much > SQL command stuff and the like, because this once was the only - or at least > the easiest - method available in those environments. > > Of course, for a single record inserted, difference will not be big. But can > you keep the recordset open, one insert is very speedy. > > /gustav > >>>> max.wanadoo at gmail.com 08-03-2009 17:24:53 >>> >> This speeds things up by keeping the recordset open the whole time. > > Would it be quicked to use an SQL statement ie, > Sql = "Insert into tblAudit fields(x,y,z) values (a,b,c) > Currentdb.execute(sql) > > Rather than opening recordsets. I don't know, so i am really asking the > question > > Thanks > > Max > > > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com