Creating a parameterised editable query

[John]

Hi

Is there a way for me to achieve below;

1. Have a parameterised query like below on SQL Server that returns a
recordset such that this recordset is editable so it acts like a normal
table.

SELECT Forename, Surname
FROM dbo.Staff
WHERE ([Contact Status] = @Status)

2. Be able to access the recordset within MS Access some way and hopefully
be able to use it as recodsource for a bound Access form.

Many Thanks

Regards

 

[Sylvain Lafontaine]

Well, you didn't tell us which version of Access and of SQL-Server your are
using and most important, by which way you are accessing or using the
SQL-Server from Access: with ODBC Linked Tables? With ADP? With DAO or ADO?
Using a passthrough query? Maybe you have given this information in the
past but each new post/thread should be seen as a distinct entity and
personally, I have no idea how your frontend (Access) is interfacing with
SQL-Server.

As to your question, one common cause of a read-only (or not updatable)
query is a missing primary key in either the query itself or in one of the
base tables. In some cases, Access with automatically retrieve it if you
didn't put it yourself in your query and it's there and available but not
always. Beside a missing primary key, there are also other things that can
make a query read-only.

Finally, I don't see how this question could have something to do with
either m.p.sqlserver.programming or m.p.sqlserver.server as this question is
clearly only about the frontend; Access in this case.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Erland Sommarskog]

Is there a way for me to achieve below;

1. Have a parameterised query like below on SQL Server that returns a
recordset such that this recordset is editable so it acts like a normal
table.

SELECT Forename, Surname
FROM dbo.Staff
WHERE ([Contact Status] = @Status)

APIs such as ADO certainly has this capability. You get the record into
a recorset and say .Update, and ADO will generate the UPDATE commands for
you.

But personally I prefer to write the SQL commands myself, and more over
I prefer to use stored procedures.

Keep in mind that SQL Server is something quite different from Access.

2. Be able to access the recordset within MS Access some way and hopefully
be able to use it as recodsource for a bound Access form.

I leave to the Access people to answer that part.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[John]

Access 97 and SQL Server either 2005 or 2008. Where I am stuck is how to
implement this on SQL Server side that it will fulfil the requirements.

Many Thanks

Regards

Well, you didn't tell us which version of Access and of SQL-Server your
are using and most important, by which way you are accessing or using the
SQL-Server from Access: with ODBC Linked Tables? With ADP? With DAO or
ADO? Using a passthrough query? Maybe you have given this information in
the past but each new post/thread should be seen as a distinct entity and
personally, I have no idea how your frontend (Access) is interfacing with
SQL-Server.

As to your question, one common cause of a read-only (or not updatable)
query is a missing primary key in either the query itself or in one of the
base tables. In some cases, Access with automatically retrieve it if you
didn't put it yourself in your query and it's there and available but not
always. Beside a missing primary key, there are also other things that
can make a query read-only.

Finally, I don't see how this question could have something to do with
either m.p.sqlserver.programming or m.p.sqlserver.server as this question
is clearly only about the frontend; Access in this case.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

Hi

Is there a way for me to achieve below;

1. Have a parameterised query like below on SQL Server that returns a
recordset such that this recordset is editable so it acts like a normal
table.

SELECT Forename, Surname
FROM dbo.Staff
WHERE ([Contact Status] = @Status)

2. Be able to access the recordset within MS Access some way and
hopefully be able to use it as recodsource for a bound Access form.

Many Thanks

Regards

 

[Sylvain Lafontaine]

Access 97? Then you are probably using either ODBC Linked tables or DAO as
ADP isn't available until Access 2000. Another possibility would be ADO.
If you are using ODBC Linked tables, then you have nothing to implement on
the SQL-Server side.

My first suggestion would be that you upgrade to either Access 2003 or 2007.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

Access 97 and SQL Server either 2005 or 2008. Where I am stuck is how to
implement this on SQL Server side that it will fulfil the requirements.

Many Thanks

Regards

Well, you didn't tell us which version of Access and of SQL-Server your
are using and most important, by which way you are accessing or using the
SQL-Server from Access: with ODBC Linked Tables? With ADP? With DAO or
ADO? Using a passthrough query? Maybe you have given this information in
the past but each new post/thread should be seen as a distinct entity and
personally, I have no idea how your frontend (Access) is interfacing with
SQL-Server.

As to your question, one common cause of a read-only (or not updatable)
query is a missing primary key in either the query itself or in one of
the base tables. In some cases, Access with automatically retrieve it if
you didn't put it yourself in your query and it's there and available but
not always. Beside a missing primary key, there are also other things
that can make a query read-only.

Finally, I don't see how this question could have something to do with
either m.p.sqlserver.programming or m.p.sqlserver.server as this question
is clearly only about the frontend; Access in this case.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam
please)
Independent consultant and remote programming for Access and SQL-Server
(French)

Hi

Is there a way for me to achieve below;

1. Have a parameterised query like below on SQL Server that returns a
recordset such that this recordset is editable so it acts like a normal
table.

SELECT Forename, Surname
FROM dbo.Staff
WHERE ([Contact Status] = @Status)

2. Be able to access the recordset within MS Access some way and
hopefully be able to use it as recodsource for a bound Access form.

Many Thanks

Regards

 

[Albert D. Kallal]

Why not just bind the form to the table, and then go:


dim strStatus as string

strStatus = inputbox("enter status")

docmd.OpenForm "myForm",,,"status = '" & strStatus & "'"

A heck of a lot less work, and it enables you to have a editable form. And,
you only wind up writing about 2 lines of code....

 

[John]

It just means the whole table comes to the client. I was hoping to filter by
status at server to lessen network load.

Thanks

Regards

 

[Albert D. Kallal]

It just means the whole table comes to the client. I was hoping to filter
by status at server to lessen network load.

Where did you hear such nonsense and rubbish? It does not pull the whole
table in.

in fact, if you using JET or sql server, in BOTH CASES ONLY THE ONE RECORD
is pulled down the network.

So, I not sure where you head such silly folklore, but you are 100% dead
wrong....