Check if Stored Procedure already exists in Access Database?

[Carl Fenley]

I am successfully adding stored procedures to an Access database. However,
I need to be able to check if the stored procedure of the same name already
exists.

Is there a way to do this other than waiting for the OleDbException caused
when adding one that already exists?

Here is the code snippet:

Private Sub CreateStoredPro cedures()
Dim alSql As New ArrayList

alSql.Add("CREA TE PROC usp_ProjectResu ltsByID(inID VARCHAR(50)) AS SELECT
* FROM qryResults WHERE ID = inID")

If dbConnection Is Nothing Then
dbConnection = New OleDbConnection (connectionStri ng)
dbCommand = New OleDbCommand
End If

dbCommand.Conne ction = dbConnection
dbConnection.Op en()

Dim i As Integer
For i = 0 To alSql.Count - 1
dbCommand.Comma ndText = DirectCast(alSq l(i), String)
dbCommand.Execu teNonQuery()
Next
dbConnection.Cl ose()
End Sub

Any help is greatly appreciated.

carl

[Tom Dacon]

I don't know how you'd do it in Access, but I can show you what the SQL
would look like if you were doing it in SQL Server 2000. From this hint,
maybe you can snoop around in the system tables in the access database and
compose something similar. To check for a stored procedure named, for
instance, my_procedure, we'd do something like this:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[my_procedure]') and OBJECTPROPERTY( id, N'IsProcedure') =
1)

begin

end

GO

"Carl Fenley" <[e-mail]> wrote in message
news:eY******** *****@TK2MSFTNG P09.phx.gbl...

I am successfully adding stored procedures to an Access database. However, I need to be able to check if the stored procedure of the same name already exists.

Is there a way to do this other than waiting for the OleDbException caused
when adding one that already exists?

Here is the code snippet:

Private Sub CreateStoredPro cedures()
Dim alSql As New ArrayList

alSql.Add("CREA TE PROC usp_ProjectResu ltsByID(inID VARCHAR(50)) AS SELECT * FROM qryResults WHERE ID = inID")

If dbConnection Is Nothing Then
dbConnection = New OleDbConnection (connectionStri ng)
dbCommand = New OleDbCommand
End If

dbCommand.Conne ction = dbConnection
dbConnection.Op en()

Dim i As Integer
For i = 0 To alSql.Count - 1
dbCommand.Comma ndText = DirectCast(alSq l(i), String)
dbCommand.Execu teNonQuery()
Next
dbConnection.Cl ose()
End Sub

Any help is greatly appreciated.

carl

[Ken Tucker [MVP]]

Hi,

List access stored procedures
http://www.windowsformsdatagridhelp....3-528cf4c49e94

Ken
--------------------
"Carl Fenley" <[e-mail]> wrote in message
news:eY******** *****@TK2MSFTNG P09.phx.gbl...
I am successfully adding stored procedures to an Access database. However,
I need to be able to check if the stored procedure of the same name already
exists.

Is there a way to do this other than waiting for the OleDbException caused
when adding one that already exists?

Here is the code snippet:

Private Sub CreateStoredPro cedures()
Dim alSql As New ArrayList

alSql.Add("CREA TE PROC usp_ProjectResu ltsByID(inID VARCHAR(50)) AS SELECT
* FROM qryResults WHERE ID = inID")

If dbConnection Is Nothing Then
dbConnection = New OleDbConnection (connectionStri ng)
dbCommand = New OleDbCommand
End If

dbCommand.Conne ction = dbConnection
dbConnection.Op en()

Dim i As Integer
For i = 0 To alSql.Count - 1
dbCommand.Comma ndText = DirectCast(alSq l(i), String)
dbCommand.Execu teNonQuery()
Next
dbConnection.Cl ose()
End Sub

Any help is greatly appreciated.

carl

[Armin Zingler]

"Carl Fenley" <[e-mail]> schrieb

I am successfully adding stored procedures to an Access database. However,
I need to be able to check if the stored procedure of the
same name already exists.

Please note that there is a group for language unrelated but ADO.Net related
questions:
microsoft.publi c.dotnet.framew ork.adonet

Armin

[Cor Ligthert]

Armin,

Did you see that site of two guys both very active in this newsgroup.

(There is nothing wrong of course telling that there is an AdoNet newsgroup,
than I show our site as well there when there are this kind of questions)

:-)

Cor

[Armin Zingler]

"Cor Ligthert" <no************ @planet.nl> schrieb

Armin,

Did you see that site of two guys both very active in this
newsgroup.

(There is nothing wrong of course telling that there is an AdoNet
newsgroup, than I show our site as well there when there are this
kind of questions)

:-)

One hint just like the others.

Armin

[Paul Clement]

On Wed, 29 Jun 2005 12:44:49 -0700, "Carl Fenley" <[e-mail]> wrote:

¤ I am successfully adding stored procedures to an Access database. However,
¤ I need to be able to check if the stored procedure of the same name already
¤ exists.
¤
¤ Is there a way to do this other than waiting for the OleDbException caused
¤ when adding one that already exists?

Yes. You can use GetOleDbSchemaT able. I don't recommend using the Access system tables.

Dim DatabaseConnect ion As New System.Data.Ole Db.OleDbConnect ion
Dim SchemaTable As DataTable

DatabaseConnect ion.ConnectionS tring = "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=E:\My Documents\db1.m db"

DatabaseConnect ion.Open()

'Use one of the two statements below depending upon the type of QueryDef

SchemaTable =
DatabaseConnect ion.GetOleDbSch emaTable(System .Data.OleDb.Ole DbSchemaGuid.Pr ocedures, _
New Object() {Nothing, Nothing, "qupdtUpdateTab le"})

'SchemaTable =
DatabaseConnect ion.GetOleDbSch emaTable(System .Data.OleDb.Ole DbSchemaGuid.Vi ews, _
' New Object() {Nothing, Nothing, "qyrQueryTable" })

If SchemaTable.Row s.Count <> 0 Then
Console.WriteLi ne("Table " & SchemaTable.Row s(0)!NAME.ToStr ing & " Exists")
Else
Console.WriteLi ne("Table does not exist")
End If

DatabaseConnect ion.Close()
Paul
~~~~
Microsoft MVP (Visual Basic)

[Ross Presser]

On Wed, 29 Jun 2005 22:02:50 -0400, Ken Tucker [MVP] wrote:

Hi,

List access stored procedures
http://www.windowsformsdatagridhelp....3-528cf4c49e94

Ken

Site looks pretty bad in Firefox, if you care

[Cor Ligthert]

Ross,

Because that it is a Net development side is every non IE compatible part
even deleted.

However there is not so much JavaScript in it, so we can think about
including it again.

Cor

[Cor Ligthert]

Ross,

I forgot it almost, thanks for making us attent on this.

Cor