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
hiramlightAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.