Solved

ADO equivalet of DAP docmd.setwarnings?

Posted on 2006-07-08
9
238 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

911 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

20 Experts available now in Live!

Get 1:1 Help Now