Seeking Advice

[NEWER USER]

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

 

[Dorian]

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".

 

[John W. Vinson]

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

 

[NEWER USER]

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.

 

[Jeff Boyce]

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.

 

[John W. Vinson]

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.

 

[NEWER USER]

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.

 

[NEWER USER]

Thank you for the explanation. I will use your advice and make the necessary
changes. Thanks again.