[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
>