May 30, 2008 at 2:27 pm
Hi,
I have not used Stored Procedures before so am unfamiliar with the syntax.
I would like to run a stored procedure (in SS2K5Express) from Access mdb.
Reason: I would like to stop some users reading, updating, inserting data in a table, but need them to be able to do INSERT and UPDATE commands on the table when I create a record in the background (of a form).
I need to know how (including code) to do the following:
1. Call a stored procedure from Access (and pass parameters).
2. If I can pass a variable with the INSERT or UPDATE command as one string, how would I write the code (in the stored procedure) to run that SQL string. I would then only need to have one stored procedure in SQL Server (to bypass the security settings on Access).
Thanks 🙂
May 30, 2008 at 2:36 pm
Access has wizards to help you write a stored procedure. They're limited, but they can help you get a start on it.
A form can have a stored proc as its data source. Then it has a section in the form properties where you can set the input parameters for the proc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 30, 2008 at 5:38 pm
Hi,
I don't know if I explained myself correctly then.
I want to write the stored procedure in SQL Server 2005 Express, and I want to call it from Access.
I would like to pass the SQLString (Insert or Update statement) to the Stored Procedure in SQL Server and then execute that string.
Can anyone help me with the code:
1. In Access to call the stored procedure.
2. In SQL Server to execute the SQL String that I pass into it.
Thanks
May 30, 2008 at 8:35 pm
Create a passthrough query in Access... Connect it to your DB.
Just type in as the query:
Exec MyStoredProcedureName @Myparameter='MyString'
Save query - run it.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 2, 2008 at 7:41 am
As far as the query to build, what you're describing is called "dynamic SQL". You'll need to take a look as sp_executesql in Books Online. It will tell you how to do that.
On the other hand, I recommend against doing what you're planning. Build the insert/update/delete commands as specific procs, then execute them with input parameters. Dynamic SQL opens up all kinds of security issues in your database.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 3, 2008 at 7:41 am
If you are planning on using one stored procedure to do either an insert or an update you need to remember that this may cause the procedure to be recompiled each time you run it ... defeating the purpose of the procedure.
My suggestion would be to use one SP for the insert and one for the update, or try the UPSERT statement if it works for your situation (I have to say, though, I have only read about that one and not yet had the chance to use it in production).
June 3, 2008 at 8:49 am
You should explore the "ACCESS PROJECT" option ... is very customized to use this kind of things.
* Noel
June 3, 2008 at 5:27 pm
Hi,
Thank you for all the replies.
I would like to use Access Project but cannot at present, as I do not have the time to switch all the programming over.
I think I will create 1 SP for INSERT and 1 for UPDATE.
In regard to the passthrough query, how can I run the SP directly from the code in Access. I am unsure what you mean by 'link the query to your DB', and would prefer to call it directly.
Is there a way to do this (ie. code)?, and if not, how do I link the passthrough query back to SQL (and then call the query from the code)?
I am new to this so apologise if these are simple questions.
Thanks again.
June 4, 2008 at 1:44 pm
There are several ways to run a proc from Access to a different database. One is to create a VBA script that will run the proc. Another is a pass-through query. Both are in Access' documentation.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 4, 2008 at 1:57 pm
Since you have lots of parameters to pass - I would probably look up "ADO Command" and get really familiar with it. There's a fair amount of readily available code on how to wire up an ADODB Command object, set the various params to the right values, and execute it.
Otherwise - you can tackle it by essentially writing dynamic SQL calls to the passthrough query, and then using the DAO query.RunQuery to get it to execute.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 5, 2008 at 11:46 am
The following code should work from Access. It's simpler to use ADO but in some cases this isn't possible.
The code below is how to insert a row into the Orders table and return an Identity column back to VBA.
Dim SQLStr As String
Dim NewOrder AS Long
SQLStr = "INSERT INTO Orders (CustomerID, OrderDate) & _
" SELECT " & CustomerID & ", '" & Format(Date, "mm/dd/yy") & "'"
NewOrder = InsertWithIdentity(SQLStr)
Function InsertWithIdentity(SQLtext As String, Optional ExistingConnection) As Long
' Pass an insert query string. Return is the identity just created from the insert.
Const cConnectString As String = "ODBC;DRIVER={SQL Server};SERVER=MyServer;" & _
"UID=MyUser;PWD=mypwd;DATABASE=MyDatabase"
Dim Ws As Workspace, Cnx As Connection, Qdef As QueryDef, rs As Recordset, Rc
On Error Resume Next
If IsMissing(ExistingConnection) Then
Set Ws = CreateWorkspace("", "", "", dbUseODBC)
Set Cnx = Ws.OpenConnection("", , , cConnectString)
Else
Set Cnx = ExistingConnection
End If
Set Qdef = Cnx.CreateQueryDef("")
' Get the identity column after the insert
Qdef.SQL = SQLtext & " " & vbCrLf & "SELECT @@identity AS TheIdentity"
Set rs = Qdef.OpenRecordset()
If rs.EOF Then
InsertWithIdentity = -1' Return -1 for Error
Else
InsertWithIdentity = IIf(IsNull(rs!TheIdentity), -1, rs!TheIdentity)' Return the Identiry
End If
rs.Close
Set rs = Nothing
If IsMissing(ExistingConnection) Then
Cnx.Close
Ws.Close
End If
Set Cnx = Nothing
Set Ws = Nothing
Set Qdef = Nothing
End Function
June 5, 2008 at 3:07 pm
That works, but it has all of the problems of dynamic SQL.
Another minor point is, it should use scope_identity, instead of @@identity. @@Identity has problems with things like triggers.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 6, 2008 at 12:01 pm
hi
Could you please expand, where do i put the exec statement
Regards
Roos
June 6, 2008 at 12:44 pm
Create a new query in Access, don't add any tables. Click Query ~ SQL Specific ~ Pass-Through. In the SQL window, type your statement e.g.
EXEC usp_MyProcedure '6/6/2008'
then click View ~ Properties and under the ODBC Connect String click the build button (...) and select a DSN that points to your SQL database.
Save your query e.g. qryMyQuery
You can run the query just like any Access action query.
To change the parameters for the query, use this in VBA code:
CurrentDB.QueryDefs("qryMyQuery").SQL = "EXEC usp_MyProcedure '6/1/2008' "
Make sure that you grant EXECUTE permission for the stored procedure to the login used by your DSN.
HTH
June 6, 2008 at 2:42 pm
I am not quite sure what the problem is. Are you connected to the SQL database in MSAccess? Then this VBA script will work for you. I program continuously in Access and VBA and this is how I call sprocs in VBA. Let me know if this worked.
Public Function MyFunction() As Boolean
On Error GoTo errChq
Dim errLoop As Variant
Dim Result1 As Long
Dim cnnDd As ADODB.Connection
Dim cmdd As New ADODB.Command
Dim rstd As New ADODB.Recordset
Set cnnDd = CurrentProject.Connection
Set cmdd.ActiveConnection = cnnDd
cmdd.CommandText = "name of stored proc"
cmdd.CommandType = adCmdStoredProc
cmdd.CommandTimeout = 15 'you can set this higher if the sproc takes long to run
' Define the stored procedure's input parameter.
Dim prmBy1d As New ADODB.Parameter
prmBy1d.Type = adInteger
'for the datatype you can type highlight "= adInteger" and type "=" and VBA will give you a couple of options. The datatypes looks a bit different in VBA I suggest you look it up in help e.g. a bit is called a boolean
'if you have a string datatype you will have to define a size bit not for datetime, numbers, currency
'prmBy1d.Size = 50
prmBy1d.Direction = adParamInput
prmBy1d.Value = WhatNumber
cmdd.Parameters.Append prmBy1d
'for each parameter you in your sproc you define like the above but give each parameter a unique number
Set rstd = cmdd.Execute
cnnDd.Errors.Clear
Set rstd = cmdd.Execute
If cnnDd.Errors.Count > 0 And Err.Number <> 0 Then
For Each errLoop In cnnDd.Errors
MsgBox "Failed :Error number: " & Err.Number & vbCr & _
Err.Description
Next errLoop
Exit Function
End If
MyFunction = True
Exit Function
errChq:
MsgBox Err.Number & ":" & Err.Description
End Function
I hope this helps!
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply