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 CreateStoredProcedures()
Dim alSql As New ArrayList

alSql.Add("CREATE PROC usp_ProjectResultsByID(inID VARCHAR(50)) AS SELECT
* FROM qryResults WHERE ID = inID")

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

dbCommand.Connection = dbConnection
dbConnection.Open()

Dim i As Integer
For i = 0 To alSql.Count - 1
dbCommand.CommandText = DirectCast(alSql(i), String)
dbCommand.ExecuteNonQuery()
Next
dbConnection.Close()
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

 

[Ken Tucker [MVP]]

Hi,

List access stored procedures
http://www.windowsformsdatagridhelp.info/default.aspx?ID=9c2ca38c-92c1-410f-b753-528cf4c49e94

Ken
--------------------
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 CreateStoredProcedures()
Dim alSql As New ArrayList

alSql.Add("CREATE PROC usp_ProjectResultsByID(inID VARCHAR(50)) AS SELECT
* FROM qryResults WHERE ID = inID")

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

dbCommand.Connection = dbConnection
dbConnection.Open()

Dim i As Integer
For i = 0 To alSql.Count - 1
dbCommand.CommandText = DirectCast(alSql(i), String)
dbCommand.ExecuteNonQuery()
Next
dbConnection.Close()
End Sub

Any help is greatly appreciated.

carl

 

[Armin Zingler]

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.public.dotnet.framework.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]

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]

¤ 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 GetOleDbSchemaTable. I don't recommend using the Access system tables.

Dim DatabaseConnection As New System.Data.OleDb.OleDbConnection
Dim SchemaTable As DataTable

DatabaseConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\My Documents\db1.mdb"

DatabaseConnection.Open()

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

SchemaTable =
DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Procedures, _
New Object() {Nothing, Nothing, "qupdtUpdateTable"})

'SchemaTable =
DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Views, _
' New Object() {Nothing, Nothing, "qyrQueryTable"})

If SchemaTable.Rows.Count <> 0 Then
Console.WriteLine("Table " & SchemaTable.Rows(0)!NAME.ToString & " Exists")
Else
Console.WriteLine("Table does not exist")
End If

DatabaseConnection.Close()


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Ross Presser]

Hi,

List access stored procedures
http://www.windowsformsdatagridhelp.info/default.aspx?ID=9c2ca38c-92c1-410f-b753-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