Non-Updatable CRecordSets when a Query Issued?

If I issue an Open in MSVC++ 6 for a CRecordSet for an Access database without any parameters, the RecordSet is Updatable (I want to delete records), however; if I issue a query in the Open fucntion, the RecordSet is not Updatable. Why? How can I correct this?

This allows the RecordSet to be Updated:
m_NGIP01ASet->Open();

This does not:
m_NGIP01ASet->Open(AFX_DB_USE_DEFAULT_TYPE, _T(QueryStr), CRecordset::skipDeletedRecords);
maknightAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GlennDeanCommented:
I believe instead of CRecordset::skipDeletedRecords use
CRecordset::none (this makes your recordset fully updatable, but is incompatible with skipDeletedRecords).
0
maknightAuthor Commented:
This doesn't work either. I've already tried it. The RecordSet is dynaset and the table only has 8 fields.
0
maknightAuthor Commented:
Below is my Query statement. I'm calling a stored Query within Access. The Query DOES work, but the Record Set is not Updatable.

CString QueryStr = "{call FindDataQuery1}";
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

cdesignerCommented:
may be
m_NGIP01ASet->Requery();
can help
?
0
GlennDeanCommented:
Humm... maybe your open fails?

The following code works on my computer:

m_pSet->Open(CRecordset::dynaset,NULL,CRecordset::none);
BOOL updatable = FALSE;
updatable = m_pSet->CanUpdata();
  //updatable is now TRUE
0
vachoohoCommented:
What is within your FindDataQuery1?

The problem may be with it query.
I will try to explain:

When you use more than 1 table in the query - it is possible that Access will not find way to update more than 1 table from recordset.

Look at this
SELECT * FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field2

Access 97 threat this as updatable recordset if you have member variables for ALL fields from both tables.

Thus I think the problem in your query.
Also try to use
SELECT * FROM FindDataQuery1

instead of {call FindDataQuery1}
Recordsets which use stored queries are not updatable.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
V_BapatCommented:
There is problem in your query. Can you post what is there in FindDataQuery1 ?
0
maknightAuthor Commented:
I'll post it later this evening. I'm not at a computer with the code on it. Thanks for all the comments thus far!!!
0
maknightAuthor Commented:
The Query as stored in Access (as best I can recall) is:

SELECT DataTable.* FROM DataTable (((WHERE DataTable.FromController = FALSE))) ORDER BY DataTable.KeyField, DataTable.Sequence

From within Access the Query works A-OK. If I put the actual Query statement in the Open function (instead of the "call" statement), it works OK except it does not perform the ORDER BY.
0
inprasCommented:
try using first parameter instead of DEFAULT to CRecordset::dynaset
inpras
0
vachoohoCommented:
ORDER BY can be implemented in CRecordset m_strSort member variable
m_pSet->m_strSort = "DataTable.KeyField, DataTable.Sequence";
m_strFilter = "DataTable.FromController = FALSE";
m_pSet->Open(CRecordset::dynaset,"Select * from [DataTable]");


Hope this helps


0
maknightAuthor Commented:
The part about Recordsets from stored queries not being updatable answered the question, and vachooho's follow-up helped tie it up. Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.