Solved

ADO equivalet of DAP docmd.setwarnings?

Posted on 2006-07-08
9
237 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
9 Comments
 
LVL 84
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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 84
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 84
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

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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Display multiple images in report 12 85
Sum Max help (Add 1 Month) 3 19
Help with SQl and UNION 3 19
Filtered index 5 0
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now