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