Can''t edit or update ADO recordset

Question

Can''t edit or update ADO recordset

victor zayas-197939

SSC-Addicted

Points: 465
More actions
January 13, 2005 at 11:52 am

#165741

I have an access data project front end and a sql server backend. I can't seem to make a form for a user to enter new records or edit existing records from a query (from a user function)
I have a table DiagnosisCodes (DiagCode, Diagnosis) and another table PatientDiagnoses (DiagDate, DiagCode, ChartNumber) which contain all of the diagnoses made for all patients. This latter table is linked to the Patients table by ChartNumber which does not participate in the query/function. DiagCode is the primary key in DiagnosisCodes and a foreign key in PatientDiagnoses. I have a function fnPtDiagnoses which accepts ChartNumber as an input parameter and returns the DiagDate, DiagCode and Diagnosis (INNER JOIN) for a given patient. The function returns the appropriate recordset (and I can add new records to it if I run it as an sql query in enterprise manager) but I cannot bind it to an access form and have it be updatable. I use in ADO:
with rst
   .activeconnection=currentproject.connection    '??excuse syntax
   .CursorType=adOpenKeyset
   .CursorLocation=adUseClient
   .LockType=adLockOptimistic (also BatchOptimistic)
   .Open ("SELECT * FROM fnPtDiagnoses(--prmChartNum--),etc
end with
set me.recordset=rst
This produces the correct recordset in the form but I cannot add or edit records. Help? Thank you very much.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I would try to run the profiler and then try to do an update.

Then I would grad the command from the profiler that fails to update and run it in query analyser. You will get the correct error message that way.

Robert Stewart Hall of Fame Points: 3399 More actions · Answer 2

There is no way that what you are trying to do will ever work. You need to use a stored procedure or the table directly to bind an Access form.

You are also not using Access in the proper way. If you want to use your function, you will have to do the form as unbound and code all of the insert and update processes. There are ways of binding the form with parameters to a stored procedure. You can bind a form to a SQL statement, a view, a table, or a stored procedure. But not to a recordset you create in code.

Something to think about is that the support for ADPs is going to disappear. Future versions of Access will not have them and development efforts from MS as being concentrated back into the base MDB.

victor zayas-197939 SSC-Addicted Points: 465 More actions · Answer 3

Thank you for your reply. I am somewhat disconcerted to hear that the adp support will vanish. I hope this will not effect this project too much since I have converted all the code to ado and moved all the data retrieval/updating/deleting to sql server user functions and stored procedures.

After I posted my question, I was in fact able to bind a form to the recordset that I generated in code. The problem I had been having was that the query (parameterized user function) had an extra column that was not in the primary table and once I removed that column, I was able to update and add new records.

I had been trying to avoid coding all the insert and update procedures. Did I understand that there is a way to use an unbound form and send a parameter to a stored procedure from the unbound form?

Do you think that using this approach has long term disadvantages? Should I get out of access altogether? My concern is that this project has a number of very complex forms, including a multitab Patient information form with demographics, insurance info, medical data, photos, appointments, medications, etc. It also has weekly and day view calendars for appointments. What do you think I should use to create a good stable user interface?

Thanks again.