By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 1,191 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

Updateable recordset, please?

P: n/a
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_tblDistributionDetail.*,
dbo_tblDistributionMaster.fldDocumentType,
dbo_tblDistributionMaster.fldDocumentID, dbo_tblDistributionMaster.ts
FROM dbo_tblDistributionMaster INNER JOIN dbo_tblDistributionDetail ON
dbo_tblDistributionMaster.fldDistributionID =
dbo_tblDistributionDetail.fldDistributionID
WHERE (((dbo_tblDistributionDetail.fldCurrent)=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_tblDistributionDetail.fldDistributionID = 4072);

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

DoCmd.RunCommand 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
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
-----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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQVHE+4echKqOuFEgEQLJ8gCgvSsa0olghyp9RcRQjnIrvN IrUlkAn058
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_tblDistributionDetail.*,
dbo_tblDistributionMaster.fldDocumentType,
dbo_tblDistributionMaster.fldDocumentID, dbo_tblDistributionMaster.ts
FROM dbo_tblDistributionMaster INNER JOIN dbo_tblDistributionDetail ON
dbo_tblDistributionMaster.fldDistributionID =
dbo_tblDistributionDetail.fldDistributionID
WHERE (((dbo_tblDistributionDetail.fldCurrent)=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_tblDistributionDetail.fldDistributionID = 4072);

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

DoCmd.RunCommand 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.


Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.