Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

ADO equivalet of DAP docmd.setwarnings?

I want to insert rows into an Acess table.

In DAO, I don't have to bother checking if some of the rows were already there: I only had to use docmd.setwarnings=false before the docmd.runSQL.

Now, I vave to use ADO for many reasons. When I execute the command, I get an error 80004005 saying that because some of the rows are already there the inserts are not made.

Because the query is already very complex, I would rather not elminiate myselg those double rows.

Is there any property or method I can use on the connection or the command to force ADO to insert the new rows and forget about the others?
0
Gite
Asked:
Gite
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Just trap the error with your error handler and, if you want to just ignore the error, issue a Resume Next:

Function SomeFunction() As Boolean

Dim xxx as yyyy
Dim zzz as aaa

On Error GoTo Err_SomeFunction

<code here>

Exit_SomeFunction:
Exit Function

Err_SomeFunction
  Select Case Err.Number
    Case 100
      Msgbox "Oh no, Error 100 occurred!!!"
      Resume Exit_SomeFunction
    Case YourErrorNumber
      Resume Next
   End Select
End Function
0
 
GiteAuthor Commented:
Remember: I want that, among the rows I try to insert:
-new rows are inserted
-existing rows remain unchanged.

I thought that getting the error condition would have the consequence that none ot the rows I was trying to insert would be inserted.

Your solution assumes that new rows are inserted even if I get the error condition. Are you sure?
0
 
GiteAuthor Commented:
I just ran a simple test to validate your assumption.
It shows that NONE of the rows are inserted if the error condition arises.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
GiteAuthor Commented:
I finally fourd myself the solution in:
http://support.microsoft.com/default.aspx?scid=kb;en;293823

I tested it and it works.

To say the least, that is not a much publicized feature!

If that can help others...
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Glad you found the answer. You would be FAR better off, however much trouble it may be, to correctly configure your SQL statement to manipulate only those rows you desire.
0
 
GiteAuthor Commented:
Why?
Don' forget, my query is already very complex and includes many subqueries.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Several reasons

Performance: Including rows in your data manipulation which are not either (a) used by the query or (b) directly manipulated by the query means additional, unneeded overhead. Query processing is possibly one of the slowest processes in Access and anything you can do to speed it up is a good thing especially if this is used over a network or for a webpage.

Growth: I don't know what your app does or how it's used, but in my experience apps rarely stay the same - they tend to grow, and features are added, as is more information (i.e. you add columns to your tables as you realize you need to store more information about your subject). If your queries include unneeded rows (or data columns, for that matter) then reviewing your db for changes becomes much, much more difficult (believe me - in 6 months you'll forget why you added that column to that query, or why those rows are being returned by that query <g>).

Maintainability: When your boss tells you "We need to see xxx in that report", and you have to make changes to the underlying query, the level of complexity of your query can make it very difficult to determine exactly what changes need to be made down the road, espeically if someone else has been tasked to do the changes. Including rows which have no bearing on the outcome of the query, and which are not used by the query, unnecessarily increase the complexity and decrease the "readability" of your code.

But the biggest one is ...

Data Integrity: Including rows in action queries which do are not used by the query, or are not directly affected by the action of the query, is a recipe for disaster, since seemingly innocuous changes made in other areas of the program could change that dynamic at some point. When your application is new (and small) it's pretty easy to "see" those changes; as your app grows (and it will grow, if it's used) the effects of those changes become more and more difficult to see.

I'm not saying all these reasons directly impact you - this may be for your own personal use, after all, and you may have no boss to direct you - but in general you should always strive to eliminate rows from your query which do not have a direct impact onthe input or output of the query ...
0
 
jjafferrCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    PAQ with points refunded

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

jjafferr
EE Cleanup Volunteer
0
 
ee_ai_constructCommented:
PAQ
ee ai construct, community support moderator
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now