pass parameter to sql server view

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a access project front end to a sql server database
After the user enters text and clicks the search button, I need to get the results set from the sql server. The problem is that the user need to make updates to the results set. I know I can use stored-procedure to pass paremeter but the results will not be updateable (am I wrong here???). How can I get the parameter to the where clause of a "view" on the sql server?
 
You can use a stored procedure to make changes.
You need to set the [RecordSet Type] of the form
to "Updatable Snapshot" and set the [Unique table]
property.
Here is an example that I am using:
....
' Expects four parameters
sfrmExpense.Form.InputParameters = "'" &
ParentProfitCenter & "', '" & ProductLine & "', '" &
CostCenter & "', '" & strBudgetTypeID & "'"
sfrmExpense.Form.RecordSource
= "dbo.opProcExpensesExpenseView"
sfrmExpense.Form.UniqueTable = "tblDatabase"
....

NOTE: I am using Access 2000 and SQL Server 2000, if I
change the order of the above lines it does not work ( I
have not figure that out)

Hedi
-----Original Message-----
I have a access project front end to a sql server database.
After the user enters text and clicks the search button,
Click to expand...
I need to get the results set from the sql server. The
problem is that the user need to make updates to the
results set. I know I can use stored-procedure to pass
paremeter but the results will not be updateable (am I
wrong here???). How can I get the parameter to the where
clause of a "view" on the sql server?
 
The purpose of the Access front-end is to enable user to update the sql server db. I added the search option so that the result set is smaller and easier for the user to go through

My main form frmMain has txtbox and btnSearch. Upon the users click, I display (make visible of) the frmResult as a subform inside frmMain. I am thinking to set the frmResult record set to view v_tblDetail (the entire table) and use the filter property to limit the display. How do I set the filter property in the btnSearch_click event



----- Hedi wrote: ----

You can use a stored procedure to make changes
You need to set the [RecordSet Type] of the form
to "Updatable Snapshot" and set the [Unique table]
property
Here is an example that I am using
...
' Expects four parameter
sfrmExpense.Form.InputParameters = "'" &
ParentProfitCenter & "', '" & ProductLine & "', '" &
CostCenter & "', '" & strBudgetTypeID & "'
sfrmExpense.Form.RecordSource
= "dbo.opProcExpensesExpenseView
sfrmExpense.Form.UniqueTable = "tblDatabase
...

NOTE: I am using Access 2000 and SQL Server 2000, if I
change the order of the above lines it does not work ( I
have not figure that out

Hed
-----Original Message----
I have a access project front end to a sql server database
After the user enters text and clicks the search button,
Click to expand...
I need to get the results set from the sql server. The
problem is that the user need to make updates to the
results set. I know I can use stored-procedure to pass
paremeter but the results will not be updateable (am I
wrong here???). How can I get the parameter to the where
clause of a "view" on the sql server
 
Back
Top