Calling a Stored Procedure from An Access Form
-
Hi,
I need some help with regards to calling stored procedures on SQL server 2000 from within Access on a form. That will narrow down the records based on a user entered parameter.
So when the user is in the form he can enter a GM_Code and it will call the stored procedure with that GM_Code and will return all that are "like" it and return those reocrds so that they can be browsed by the form.
The Stored Procedure Name : SearchGmCode
The stored procedure Code :
CREATE PROCEDURE dbo.SearchGmCode(@GMCODE nvarchar(50))
AS SELECT *
FROM dbo.product
WHERE (GM_Code Like @GMCODE + '%')
GO
The form code and setup :
Form Name :Search product Form
Source : product <---Linked SQl table in access
Search Criteria Field name : strFirst
Command Button Name : Command187
The Command Button code :
Private Sub Command187_Click()
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Set objConn = CreateObject("ADODB.Connection")
' Create an ADO connect string
sDataConnect = adoConnectSQL("123Access", "Productsearch", "ProductSQL", "GMSQL1")
Set cnn = New ADODB.Connection
cnn.Open sDataConnect
Set cmd = New ADODB.Command
With cmd
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter( _
strfirst, _
adVarChar, _
adParamInput, _
50, _
strfirst)
.CommandText = SearchGmCode
Set .ActiveConnection = cnn
End With
Set rst = New ADODB.Recordset
With rst
.CursorLocation = adUseClient
.LockType = RstLockType
.Open cmd, options:=adCmdStoredProc
End With
Set rst.ActiveConnection = Nothing
Set Forms!MyForm.Recordset = rst
The adoConnectSQL Module code :
Public Function adoConnectSQL(psPassword, psUser, psCatalog, psDataSource)
' Returns SQLOLEDB ADO connect string
' Uses SQL Server security
Dim sProvider, sPassword, sPersist, sUser, sCatalog, sDataSource
sProvider = "Provider=SQLOLEDB.1;"
sPassword = "Password=" & psPassword & ";"
sPersist = "Persist Security Info=True;"
sUser = "User ID=" & psUser & ";"
sCatalog = "Initial Catalog=" & psCatalog & ";"
sDataSource = "Data Source=" & psDataSource
adoConnectSQL = sProvider & sPassword & sPersist & sUser & sCatalog & sDataSource
End Function
Am I even going in the right direction?? FYI this is a access Db not a project. It jsut has the tables linked direc into SQL.
I think ive got everything you would ask of me, although slightly frustrated cus when I clicked preview it wiped my info and didnt show me a preview so I have had to re write this
Time to make a change -
I think you might want to try using a SQL Pass Through query. Set this up by going in to design mode to create a query then with the design window up select Query from the menu. Then select SQL Specific and then SQL Pass Through. Put in the procedure name. You'll have to work with it a bit but I think you can pass in the parameters by putting in the Forms!frmName!txtBox type of statement.
Stuart
-
I don't see a sence of creating a stored procedure here. The select statement is short and not complicated, only code ID is returned so it may or may not be performance gain especially if the field is indexed properly.
I would try first to use ADO Recordset.Open with your Command object that contains CommandText your SQL Statement.
If it does not work I would then follow Stuart's advice and create the Pass Through query.
Your table is a linked SQL Server to Access table. If you will be using explicit SQL, not stored procedure you will not need to have your SQL connection to SQL Server. You can select from the linked table like you select from Access table.
Regards,Yelena Varsha
-
There are roughly 90 fields in this one table and just about all of them are requried to be viewed by the form. There are 2700 + records totalling around 2Gb of information.
I am a total noob with regards to best practices for performance and have been trying to read the best way to do it.
I used this forum post for a reference http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=131&messageid=279076#bm281340 It seemed a nuumber of other people suggested this being the best way too.
Time to make a change -
One incredibly stupid way to do it is to link to the table and simply do
- me.Filter = "GMCode Like " & txtbox & "*"
- me.FilterOn = True
i.e. do it all in Access. Try that and see how slowly it runs.
As to your code it's basically OK except that
With cmd
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter( _
strfirst, _
adVarChar, _
adParamInput, _
50, _
strfirst)
The first strfirst should be "@GMCode" the name of the parameter and the second should be the actual value which I suspect comes from a text box.
One 'nice' trick is to change the Caption of the from so that it displays the filter i.e.
Me.Caption = "GMCode Like " & txtbox & "*"
then at least the users can see what is happening.
-
Hello Antony,
I would use a stored procedure, because you are only using Access as a container to hold the forms & reports etc. Which means that the less you have to think about "Access sql syntax" the better off you'll be.
Also, if you have access to SQL Server Query Analyzer, you can design & test all your sql right there, before you copy it over into Access.
This is what I would do personally:
Create your stored procedure in SQL Server, just like you did above, except that I would use a prefix like 'proc' or 'usp' or 'prc'...
CREATE PROCEDURE dbo.prcSearchGmCode (@GMCODE nvarchar(50))
AS SELECT *
FROM dbo.product
WHERE (GM_Code Like @GMCODE + '%')
...then in Access I would create a pass-thru query with this sql statement:
prcSearchGmCode ''
...and then call it qrySearchGmCode to keep things organized.
Now, let's say you want GMCodes starting with 'Joe' - all you have to do in Access is this (excuse any line breaks):
CurrentDb.QueryDefs("qrySearchGmCode").SQL="prcSearchGmCode 'Joe' "
And when you execute that Access query, you get what you want.
-
very very very very late reply, but this has very much helped me in my further endevaours in stored procedure running.
And you simplistic way of explaining this made it all possible.
/salute
Time to make a change
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply