Access 07: Creating Procedures

[Rob W]

Greetings,

This is a mixed technologies question really, but here goes and I will
explain as clearly as I can.

I am developing an application in vb.net, within this app I hardcode my SQL
statements and execute them using the OleDbCommand, passing them in as a
string/text.
With OleDbCommand I have read it can execute types storedprocedure,
tableDirect and text (which I use).
All my queries written in MS Access using the query wizard appear in .NET
data connections as functions, I right click and there is no real properties
other than type where the query is labeled as type Function though next to
its query name it reads procedure.

Can I execute these functions in vb.NET (don't look to be supported by the
OleDBCommand)?

I would also be interested in executing storedprocedures as another
alternative.

I read storedprocedures are SQLserver related, however I cannot come to the
conclusion if an ms access 07 database has them or sort of pseudo stored
procedures.

If I created a normal ms access procedure i.e. "CREATE PROCEDURE exist
([MembershipNum] Text) AS SELECT strUsername FROM tblMembers WHERE
cMembershipId = [MembershipNum] "

Do you think these would be read as stored procedures in vb.net and be seen
via server explorer in .NET (as the functions do)?

With this in mind I don't know how to create procedures in ms access 07.

I know it has to be executed in vba but other than that I'm pretty clueless.

This link looks interesting
http://help.lockergnome.com/office2/Creating-stored-Procedure-Access--ftopict25592.html

can I create a procedure from the debug window?



Hope I have explained my problem which is in summary to be able to execute
SQL statements/queries written in MS access within my .Net application
rather than hard coding in SQL statements.



Thanks (Its a long post!!)

Rob

 

[Arvin Meyer [MVP]]

This is a question for a vb.net newsgroup. From most languages there is an
Execute command like in DAO:

CurrentDb.Execute "Select * From ..."

in OLEDB, something like (oCon, being the connection object):

oCon.Execute

 

[Rob W]

Thanks for the reply.

I thought I need to create the procedure in MS Access first?
i.e. CREATE PROCEDURE exist
([MembershipNum] Text) AS SELECT strUsername FROM tblMembers WHERE
cMembershipId = [MembershipNum])

Then I know the code in .net to run a OLEDBcommand of type procedure, I just
don't have a clue how to create procedures in MS Access.

Thanks
Rob

This is a question for a vb.net newsgroup. From most languages there is an
Execute command like in DAO:

CurrentDb.Execute "Select * From ..."

in OLEDB, something like (oCon, being the connection object):

oCon.Execute
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Greetings,

This is a mixed technologies question really, but here goes and I will
explain as clearly as I can.

I am developing an application in vb.net, within this app I hardcode my
SQL statements and execute them using the OleDbCommand, passing them in
as a string/text.
With OleDbCommand I have read it can execute types storedprocedure,
tableDirect and text (which I use).
All my queries written in MS Access using the query wizard appear in .NET
data connections as functions, I right click and there is no real
properties other than type where the query is labeled as type Function
though next to its query name it reads procedure.

Can I execute these functions in vb.NET (don't look to be supported by
the OleDBCommand)?

I would also be interested in executing storedprocedures as another
alternative.

I read storedprocedures are SQLserver related, however I cannot come to
the conclusion if an ms access 07 database has them or sort of pseudo
stored procedures.

If I created a normal ms access procedure i.e. "CREATE PROCEDURE exist
([MembershipNum] Text) AS SELECT strUsername FROM tblMembers WHERE
cMembershipId = [MembershipNum] "

Do you think these would be read as stored procedures in vb.net and be
seen via server explorer in .NET (as the functions do)?

With this in mind I don't know how to create procedures in ms access 07.

I know it has to be executed in vba but other than that I'm pretty
clueless.

This link looks interesting
http://help.lockergnome.com/office2/Creating-stored-Procedure-Access--ftopict25592.html

can I create a procedure from the debug window?



Hope I have explained my problem which is in summary to be able to
execute SQL statements/queries written in MS access within my .Net
application rather than hard coding in SQL statements.



Thanks (Its a long post!!)

Rob

 

[Klatuu]

Stored Procedures do not exist within Access.
--
Dave Hargis, Microsoft Access MVP

Thanks for the reply.

I thought I need to create the procedure in MS Access first?
i.e. CREATE PROCEDURE exist
([MembershipNum] Text) AS SELECT strUsername FROM tblMembers WHERE
cMembershipId = [MembershipNum])

Then I know the code in .net to run a OLEDBcommand of type procedure, I just
don't have a clue how to create procedures in MS Access.

Thanks
Rob

This is a question for a vb.net newsgroup. From most languages there is an
Execute command like in DAO:

CurrentDb.Execute "Select * From ..."

in OLEDB, something like (oCon, being the connection object):

oCon.Execute
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Greetings,

This is a mixed technologies question really, but here goes and I will
explain as clearly as I can.

I am developing an application in vb.net, within this app I hardcode my
SQL statements and execute them using the OleDbCommand, passing them in
as a string/text.
With OleDbCommand I have read it can execute types storedprocedure,
tableDirect and text (which I use).
All my queries written in MS Access using the query wizard appear in .NET
data connections as functions, I right click and there is no real
properties other than type where the query is labeled as type Function
though next to its query name it reads procedure.

Can I execute these functions in vb.NET (don't look to be supported by
the OleDBCommand)?

I would also be interested in executing storedprocedures as another
alternative.

I read storedprocedures are SQLserver related, however I cannot come to
the conclusion if an ms access 07 database has them or sort of pseudo
stored procedures.

If I created a normal ms access procedure i.e. "CREATE PROCEDURE exist
([MembershipNum] Text) AS SELECT strUsername FROM tblMembers WHERE
cMembershipId = [MembershipNum] "

Do you think these would be read as stored procedures in vb.net and be
seen via server explorer in .NET (as the functions do)?

With this in mind I don't know how to create procedures in ms access 07.

I know it has to be executed in vba but other than that I'm pretty
clueless.

This link looks interesting
http://help.lockergnome.com/office2/Creating-stored-Procedure-Access--ftopict25592.html

can I create a procedure from the debug window?



Hope I have explained my problem which is in summary to be able to
execute SQL statements/queries written in MS access within my .Net
application rather than hard coding in SQL statements.



Thanks (Its a long post!!)

Rob

 

[Gigamite]

I read storedprocedures are SQLserver related, however I cannot come to the
conclusion if an ms access 07 database has them or sort of pseudo stored
procedures.

A2K7 has them in ADPs, which means they're stored in the backend SQL
Server database, not the front end you want to program with.

If I created a normal ms access procedure i.e. "CREATE PROCEDURE exist
([MembershipNum] Text) AS SELECT strUsername FROM tblMembers WHERE
cMembershipId = [MembershipNum] "

Do you think these would be read as stored procedures in vb.net and be seen
via server explorer in .NET (as the functions do)?

Your application's connection string needs to use the SQL Server
database in order for your VB.net environment to "see" the procedures
and data.

With this in mind I don't know how to create procedures in ms access 07.

You create them with SSMS, not Access.

 

[Rob W]

So is the outcome when I'm connecting to a JET DATABASE (accdb) in vb.net I
cannot use stored procedures or reference any queries I've written in MS
access (though can see them as functions in .net)?

If so that's not very impressive :-(

I read storedprocedures are SQLserver related, however I cannot come to
the conclusion if an ms access 07 database has them or sort of pseudo
stored procedures.

A2K7 has them in ADPs, which means they're stored in the backend SQL
Server database, not the front end you want to program with.

If I created a normal ms access procedure i.e. "CREATE PROCEDURE exist
([MembershipNum] Text) AS SELECT strUsername FROM tblMembers WHERE
cMembershipId = [MembershipNum] "

Do you think these would be read as stored procedures in vb.net and be
seen via server explorer in .NET (as the functions do)?

Your application's connection string needs to use the SQL Server database
in order for your VB.net environment to "see" the procedures and data.

With this in mind I don't know how to create procedures in ms access 07.

You create them with SSMS, not Access.

 

[Gigamite]

So is the outcome when I'm connecting to a JET DATABASE (accdb) in vb.net I
cannot use stored procedures or reference any queries I've written in MS
access (though can see them as functions in .net)?

If so that's not very impressive :-(

If you wrote your stored procedures from Access, you _stored_ them
in SQL Server and that's what you need to target. The queries are
probably accessible from your VB.net application (I don't know,
never tried it), but you need to identify whether it's really an
Access query or a SQL Server view. The queries will be stored in
Access, and the views will be stored in SQL Server. Target where
they're stored, not where you can see them when you open Access
_unless_ you're working from Access, which you're not. Eliminate
any middle man and you'll have better performance, less complexity
and easier software maintenance.

 

[Arvin Meyer [MVP]]

You can create SQL statements for use in any database. Stored Procedures and
only available against server databases like SQL-Server or Oracle. Access
doesn't support any server-based constructs like Stored Procs and Triggers.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thanks for the reply.

I thought I need to create the procedure in MS Access first?
i.e. CREATE PROCEDURE exist
([MembershipNum] Text) AS SELECT strUsername FROM tblMembers WHERE
cMembershipId = [MembershipNum])

Then I know the code in .net to run a OLEDBcommand of type procedure, I
just don't have a clue how to create procedures in MS Access.

Thanks
Rob

This is a question for a vb.net newsgroup. From most languages there is
an Execute command like in DAO:

CurrentDb.Execute "Select * From ..."

in OLEDB, something like (oCon, being the connection object):

oCon.Execute
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Greetings,

This is a mixed technologies question really, but here goes and I will
explain as clearly as I can.

I am developing an application in vb.net, within this app I hardcode my
SQL statements and execute them using the OleDbCommand, passing them in
as a string/text.
With OleDbCommand I have read it can execute types storedprocedure,
tableDirect and text (which I use).
All my queries written in MS Access using the query wizard appear in
.NET data connections as functions, I right click and there is no real
properties other than type where the query is labeled as type Function
though next to its query name it reads procedure.

Can I execute these functions in vb.NET (don't look to be supported by
the OleDBCommand)?

I would also be interested in executing storedprocedures as another
alternative.

I read storedprocedures are SQLserver related, however I cannot come to
the conclusion if an ms access 07 database has them or sort of pseudo
stored procedures.

If I created a normal ms access procedure i.e. "CREATE PROCEDURE exist
([MembershipNum] Text) AS SELECT strUsername FROM tblMembers WHERE
cMembershipId = [MembershipNum] "

Do you think these would be read as stored procedures in vb.net and be
seen via server explorer in .NET (as the functions do)?

With this in mind I don't know how to create procedures in ms access 07.

I know it has to be executed in vba but other than that I'm pretty
clueless.

This link looks interesting
http://help.lockergnome.com/office2/Creating-stored-Procedure-Access--ftopict25592.html

can I create a procedure from the debug window?



Hope I have explained my problem which is in summary to be able to
execute SQL statements/queries written in MS access within my .Net
application rather than hard coding in SQL statements.



Thanks (Its a long post!!)

Rob

 

[David W. Fenton]

You can create SQL statements for use in any database. Stored
Procedures and only available against server databases like
SQL-Server or Oracle. Access doesn't support any server-based
constructs like Stored Procs and Triggers.

Strictly speaking, since Jet 4, DML queries (i.e., "action queries,"
e.g., INSERT, DELETE, UPDATE) are considered to be "procedures"
(though not "stored procedures"). ADO allows you to create them
using the same syntax you'd use to create a stored procedure on a
server database.

I find the terminology silly, since those coming from outside Access
expect something called a "procedure" to be able to include
"procdural" code, but that's not the case, of course.

I've had running argument over this subject on Stackoverflow.com for
quite a while. I find it useless to pollute the term by applying it
to what Jet/ACE can do, but as with obfuscating the difference
between Access and its default db engine, Microsoft insists on doing
it anyway.

 

[David W. Fenton]

So is the outcome when I'm connecting to a JET DATABASE (accdb) in
vb.net I cannot use stored procedures or reference any queries
I've written in MS access (though can see them as functions in
.net)?

You can access and use any pure Jet/ACE objects, i.e., tables and
queries. But none of the Access objects are available to you except
by using COM automation.

In regard to "stored procedures” there is no such thing in
Access/Jet/ACE as a saved SQL statement with procedural code.
Microsoft since Jet 4 has started referring to DML queries (INSERT,
UPDATE, DELETE) as “procedures,” and, of course, any saved QueryDef
is “stored.” It’s just not a “procedure” by any meaningful
definition of the term.

And QueryDef in a Jet/ACE database can be executed from another
application as long as it’s not dependent on Access functions or VBA
user-defined functions.

 

[Hans Up]

I read storedprocedures are SQLserver related, however I cannot come to the
conclusion if an ms access 07 database has them or sort of pseudo stored
procedures.

You can create "stored procedures" with ADO in Access. However, the
capabilities of those "procedures" are very limited in comparison to SQL
Server or other powerful database systems.

If I created a normal ms access procedure i.e. "CREATE PROCEDURE exist
([MembershipNum] Text) AS SELECT strUsername FROM tblMembers WHERE
cMembershipId = [MembershipNum] "

Try it with ADO.

Do you think these would be read as stored procedures in vb.net and be seen
via server explorer in .NET (as the functions do)?

I have no clue how it would appear in .NET

With this in mind I don't know how to create procedures in ms access 07.

I know it has to be executed in vba but other than that I'm pretty clueless.

I can show you one I created in Access 2003, and I just now verified it
also works in Access 2007.

I used a function to spit out my CREATE statement:

Public Function demoCreateProcStatement() As String
Dim strSql As String

'# this one worked
strSql = "CREATE PROCEDURE uspTblAutonumberInsert" & vbNewLine _
& "(pFrom CHARACTER, pTo CHARACTER) AS" & vbNewLine _
& "INSERT INTO tblAutonumber ( fFrom, fTo )" & vbNewLine _
& "VALUES (pFrom, pTo);"

demoCreateProcStatement = strSql
End Function

Then from the Immediate Window, ran this command:

currentproject.Connection.Execute demoCreateProcStatement()

Then I ran this VBA sub (as written, requires you to set a reference to
Microsoft ActiveX Data Objects Library):

Public Sub RunProc()
Dim cn As ADODB.Connection
Dim lngLastAutonumber As Long
Dim strSql As String

Set cn = CurrentProject.Connection

strSql = "uspTblAutonumberInsert 'jjj', Null;"
cn.Execute strSql, , adCmdStoredProc + adExecuteNoRecords

lngLastAutonumber = 0
lngLastAutonumber = lastAutonumber(cn)
Debug.Print "Last Autonumber = " & CStr(lngLastAutonumber)

Set cn = Nothing
End Sub

That subroutine calls this function:

Public Function lastAutonumber(ByRef cn As ADODB.Connection) As Long
Dim lngLastAutonumber As Long
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "SELECT @@identity AS last_autonumber;", cn
lngLastAutonumber = 0
If Not (rs.BOF Or rs.EOF) Then
lngLastAutonumber = rs!last_autonumber
End If
rs.Close
Set rs = Nothing
lastAutonumber = lngLastAutonumber
End Function

In my Access 2007 system with an MDB format database, I see
uspTblAutonumberInsert in the Queries group of the navigation panel. I
haven't tried this with the newer ACCDB format database.

The approach I used may be way more complicated than what you need ---
you get to decide which parts, if any, are useful to you.

I suspect your .NET goal is to get to something like these two lines:

strSql = "uspTblAutonumberInsert 'jjj', Null;"
cn.Execute strSql, , adCmdStoredProc + adExecuteNoRecords

Good luck,
Hans

 

[Mark Andrews]

You can execute stored procedures in Access by using a pass thru query with
sql such as
"exec mystoredproc @salary = 50000"
You do need the connection to the sql server in the pass-thru query.
These will run the sql on the sql server and just return the results to
Access so much better
when you have big sql server tables etc....

Have not done any of the other things you are trying to do.

HTH,
Mark

I read storedprocedures are SQLserver related, however I cannot come to
the conclusion if an ms access 07 database has them or sort of pseudo
stored procedures.

You can create "stored procedures" with ADO in Access. However, the
capabilities of those "procedures" are very limited in comparison to SQL
Server or other powerful database systems.

If I created a normal ms access procedure i.e. "CREATE PROCEDURE exist
([MembershipNum] Text) AS SELECT strUsername FROM tblMembers WHERE
cMembershipId = [MembershipNum] "

Try it with ADO.

Do you think these would be read as stored procedures in vb.net and be
seen via server explorer in .NET (as the functions do)?

I have no clue how it would appear in .NET

With this in mind I don't know how to create procedures in ms access 07.

I know it has to be executed in vba but other than that I'm pretty
clueless.

I can show you one I created in Access 2003, and I just now verified it
also works in Access 2007.

I used a function to spit out my CREATE statement:

Public Function demoCreateProcStatement() As String
Dim strSql As String

'# this one worked
strSql = "CREATE PROCEDURE uspTblAutonumberInsert" & vbNewLine _
& "(pFrom CHARACTER, pTo CHARACTER) AS" & vbNewLine _
& "INSERT INTO tblAutonumber ( fFrom, fTo )" & vbNewLine _
& "VALUES (pFrom, pTo);"

demoCreateProcStatement = strSql
End Function

Then from the Immediate Window, ran this command:

currentproject.Connection.Execute demoCreateProcStatement()

Then I ran this VBA sub (as written, requires you to set a reference to
Microsoft ActiveX Data Objects Library):

Public Sub RunProc()
Dim cn As ADODB.Connection
Dim lngLastAutonumber As Long
Dim strSql As String

Set cn = CurrentProject.Connection

strSql = "uspTblAutonumberInsert 'jjj', Null;"
cn.Execute strSql, , adCmdStoredProc + adExecuteNoRecords

lngLastAutonumber = 0
lngLastAutonumber = lastAutonumber(cn)
Debug.Print "Last Autonumber = " & CStr(lngLastAutonumber)

Set cn = Nothing
End Sub

That subroutine calls this function:

Public Function lastAutonumber(ByRef cn As ADODB.Connection) As Long
Dim lngLastAutonumber As Long
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "SELECT @@identity AS last_autonumber;", cn
lngLastAutonumber = 0
If Not (rs.BOF Or rs.EOF) Then
lngLastAutonumber = rs!last_autonumber
End If
rs.Close
Set rs = Nothing
lastAutonumber = lngLastAutonumber
End Function

In my Access 2007 system with an MDB format database, I see
uspTblAutonumberInsert in the Queries group of the navigation panel. I
haven't tried this with the newer ACCDB format database.

The approach I used may be way more complicated than what you need ---
you get to decide which parts, if any, are useful to you.

I suspect your .NET goal is to get to something like these two lines:

strSql = "uspTblAutonumberInsert 'jjj', Null;"
cn.Execute strSql, , adCmdStoredProc + adExecuteNoRecords

Good luck,
Hans

 

[Arvin Meyer [MVP]]

Are you, perhaps talking about OBDCDirect? Using that, (it's been 10 years
since I last used it) IIRC, you can use T-SQL to access the server database,
with the same language as creating a procedure on the server.

 

[David W. Fenton]

Are you, perhaps talking about OBDCDirect? Using that, (it's been
10 years since I last used it) IIRC, you can use T-SQL to access
the server database, with the same language as creating a
procedure on the server.

No, I'm not talking about ODBCDirect, which is for accessing
databases *other* than Jet.

The pollution of terminology in Jet 4 was part of the ADO debacle.
MS recommended using ADO everywhere, including with Jet (which we
all know was wrong for Access users), and ADO creates DML queries in
Access with the same commands that are used to create real stored
procedures in server databases.

Most of us don't notice this because we don't use ADO and because we
know enough to not follow MS's recommendations in regard to ADO and
Access. I didn't know about it until it was recently pointed out to
me on Stackoverflow.com.

 

[David W. Fenton]

You can execute stored procedures in Access by using a pass thru
query

This is not an answer to the OP's question, though.