sending paramater info from access form 2

[Spyridon]

Hi,

I'm just getting started with SQL Server, and I've run into a bit of a problem. I have upsized my Access database, but in a few of my old queries I have query fields based on a parameter sent to it from a form. Those queries won't upsize, and in creating a new view I'm wondering how to get that same info from my form in my access project. I'm guessing that it has to do with a stored procedure, but I'm really at a loss how to create a stored procedure, much less one that did what I needed it too!

Any help would be appreciated, and I'll specify any other info that you might need.


Michael

 

[josephwalter]

I would suggest either leaving your queries in Access (simply base them on your linked tables from SQL Server) or use the Filter property on your Form/Report to limit the records based on the values provided by the user.

 

[BuGlen]

Actually, stored procedures are not that difficult once you get used to the syntax. Let's take a Northwind example and break it down a bit:
-----
CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5)
AS
SELECT OrderID,
OrderDate,
RequiredDate,
ShippedDate
FROM Orders
WHERE CustomerID = @CustomerID
ORDER BY OrderID

GO
-----
The command "CREATE PROCEDURE" tells SQL server what the object is and the "CustOrdersOrders" names the object. Following that, you have the parameter expected by the procedure (always starts with an @ character) and the parameter type. You can specify more than one expected parameter by seperating them with commas (@Parm1 int, @Parm2 nchar(2), etc...).

Now, everything after the "AS" directive in the statement tells SQL server what is going to be returned (and/or updated) and how. Each field defined in the SELECT part of the statement will be what's returned, just like a view or query. In this case, 4 fields will be returned.

As you can see, the expected parameter, @CustomerID is being used in the WHERE clause to filter the results which is fairly common practice. The ORDER BY pretty much speaks for itself, but you could use a supplied parameter here as well.

And finally, the GO command which tells SQL server to execute the current batch.

This is a very basic example, and when you get comfortable with the structures, you will no doubt find some really great uses for stored procedures.

Hope this helps.

Glen Appleton

VB.Net student.

 

[josephwalter]

How can you base a form or report off the results of the stored procedure?

 

[BuGlen]

Yes, you can by creating a Pass-Through query in Access to call the SP and return the results. However, in a report that groups results the SP could be called many times and it might be better to first call the SP and cache the results to a local table and run the report or form from the cached data.

Glen Appleton

VB.Net student.

 

[josephwalter]

I just made my first Pass-Through query. I was able to make a form and report from the recordset returned by a stored procedure. I've found that my recordset is not updateable, though. Is there a way to get an updatable recordset from my pass-through query?

 

[BuGlen]

Not from the same query that returned the recordset. If you want to use this method to harness the power of the SQL server, you will need to cache the results, allow the user to make the changes, and the use another pass-through to update the changes against the data on the server.

The down side to this method is the record being edited is not locked on the server, but depending on your application, this might not be a problem.

Glen Appleton

VB.Net student.

 

[Spyridon]

Thank you BuGlen,

You should write a book. Very concise and simple answer. I feel fairly comfortable with it, but I am wondering one thing. Maybe you can show me the light here. I have a login form that takes a user's id, checks it with a table, and then populates another table with the info. In Access the append query's SQL looks like this:

INSERT INTO LocalUser ( UserID, UserLevel, SalesID, Manager, Employee )

SELECT [Forms]![FoLogin]![txtUserID] AS Expr1,

Employee.UserLevel, Employee.SalesID, Employee.Manager, Employee.Employee

FROM Employee

WHERE (((Employee.UserLevel)=(SELECT UserLevel FROM Employee WHERE UserID = [Forms]![FoLogin]![txtUserID]))

AND ((Employee.SalesID)=(SELECT SalesID FROM Employee WHERE UserID = [Forms]![FoLogin]![txtUserID]))

AND ((Employee.Manager)=(SELECT Manager FROM Employee WHERE UserID = [Forms]![FoLogin]![txtUserID]))

AND ((Employee.Employee)=(SELECT Employee FROM Employee WHERE UserID = [Forms]![FoLogin]![txtUserID])));


Sorry for the formating here, I'm sure it won't come up right. In your example, what I'm wanting it to do is something like this:

CREATE PROCEDURE CustOrdersOrders @CustomerID nchar(5)
AS
SELECT OrderID,
OrderDate,
RequiredDate,
ShippedDate
FROM Orders
WHERE CustomerID = (Value passed from a form's text box on click event)

ORDER BY OrderID

GO



In Access the query would read [Forms]![FormName]![textbox]
How does SQL Server need this info?

Thank you so much once again for your help!

Michael

 

[BuGlen]

Hi Michael,

It can't be done directly in the query designer window, but it can be done with a little VBA. Create a pass-through query with the appropriate connect string and the return records property set to true. Then build the SQL string in your forms event (perhaps a button click) to update the SQL of the query with the textbox parameters:

-----

Private Sub MyButton_Click()

    Dim strSQL As String
    Dim qdMyQuery As QueryDef

    strSQL = "Exec ""MyStoredProcedure"" '" & txtMyTextBox & "'"
    Set qdMyQuery = QueryDefs("MyPassThroughQuery")
    qdMyQuery.SQL = strSQL
    Set qdMyQuery = Nothing

    DoCmd.OpenQuery "MyPassThroughQuery", acViewNormal

End Sub

-----

This is just a basic example, but it should give you the idea I'm trying to convey.

Hope this helps.

Glen Appleton

VB.Net student.