PC Review


Reply
Thread Tools Rate Thread

Seeking Advice

 
 
NEWER USER
Guest
Posts: n/a
 
      22nd Feb 2010
I have several Update queries that I am running back to back in succession.
Is it better/faster to create the queries and then run the following:

Dim strDoc 1As String
Dim strDoc2 As String

strDoc1 = "Query1"
strDoc2 = "Query2"

DoCmd. OpenQuery strDoc1, acNormal, acEdit
DoCmd. OpenQuery strDoc2, acNormal, acEdit

OR

Dim sql1 As String
Dim sql2 As String

sql1 = "UPDATE tblData ..."
sql2 = "UPDATE tblData ..."

DoCmd.RunSQL sql1
DoCmd.RunSQL sql2

OR Just type

DoCmd.RunSQL "UPDATE tblData ..."

Looking for ther proper method. Thanks
 
Reply With Quote
 
 
 
 
Dorian
Guest
Posts: n/a
 
      22nd Feb 2010
It's better to use prebuilt queries.

If you are doing operations that return no recordset, I'd use the syntax:
CurrentProject.Connection.Execute "Query1", , adExecuteNoRecords
CurrentProject.Connection.Execute "Query2", , adExecuteNoRecords

Make sure you have error trapping on in your procedure (On Error GoTo....)
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"NEWER USER" wrote:

> I have several Update queries that I am running back to back in succession.
> Is it better/faster to create the queries and then run the following:
>
> Dim strDoc 1As String
> Dim strDoc2 As String
>
> strDoc1 = "Query1"
> strDoc2 = "Query2"
>
> DoCmd. OpenQuery strDoc1, acNormal, acEdit
> DoCmd. OpenQuery strDoc2, acNormal, acEdit
>
> OR
>
> Dim sql1 As String
> Dim sql2 As String
>
> sql1 = "UPDATE tblData ..."
> sql2 = "UPDATE tblData ..."
>
> DoCmd.RunSQL sql1
> DoCmd.RunSQL sql2
>
> OR Just type
>
> DoCmd.RunSQL "UPDATE tblData ..."
>
> Looking for ther proper method. Thanks

 
Reply With Quote
 
 
 
 
John W. Vinson
Guest
Posts: n/a
 
      22nd Feb 2010
On Mon, 22 Feb 2010 08:05:01 -0800, NEWER USER
<(E-Mail Removed)> wrote:

>I have several Update queries that I am running back to back in succession.
>Is it better/faster to create the queries and then run the following:
>
>Dim strDoc 1As String
>Dim strDoc2 As String
>
>strDoc1 = "Query1"
>strDoc2 = "Query2"
>
>DoCmd. OpenQuery strDoc1, acNormal, acEdit
>DoCmd. OpenQuery strDoc2, acNormal, acEdit
>
>OR
>
>Dim sql1 As String
>Dim sql2 As String
>
>sql1 = "UPDATE tblData ..."
>sql2 = "UPDATE tblData ..."
>
>DoCmd.RunSQL sql1
>DoCmd.RunSQL sql2
>
>OR Just type
>
>DoCmd.RunSQL "UPDATE tblData ..."
>
>Looking for ther proper method. Thanks


None of the above!

Instead, use the Execute method, which lets you trap errors:


On Error GoTo Proc_Error
CurrentDb.Execute "Query1", dbFailOnError
CurrentDb.Execute "Query2", dbFailOnError
....
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " running update:" & vbCrLf & Err.Description
Resume Proc_Exit
End Sub

--

John W. Vinson [MVP]
 
Reply With Quote
 
NEWER USER
Guest
Posts: n/a
 
      22nd Feb 2010
This method worked fine on the first ten queries and then came the MakeTable
query. I got an error message that the table already existed. I have
Warnings set to False and don't know why/how to get around overwriting an
existing table and avoid the error message.

"John W. Vinson" wrote:

> On Mon, 22 Feb 2010 08:05:01 -0800, NEWER USER
> <(E-Mail Removed)> wrote:
>
> >I have several Update queries that I am running back to back in succession.
> >Is it better/faster to create the queries and then run the following:
> >
> >Dim strDoc 1As String
> >Dim strDoc2 As String
> >
> >strDoc1 = "Query1"
> >strDoc2 = "Query2"
> >
> >DoCmd. OpenQuery strDoc1, acNormal, acEdit
> >DoCmd. OpenQuery strDoc2, acNormal, acEdit
> >
> >OR
> >
> >Dim sql1 As String
> >Dim sql2 As String
> >
> >sql1 = "UPDATE tblData ..."
> >sql2 = "UPDATE tblData ..."
> >
> >DoCmd.RunSQL sql1
> >DoCmd.RunSQL sql2
> >
> >OR Just type
> >
> >DoCmd.RunSQL "UPDATE tblData ..."
> >
> >Looking for ther proper method. Thanks

>
> None of the above!
>
> Instead, use the Execute method, which lets you trap errors:
>
>
> On Error GoTo Proc_Error
> CurrentDb.Execute "Query1", dbFailOnError
> CurrentDb.Execute "Query2", dbFailOnError
> ....
> Proc_Exit:
> Exit Sub
> Proc_Error:
> MsgBox "Error " & Err.Number & " running update:" & vbCrLf & Err.Description
> Resume Proc_Exit
> End Sub
>
> --
>
> John W. Vinson [MVP]
> .
>

 
Reply With Quote
 
Jeff Boyce
Guest
Posts: n/a
 
      23rd Feb 2010
Make table queries MUST over-write an existing table ... if you tell Access
to make a table when that table's name already exists, you are making a NEW
table with the SAME name ... the same as saving a file with the same name.

If you want your new data to go into an old table, you have to append the
data. If there's already data in there, you have to decide whether you are
going to empty the table out before appending, or append the new records to
the list of old ones.

The latter is a common way to avoid getting the 'table exists' message ...
just don't use Make Table!

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


"NEWER USER" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> This method worked fine on the first ten queries and then came the
> MakeTable
> query. I got an error message that the table already existed. I have
> Warnings set to False and don't know why/how to get around overwriting an
> existing table and avoid the error message.
>
> "John W. Vinson" wrote:
>
>> On Mon, 22 Feb 2010 08:05:01 -0800, NEWER USER
>> <(E-Mail Removed)> wrote:
>>
>> >I have several Update queries that I am running back to back in
>> >succession.
>> >Is it better/faster to create the queries and then run the following:
>> >
>> >Dim strDoc 1As String
>> >Dim strDoc2 As String
>> >
>> >strDoc1 = "Query1"
>> >strDoc2 = "Query2"
>> >
>> >DoCmd. OpenQuery strDoc1, acNormal, acEdit
>> >DoCmd. OpenQuery strDoc2, acNormal, acEdit
>> >
>> >OR
>> >
>> >Dim sql1 As String
>> >Dim sql2 As String
>> >
>> >sql1 = "UPDATE tblData ..."
>> >sql2 = "UPDATE tblData ..."
>> >
>> >DoCmd.RunSQL sql1
>> >DoCmd.RunSQL sql2
>> >
>> >OR Just type
>> >
>> >DoCmd.RunSQL "UPDATE tblData ..."
>> >
>> >Looking for ther proper method. Thanks

>>
>> None of the above!
>>
>> Instead, use the Execute method, which lets you trap errors:
>>
>>
>> On Error GoTo Proc_Error
>> CurrentDb.Execute "Query1", dbFailOnError
>> CurrentDb.Execute "Query2", dbFailOnError
>> ....
>> Proc_Exit:
>> Exit Sub
>> Proc_Error:
>> MsgBox "Error " & Err.Number & " running update:" & vbCrLf &
>> Err.Description
>> Resume Proc_Exit
>> End Sub
>>
>> --
>>
>> John W. Vinson [MVP]
>> .
>>



 
Reply With Quote
 
John W. Vinson
Guest
Posts: n/a
 
      23rd Feb 2010
On Mon, 22 Feb 2010 14:58:01 -0800, NEWER USER
<(E-Mail Removed)> wrote:

>This method worked fine on the first ten queries and then came the MakeTable
>query.


You didn't mention a MakeTable query.

> I got an error message that the table already existed. I have
>Warnings set to False and don't know why/how to get around overwriting an
>existing table and avoid the error message.


Well... you're getting an error message (not a warning message) because *you
have a real, genuine error*.

You cannot use a MakeTable query to create a table named MyNewTable if there
already IS a table named MyNewTable. Access won't know what to do! It can't
make a new table with the same name as an existing table (table names must be
unique); it can't (safely) delete the old table before running the new table,
Microsoft would have programmers with pitchforks and torches at the gate if
Access deleted tables without explicit permission!

I would assert that MakeTable queries are *almost never needed*, at least in a
production application. At most I'd have a permanent table for the final
outcome of your query stack; run a Delete * From tablename; query to empty it,
and then use an Append query, rather than a MakeTable query, to repopulate it.
This will let you define your field sizes, indexes, formats, etc. and so on,
which a MakeTable query will not.

If you really must use a MakeTable query, then you must delete the table
itself before running the MakeTable.

--

John W. Vinson [MVP]
 
Reply With Quote
 
NEWER USER
Guest
Posts: n/a
 
      23rd Feb 2010
I understand what you have said. However, if I were to use the DoCmd.Open
Query command rather than Execute and run several back to back queries with
Warnings set to False, the table would be overwritten and NO eroor message
appears. Why with Execute and not OpenQuery.

"Jeff Boyce" wrote:

> Make table queries MUST over-write an existing table ... if you tell Access
> to make a table when that table's name already exists, you are making a NEW
> table with the SAME name ... the same as saving a file with the same name.
>
> If you want your new data to go into an old table, you have to append the
> data. If there's already data in there, you have to decide whether you are
> going to empty the table out before appending, or append the new records to
> the list of old ones.
>
> The latter is a common way to avoid getting the 'table exists' message ...
> just don't use Make Table!
>
> Good luck!
>
> Regards
>
> Jeff Boyce
> Microsoft Access MVP
>
> --
> Disclaimer: This author may have received products and services mentioned
> in this post. Mention and/or description of a product or service herein
> does not constitute endorsement thereof.
>
> Any code or pseudocode included in this post is offered "as is", with no
> guarantee as to suitability.
>
> You can thank the FTC of the USA for making this disclaimer
> possible/necessary.
>
>
> "NEWER USER" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > This method worked fine on the first ten queries and then came the
> > MakeTable
> > query. I got an error message that the table already existed. I have
> > Warnings set to False and don't know why/how to get around overwriting an
> > existing table and avoid the error message.
> >
> > "John W. Vinson" wrote:
> >
> >> On Mon, 22 Feb 2010 08:05:01 -0800, NEWER USER
> >> <(E-Mail Removed)> wrote:
> >>
> >> >I have several Update queries that I am running back to back in
> >> >succession.
> >> >Is it better/faster to create the queries and then run the following:
> >> >
> >> >Dim strDoc 1As String
> >> >Dim strDoc2 As String
> >> >
> >> >strDoc1 = "Query1"
> >> >strDoc2 = "Query2"
> >> >
> >> >DoCmd. OpenQuery strDoc1, acNormal, acEdit
> >> >DoCmd. OpenQuery strDoc2, acNormal, acEdit
> >> >
> >> >OR
> >> >
> >> >Dim sql1 As String
> >> >Dim sql2 As String
> >> >
> >> >sql1 = "UPDATE tblData ..."
> >> >sql2 = "UPDATE tblData ..."
> >> >
> >> >DoCmd.RunSQL sql1
> >> >DoCmd.RunSQL sql2
> >> >
> >> >OR Just type
> >> >
> >> >DoCmd.RunSQL "UPDATE tblData ..."
> >> >
> >> >Looking for ther proper method. Thanks
> >>
> >> None of the above!
> >>
> >> Instead, use the Execute method, which lets you trap errors:
> >>
> >>
> >> On Error GoTo Proc_Error
> >> CurrentDb.Execute "Query1", dbFailOnError
> >> CurrentDb.Execute "Query2", dbFailOnError
> >> ....
> >> Proc_Exit:
> >> Exit Sub
> >> Proc_Error:
> >> MsgBox "Error " & Err.Number & " running update:" & vbCrLf &
> >> Err.Description
> >> Resume Proc_Exit
> >> End Sub
> >>
> >> --
> >>
> >> John W. Vinson [MVP]
> >> .
> >>

>
>
> .
>

 
Reply With Quote
 
NEWER USER
Guest
Posts: n/a
 
      23rd Feb 2010
Thank you for the explanation. I will use your advice and make the necessary
changes. Thanks again.

"John W. Vinson" wrote:

> On Mon, 22 Feb 2010 14:58:01 -0800, NEWER USER
> <(E-Mail Removed)> wrote:
>
> >This method worked fine on the first ten queries and then came the MakeTable
> >query.

>
> You didn't mention a MakeTable query.
>
> > I got an error message that the table already existed. I have
> >Warnings set to False and don't know why/how to get around overwriting an
> >existing table and avoid the error message.

>
> Well... you're getting an error message (not a warning message) because *you
> have a real, genuine error*.
>
> You cannot use a MakeTable query to create a table named MyNewTable if there
> already IS a table named MyNewTable. Access won't know what to do! It can't
> make a new table with the same name as an existing table (table names must be
> unique); it can't (safely) delete the old table before running the new table,
> Microsoft would have programmers with pitchforks and torches at the gate if
> Access deleted tables without explicit permission!
>
> I would assert that MakeTable queries are *almost never needed*, at least in a
> production application. At most I'd have a permanent table for the final
> outcome of your query stack; run a Delete * From tablename; query to empty it,
> and then use an Append query, rather than a MakeTable query, to repopulate it.
> This will let you define your field sizes, indexes, formats, etc. and so on,
> which a MakeTable query will not.
>
> If you really must use a MakeTable query, then you must delete the table
> itself before running the MakeTable.
>
> --
>
> John W. Vinson [MVP]
> .
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
seeking advice Monique Chu Windows XP Basics 4 11th Apr 2004 03:16 PM
Seeking Advice on XP Start Up Issue HippyFish Windows XP Help 1 2nd Mar 2004 07:48 PM
Seeking Advice on Start Up Issue HippyFish Windows XP Performance 1 2nd Mar 2004 02:51 PM
Reformatting; seeking partition advice Mike Trozzo Windows XP Performance 4 15th Jan 2004 12:22 PM
Seeking Advice Layna Windows XP Print / Fax 1 3rd Nov 2003 10:11 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:43 AM.