• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

how to build a stored proc in office 2013 calling it from vb net

Id id this once before and had not to much problem with it, but now with a office Access 2013 and in vb net 12,  I cannot redo it.

All I want to do is building 2  Stored  Procedure  in access  2013, simple one like ( I have this running on my MySQL server )

`@`%` PROCEDURE `getHotStaff`(HotelID bigint)
BEGIN

Select * from staff where hot_id = HotelID;

END


and call it from vb net with something like this
//
   Using conn As New OleDbConnection()
            conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myaccess.mdb"
            conn.Open()
            Using cmd As New OleDbCommand()
                cmd.CommandText = "NameOfTheQuery"
                cmd.CommandType = CommandType.StoredProcedure
                ' Note: You can also use CommandType.TableDirect
                cmd.Connection = conn
                cmd.ExecuteNonQuery()
                conn.Close()
            End Using
        End Using

my problem is that  with office 2013, all I was able to build is a view but no 'stored proc', I google it but didn't find that step by step in access 2013 on how to build a S.P
A little help would be greatly appreciated,

Cher's

Alan
0
hiramlight
Asked:
hiramlight
  • 3
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Access doesn't do stored procedures, but you can create a saved Query in the Access db and call that:

Using conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myaccess.mdb")
  conn.Open()
  Using cmd As New OleDbCommand("SELECT * FROM YourQuery WHERE HotelID=" & YourIDValue, conn)
    Using dt As New Datatable
      dt.Load(cmd.ExecuteReader)
    End Using      
  End Using
End Using
0
 
Jacques Bourgeois (James Burger)PresidentCommented:
Why go through Access to execute a stored procedure with data in SQL Server. Couldn't you simply connect directly to the server instead of using an intermediate.

And your 'Note is not good. You can use TableDirect only when you read the whole table, not when you are running a SQL command.
0
 
hiramlightAuthor Commented:
I am working with a Third party application and they are using access , this is just for prox key for hotel door, so got to get lock pw word, Id and couple more info from that cpy DB,  for all other db in my app I am using MySQL >>> why I am not an access expert ...>

I find a way some time ago from.Net  to create some proc into access using this, I simply don't remember how I was getting to it . Maybe just calling  'ProductsProcs'  from a test aspx  page

/////////////////////////

   Public Sub ProductsProcs()
        Dim sSQL As String

              ' GetRoomList - Retrieves entire rofstatus table
        '  sSQL = "CREATE PROC GetRoomList AS SELECT * FROM rofstatus;"
        '  CreateStoredProc(sSQL)

        '  GetDBConn - Retrieves entire rofstatus table ( S.P to install)
     
  sSQL = "CREATE PROC GetMekinfo AS SELECT * FROM H_MSC;"
        CreateStoredProc(sSQL)

    End Sub

/////////////////////////////////////////

   ' Execute the creation of Stored Procedures
   
 Public Sub CreateStoredProc(ByVal sSQL As String)
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand = New OleDbCommand()
        Dim da As OleDbDataAdapter

        ' For all OFFLINE query
        '  Dim sConStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\gidadmin\Documents\Visual Studio 2008\Projects\Rooms Management System\gidrms\data\rmstransac.accdb'"

        ' live connection
  ' Dim sConStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='DBInfo.accdb'"
 
''''  will try and replace the accdb file by my H_Data.mdb since I cannot convert my mdb to accdb  ( even now that I decrypt the DB and remove the pw >>>??? ))

      Dim sConStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='D:\Projects\RMS APPLICATIONS 4.8\RMS_4\_VS 2013\gidrms_5\DBstoreProc\H_Data.mdb'"
     

   con = New OleDbConnection(sConStr)

        cmd.Connection = con
        cmd.CommandText = sSQL

        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()

    End Sub///

I was certainly using an page to call that sub , but can't remember what I did with it , but this was creating  a stro proc or something similar in access and working very well .

cheer's

Alan
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
On further reading, it seems Access 2010 and above can create Stored Procs:

https://msdn.microsoft.com/en-us/library/office/ff845861.aspx

However, the database you're connecting to MUST be an .accdb database, and it must be 2010 or above (2007 did not have that functionality).

You would not be able to do this with a .mdb database.

That said - I see absolutely no benefit in pushing off processing like this to Access. You'd be much better served doing it directly in your code, as I showed above. There's just no good reason to do this the way you want.
0
 
hiramlightAuthor Commented:
Like I put in my reply thank's a lot , it is correct what scott said about mdb versus  accdb

cheer's

Alan
0
 
hiramlightAuthor Commented:
I like to apologize for the long delay but I had to go out of station for a long time ( Mother illness) , but Scott is right and in my case I have to work with a third party app using mdb  and for store proc will have to use 2010 or above and it seem that app DB was build in a 2007 so no stored proc but queries work just as well.

so Scott  again my apology and thank you for the solution

alan  
l
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now