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.OL
conn.Open()
Using cmd As New OleDbCommand()
cmd.CommandText = "NameOfTheQuery"
cmd.CommandType = CommandType.StoredProcedur
' 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
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.OL
conn.Open()
Using cmd As New OleDbCommand()
cmd.CommandText = "NameOfTheQuery"
cmd.CommandType = CommandType.StoredProcedur
' 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.OL
' live connection
' Dim sConStr As String = "Provider=Microsoft.ACE.OL
'''' 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.OL
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
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.OL
' live connection
' Dim sConStr As String = "Provider=Microsoft.ACE.OL
'''' 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.OL
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
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.
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.
ASKER
Like I put in my reply thank's a lot , it is correct what scott said about mdb versus accdb
cheer's
Alan
cheer's
Alan
ASKER
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
so Scott again my apology and thank you for the solution
alan
l
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.