[AccessD] Update Query - Need to Speed It Up

Mark:

Thanks for the snippet.  I know you're right about MSDE or SQL.  If this
business of my client takes off we'll have to move there eventually.  Right
now it's single user so the delete and re-create field approach will be
okay.

You're monitoring the list.  Are you back in the Access business?  Have you
become a phenomenally wealthy and powerful software entrepreneur?  Staying
away from China these days? How's the little girl?  (excuse the OT, please)

Rocky

----- Original Message ----- 
From: "Mark L. Breen" <subs at solution-providers.ie>
To: <accessd at databaseadvisors.com>
Sent: Tuesday, June 03, 2003 1:55 AM
Subject: Re: [AccessD] Update Query - Need to Speed It Up


> Hello Rocky,
>
> Here is a snipit of code I wrote a few years ago that appends default
values
> amongst other things.  All I can say is 'it works', it was A97 but as far
as
> I know it also works in A2K.
>
> I know that you do not want to hear it, but I have terrible problems with
> databases over 1M records.  You have to consider compacting and rebuilding
> regularly, and I often got databases that would just go corrupt.  A backup
> from yesterday evening sometimes does not cut it.  A one user, one machine
> system may work fine, but that same db now has put 18 M records through
over
> three years on SQL 7 / MSDE 1.0 and it has never ever crashed and
> performance it still tip top.  You can not agrue with that.
>
> The dropping and re-creating is quick but consider the compact and rebuild
> regularly.
>
> On the other hand, it is much more fun that a little db with only a few
> hundred records, even if you make more money on the small app.
>
> Enjoy,
>
> Mark
>
> ...snip
>
> Set fld = tdf.CreateField(strFieldName)
>
> fld.Type = intDataType
>
> If intDataType = dbText Or intDataType = dbNumeric Then
>     fld.Size = intDataSize
> End If
>
> If Right(fld.Name, 3) = "_ID" Then
>     fld.Attributes = dbAutoIncrField
> End If
>
> If Len(strDefaultValue & "") > 0 Then
>     fld.DefaultValue = strDefaultValue
> End If
>
> If strReq = "y" Then
>     fld.Required = True
> End If
>
> tdf.Fields.Append fld
>
> ...end snip
>
>
>
>
> ----- Original Message -----
> From: "Rocky Smolin - Beach Access Software" <bchacc at san.rr.com>
> To: <accessd at databaseadvisors.com>
> Sent: Tuesday, June 03, 2003 6:51 AM
> Subject: Re: [AccessD] Update Query - Need to Speed It Up
>
>
> > Henry:
> >
> > I'm trying to implement this method but don't see the way to set the
> default
> > value using the CreateField method.  Is there another way?
> >
> > I tried:
> >
> > Set fld = tdf.CreateField("fldNew", dbText, 44).DefaultValue = ""
> > tdf.Fields.Append fld
> > Set fld = tdf.CreateField("fldNew2", dbDouble).DefaultValue = 0
> > tdf.Fields.Append fld
> >
> > which works without the .DefaultValue clause.  WQIth the cluase I get an
> > error 'Object Required'.
> >
> > Thanks and regards,
> >
> > Rocky
> >
> > ----- Original Message -----
> > From: "Henry Simpson" <hsimpson88 at hotmail.com>
> > To: <accessd at databaseadvisors.com>
> > Sent: Monday, June 02, 2003 4:31 PM
> > Subject: Re: [AccessD] Update Query - Need to Speed It Up
> >
> >
> > > Delete the fields and add them back.  Default value "" and 0.
> > >
> > > Hen
> > >
> > >
> > > >From: "Rocky Smolin - Beach Access Software" <bchacc at san.rr.com>
> > > >Reply-To: accessd at databaseadvisors.com
> > > >To: <AccessD at databaseadvisors.com>
> > > >Subject: [AccessD] Update Query - Need to Speed It Up
> > > >Date: Mon, 2 Jun 2003 13:47:27 -0700
> > > >
> > > >Dear List:
> > > >
> > > >I am running an update query on a table with 2,500,000 records and
it's
> > > >taking just a bit too long for the my and the client's liking.
> > > >The SQL is:
> > > >
> > > >UPDATE tblInvoiceDetail SET tblInvoiceDetail.fldInvoiceDetailPPV =
> """",
> > > >tblInvoiceDetail.fldInvoiceDetailVariancePercent = 0;
> > > >
> > > >It's setting one field to blank the other to 0.
> > > >
> > > >Is there a way to make it run any faster?
> > > >
> > > >
> > > >MTIA,
> > > >
> > > >Rocky Smolin
> > > >Beach Access Software
> > > >
> > > >_______________________________________________
> > > >AccessD mailing list
> > > >AccessD at databaseadvisors.com
> > > >http://databaseadvisors.com/mailman/listinfo/accessd
> > > >Website: http://www.databaseadvisors.com
> > >
> > > _________________________________________________________________
> > > The new MSN 8: advanced junk mail protection and 2 months FREE*
> > > http://join.msn.com/?page=features/junkmail
> > >
> > > _______________________________________________
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> >
> >
> > _______________________________________________
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com