[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

ADO equivalet of DAP docmd.setwarnings?

Posted on 2006-07-08
9
Medium Priority
?
247 Views
Last Modified: 2008-02-26
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
Comment
Question by:Gite
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 85
ID: 17066142
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
 

Author Comment

by:Gite
ID: 17066577
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
 

Author Comment

by:Gite
ID: 17066609
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Gite
ID: 17066649
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
 
LVL 85
ID: 17067452
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
 

Author Comment

by:Gite
ID: 17067553
Why?
Don' forget, my query is already very complex and includes many subqueries.
0
 
LVL 85
ID: 17067732
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
 
LVL 27

Expert Comment

by:jjafferr
ID: 17461871
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
 

Accepted Solution

by:
ee_ai_construct earned 0 total points
ID: 17489784
PAQ
ee ai construct, community support moderator
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question