By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,190 Members | 783 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,190 IT Pros & Developers. It's quick & easy.

Check if Stored Procedure already exists in Access Database?

P: n/a
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
Nov 21 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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" <clfenley-X-@-X-wcpci.com> wrote in message
news:eY*************@TK2MSFTNGP09.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 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

Nov 21 '05 #2

P: n/a
Hi,

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

Ken
--------------------
"Carl Fenley" <clfenley-X-@-X-wcpci.com> wrote in message
news:eY*************@TK2MSFTNGP09.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 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

Nov 21 '05 #3

P: n/a
"Carl Fenley" <clfenley-X-@-X-wcpci.com> 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.public.dotnet.framework.adonet

Armin

Nov 21 '05 #4

P: n/a
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
Nov 21 '05 #5

P: n/a
"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
Nov 21 '05 #6

P: n/a
On Wed, 29 Jun 2005 12:44:49 -0700, "Carl Fenley" <clfenley-X-@-X-wcpci.com> 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 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)
Nov 21 '05 #7

P: n/a
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
Nov 21 '05 #8

P: n/a
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
Nov 21 '05 #9

P: n/a
Ross,

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

Cor
Nov 21 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.