Can''t get an updatable recordset on a form access/sql server
-
hello all,
I have converted an Access database to an access front end and a sql server backend. I have tried using various combinations of functions, stored procedures, views, etc to produce a user form with an updatable, SORTED recordset. The recordset must be generated using an input parameter. I would like to present the user with data ORDER by a filed and let them enter new data (new record) or modify the existing data in the records. Can you help??
Thank you in advance./
-
First of all, a primary key must be defined in the table when using a linked SQL Server table in Access for the table to be updatable. Access forms are updatable by default if the records they are linked to is updatable. If linked to SQL, the primary key must be defined. Define the primary key in the SQL table, then you will have to refresh the linked table (look in tools, database utilities, linked table manager).
You can create input parameters in Access queries and you can base your form on the query. Input parameters are set up by using brackets and a prompt in the criteria of the query. Here is an example:
select * from myTable where orderDate = [enter the date]
You can also use the Order by clause in the Access query, but is it easy for the user to modify the sort order of a form with a couple of clicks.
Access/SQL Server apps can be wonderful or can be a mess depending on how they are written. Make sure you filter the data, don't link a whole table to a form.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Thank you for your help. I believe I have a primary key defined in all of my tables in SQL server as well as all appropriate foreign keys. 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). 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
with rst
.activeconnection=currentproject.connection '??excuse syntax
.CursorType=adOpenKeyset
.CursorLocation=adUseClient
.LockType=adLockOptimistic (also BatchOptimistic)
.Open ("SELECT * FROM fnPtDiagnoses(--ChartNum--),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.
-
I have done lots of Access/SQL development but never got into the Access Projects. Is this a Project or the old fashioned mdb? My advice is for the old fashioned type, so diregard if you are using a project.
If you are using the mdb, you must link to the SQL tables in order to get the functionality you want.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Yes, it is an access data project. Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply