[AccessD] (no subject)

Thanks Paul

I really appreciate the advice

Have a great day

Darren



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Paul Strauss
Sent: Tuesday, 7 September 2004 12:01 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] (no subject)


At 9/6/2004 12:30 AM, you wrote:
>Darren,
>
>I wouldn't set it up like this. Try something like this:
>
>     Dim dbs As dao.Database
>     Dim rstDataToAppend As dao.Recordset
>     Dim rstTableToAppendTo As dao.Recordset
>     Dim strSQLDataForAppend As String
>
>     strSQLDataForAppend = "SELECT .....Whatever...."
>
>
>     Set dbs = CurrentDB()
>     Set rstDataToAppend = dbs.OpenRecordset(strSQLDataForAppend,
>dbOpenDynaset)
>     Set rstTableToAppendTo = 
>dbs.OpenRecordset("tblYourTableToAppendTo",
>dbOpenDynaset)
>    ...snip...

Any time you can do table manipulations with SQL, rather that DAO, you 
should choose SQL. DAO is much slower than doing it with SQL. Sure, there 
are times when only DAO will do, so I am not saying never, but I would 
always strive to use SQL.

If the task at hand is to eat a handful of M&M's, there are two ways to do 
it 1) one-by-one and 2) shove them all in your mouth and chew. Access can 
chew up the mouthful and swallow it much faster than it can eat them one at 
a time. That's just the way it has been built and optimized.

>A good idea is to also check whether the recordset has any data before 
>hand, otheriwse this will throw up an error. ...snip...

I agree it a good idea. I've never seemed to need to append from one table 
to another using a Select inside the Insert, so I am not sure what would 
happen if the Select didn't return any rows. If it returns a trappable 
error, then I'd just attempt the Insert and handle this case in the error 
handler. That would be the fastest way to do it. If a trappable error isn't 
returned, then opening a recordset with  "Select Count(*) ..." and checking 
that at least one row is returned before doing the Insert would still be 
faster than doing it the DAO way in a loop. It may also be the case that 
nothing harmful happens (it is a null operation) and so you don't have to 
worry. But since you want to do it, and I don't, I'll let you figure out as 
a "homework" assignment what happens :). Unless I were sure that I always 
had something to append, I'd lose a good bit a sleep wondering if this 
would cause my program to blow up. Since I need my sleep, I'd test it.


Regards,
Paul Strauss


----------
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com