Ignoring error message (1 Viewer)

[eyau1981]

Hello all,

I have an interesting issue for you to think about.

I am developing a small-scale document management system that I am developing with Access and SQL server, and I'm trying to get round the following issue:

I have a 'Document' table with an ON CREATE trigger that creates tables in various other databases with the following code:

IF OBJECT_ID ('dbo.'+@docID, 'U') IS NULL

BEGIN
CREATE TABLE [dbo].[newDocumentType](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Pattern] [nvarchar](100) NOT NULL,
[Replacement] [nvarchar](100) NOT NULL,
[UseWildcards] [bit] NULL DEFAULT ((0)),
[CaseSensitive] [bit] NULL DEFAULT ((1)),
[Style] [nvarchar](20) NULL
) ON [PRIMARY]

EXEC sp_rename [newDocumentType], @docID
END

This works fine, although I get a warning "Caution: Changing any part of an object name could break scripts and stored procedures", which is normal.

The front end of the system, for legacy reasons, is in MS Access.
Everything is fine when I create a 'document' through Access.

But I figure if I am creating lots of these I want a batch-import function that runs a StoredProc with an INSERT on the 'Document' table. This StoredProc runs fine from Management Studio. The problem I have is that Access seems to be stopping after it gets this warning message and I can't get it to carry on.

I have tried the DoCmd, but that doesn't really seem to help.
When I use that, my code will run over the list of items I am trying to INSERT, but it won't finish running the StoredProc.

From what I have read, this is a level 10 error, so SQL Server treats it as a non-fatal error, hence why it works in Management Studio.

I would be grateful for any helpful insights into how to get this working, as I am really stuck!

Many thanks,
Ed

 

[MarkK]

Hey, welcome to the forum.
It'd be nice to help you out, but a few things aren't that clear to me.
1) Are you working in an .adp, .mdb, or .accdb file?
2) I don't understand your desciptions of the problem...for instance...
- a) What does it mean 'that Access seems to be stopping'?
- b) You say you've 'tried the DoCmd,' but the DoCmd object provides about 60 functions...
- c) You say your INSERT isn't working, but the SQL you've posted is not an INSERT query.
Maybe post some code and indicate which lines fail with what errors.
Cheers,

 

[eyau1981]

Hello,

Sorry if I missed a few details off, I posted in a bit of a hurry after a long day.
I didn't want to paste too much code.

1) I am working in an .adp file
2)
a) Access stops halfway though the StoredProc and takes me to the VBA line that calls it. The line that calls the StoredProc starts as "CurrentProject.Connection.createNewDocType", which runs in a loop for the items I am trying to add.

b) I meant I tried "DoCmd.SetWarnings False" but it still stops in the StoredProc, just it carries on around the loop to the next item.

c) The storedProc does an INSERT, but the table that the INSERT runs on has a trigger that runs the code that I pasted in.

The error I get is the following:
"Run-time error '-2147217900 (80040e14)':
Caution: Changing any part of an object name could break scripts and stored procedures".

 

[MarkK]

Sorry, but from your descriptions I still don't really understand, and I'm not that well versed in SQL Server either. I think posting code, both VBA and SQL, would be much clearer but it's up to you.
The thing is if this operation raises an error in VBA it does and as long as that is the case the line will not execute. There is not a mechanism in VBA to force the code to run anyway, regardless if the same operation executes in Management Studio.
Cheers,

 

[petehilljnr]

You could perhaps try capturing that error number and resuming next?

e.g.

On Error Goto ErrHandler
 
.... Your Code
 
Exit Sub
 
ErrHandler:
 
Select Case Err.Number
     Case -2147217900 
          Resume Next
     Case Else
          Msgbox Err.Description
End Select
 
Exit Sub

Just a guess
Pete