?
Solved

ADO equivalet of DAP docmd.setwarnings?

Posted on 2006-07-08
9
Medium Priority
?
245 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

771 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