Updateable recordset, please?

[Edward]

I've recently migrated the back end of one of my client's applications
from Access to SQL Server.

One of the forms is based on an Access query thus:

SELECT dbo_tblDistribu tionDetail.*,
dbo_tblDistribu tionMaster.fldD ocumentType,
dbo_tblDistribu tionMaster.fldD ocumentID, dbo_tblDistribu tionMaster.ts
FROM dbo_tblDistribu tionMaster INNER JOIN dbo_tblDistribu tionDetail ON
dbo_tblDistribu tionMaster.fldD istributionID =
dbo_tblDistribu tionDetail.fldD istributionID
WHERE (((dbo_tblDistr ibutionDetail.f ldCurrent)=True ));
At run time, the form's Form_Load event contains the following code:

Me.RecordSource = "SELECT * FROM qryDistribution WHERE (" &
Me.OpenArgs & ");"

Thus, typically, the ACTUAL RecordSource might be:

SELECT *
FROM qryDistribution
WHERE (dbo_tblDistrib utionDetail.fld DistributionID = 4072);

Under Access, I could make changes on the form and press the form's
Save button, whose code behind contained the line:

DoCmd.RunComman d acCmdSaveRecord

Now, under SQL Server, when I try to make a change on the form the
status bar shows

"This recordset is not updateable"

and if I press the "Save" button I get error number 2046 "The command
or action 'SaveRecord' isn't available now."

I have added Timestamp fields to both SQL Server tables referenced in
the query, and these are included in the query itself.

One obvious way around this would be to unbind the form, populate the
form's controls explicitly, and then save the data to the correct
tables using SPROCs or in-line SQL. However, if there is an easier
way, I'd like to know it.

Many thanks in advance

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

For each table to be updateable the front-end must be able to "see" a
unique value column(s) in the table (usually the Primary Key or a
TimeStamp column). You do not need to include that column in your
queries. Try unlinking your SQL tables and re-linking them. When
Access cannot recognize the unique column in the SQL table it will
prompt you for that column(s) in a pop-up dialog box w/ a list of
columns. If you see this prompt you'll know there isn't a Primary Key
or unique TimeStamp column in that table, and, therefore, you need to
create that uniqueness. Or, in the prompt, you can indicate the
column(s) you believe will always hold unique values.

Queries that use more than one table, and update values in those tables,
must have all the unique columns and required columns for all tables in
the query SELECT list.

Since you're using Access/JET query syntax try using the DISTINCTROW
keyword in the SELECT clause:

SELECT DISTINCTROW col1, col2, col3, ... etc. ...

This sometimes makes queries updateable.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQVHE+4echKq OuFEgEQLJ8gCgvS sa0olghyp9RcRQj nIrvNIrUlkAn058
GGu0iRknhF/FS1DSN3lq2Yao
=MEDk
-----END PGP SIGNATURE-----
Edward wrote:

I've recently migrated the back end of one of my client's applications
from Access to SQL Server.

One of the forms is based on an Access query thus:

SELECT dbo_tblDistribu tionDetail.*,
dbo_tblDistribu tionMaster.fldD ocumentType,
dbo_tblDistribu tionMaster.fldD ocumentID, dbo_tblDistribu tionMaster.ts
FROM dbo_tblDistribu tionMaster INNER JOIN dbo_tblDistribu tionDetail ON
dbo_tblDistribu tionMaster.fldD istributionID =
dbo_tblDistribu tionDetail.fldD istributionID
WHERE (((dbo_tblDistr ibutionDetail.f ldCurrent)=True ));
At run time, the form's Form_Load event contains the following code:

Me.RecordSource = "SELECT * FROM qryDistribution WHERE (" &
Me.OpenArgs & ");"

Thus, typically, the ACTUAL RecordSource might be:

SELECT *
FROM qryDistribution
WHERE (dbo_tblDistrib utionDetail.fld DistributionID = 4072);

Under Access, I could make changes on the form and press the form's
Save button, whose code behind contained the line:

DoCmd.RunComman d acCmdSaveRecord

Now, under SQL Server, when I try to make a change on the form the
status bar shows

"This recordset is not updateable"

and if I press the "Save" button I get error number 2046 "The command
or action 'SaveRecord' isn't available now."

I have added Timestamp fields to both SQL Server tables referenced in
the query, and these are included in the query itself.

One obvious way around this would be to unbind the form, populate the
form's controls explicitly, and then save the data to the correct
tables using SPROCs or in-line SQL. However, if there is an easier
way, I'd like to know it.