Bobby Heid
bheid at appdevgrp.com
Thu Jul 1 06:08:22 CDT 2004
Thanks for the reply.
I deleted the indexes because I thought that they might be slowing it down
with so many records being updated.
There are usually between 5000 and 50000 payroll records. But even on the
other larger databases, it did not take very long.
Thanks,
Bobby
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Wednesday, June 30, 2004 4:57 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: Updated subject: Query failing to execute WAS: RE: [AccessD]
Combobox question UPDATE
Plus, I neglected to mention that "MyTempQuery" is the name of an already
existing query in the database.
Lambert
> -----Original Message-----
> From: Heenan, Lambert [SMTP:Lambert.Heenan at aig.com]
> Sent: Wednesday, June 30, 2004 4:44 PM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: Updated subject: Query failing to execute WAS: RE:
> [AccessD] Combobox question UPDATE
>
> My 2 cents worth.
>
> 1/ Removing the indexes is a sure fire way to slow things down. 2/ If
> this is running on a JET database then executing the SQL string
> directly does not give the JET engine a chance to optimize the query.
> Better to plug the SQL into a querydef and the run the query, as you
> discovered. How to do that in code?...
>
> Dim Qd as QueryDef
> Dim sSQL as String
>
> sSQL = "UPDATE Contract INNER JOIN [Payroll Line Items] ON
> Contract.[Contract ID] = [Payroll Line Items].Contract ID] SET
> [Payroll Line Items].PolicyID = [EnforceWCID] WHERE [Payroll Line
> Items].CostType='WC' AND
> Contract.EnforceWCID<>0;"
>
> Set Qd = CurrentDb.QueryDefs("MyTempQuery")
> Qd.SQL = sSQL
> Set Qd = Nothing
> Docmd.OpenQuery "MyTempQuery"
>
> 3/ How many records were involved with the other databases?
>
> Lambert
>
> > -----Original Message-----
> > From: Bobby Heid [SMTP:bheid at appdevgrp.com]
> > Sent: Wednesday, June 30, 2004 3:31 PM
> > To: 'Access Developers discussion and problem solving'
> > Subject: RE: Updated subject: Query failing to execute WAS: RE:
> > [AccessD] Combobox question UPDATE
> >
> > I let it run during lunch and it finally did get through the query.
> > It updated about 48000 records.
> >
> > I still do not know why it is taking so long. I even took off
> > almost
> all
> > of
> > the indexes of the payroll table and it still took a very long time.
> >
> > Any ideas?
> >
> > Thanks,
> > Bobby
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bobby
> > Heid
> > Sent: Wednesday, June 30, 2004 11:41 AM
> > To: 'Access Developers discussion and problem solving'
> > Subject: Updated subject: Query failing to execute WAS: RE: [AccessD]
> > Combobox question
> >
> >
> > Sorry about that. I forgot to change the subject.
> >
> > Bobby
> >
> >
> > -----Original Message-----
> > From: Bobby Heid [mailto:bheid at appdevgrp.com]
> > Sent: Wednesday, June 30, 2004 11:40 AM
> > To: 'Access Developers discussion and problem solving'
> > Subject: RE: [AccessD] Combo box question
> >
> >
> > Hi all,
> >
> > I have a query that is run during a conversion of a database that
> appears
> > to
> > be hanging on this one particular database.
> >
> > Here's the query:
> > UPDATE Contract
> > INNER JOIN [Payroll Line Items]
> > ON Contract.[Contract ID] = [Payroll Line Items].[Contract
ID]
> > SET [Payroll Line Items].PolicyID = [EnforceWCID]
> > WHERE [Payroll Line Items].CostType='WC'
> > AND Contract.EnforceWCID<>0;
> >
> > This query is placed into a string and executed with: db.execute
> > strSQL,dbfailonerror
> >
> > There are ~192,000 payroll records and ~3500 contract records.
> >
> > If I copy the SQL into the query designer and run it, it only takes
> about
> > 5
> > seconds or so to execute. I have let the version in the code run
> > for
> over
> > 1/2 hour and have to end up killing access.
> >
> > Anyone have any ideas as to what may be happening? I am running AXP
> sp2.
> > The tables are linked. This code has ran fine on over 100 other
> > conversions.
> >
> > Thanks,
> > Bobby
> >
> > --
> > _______________________________________________
> > 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
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com