Take it back, I thought I fixed it.
Suddenly not working again :(
The way you should consider dealing with this is in SQL Server.
Using stored procedures where parameters are passed can result visually in a recordset of no 'depth' at all in other words typical 'snapshot' behaviour and that can be caused where multiple tables might be drawn together making a recordset 'uneditable' in the true sense on the application side.
Where this is likely to take place from a design perspective you should consider making use of the keyword
EXISTS on the server side stored procedure to return at least something to the client application that informs the user that in actual fact, 'no record exists' for the dataset requested. This is
NOT a workaround it is proper implementation of existing commands to ensure your application runs as you would want or expect.
A viable technique you should consider employing for yourself might be something like this:
Imagine you have a table called tblPerson consisting of Person_Id and PersonName. Two fields the primary being Person_Id (Go ahead create the table for yourself and populate it with data)
Now imagine you have a table called tblPersonAddress consisting of Person_ID and PersonAddress and AddressType.
(Address type might typically store information to describe the building ie: house, flat, Apartment, Commercial etc)
Person_Id in the tblPersonAddress table is the foreign key.
(Go ahead create the table for yourself and populate it with some data and include in it relevant numeric keys to enable a 'join' between Person_Id's from both tables)
Now imagine you have a table called tlkpAddressTypes with one single field called AddressType that contains descriptive data ie House,Flat,Apartment,Commercial
(Go ahead create the table and populate it with data sufficient to enable a join between AddressType(the primary in this table with AddressType the foreign key in tblPersonAddress)
When all
three of these tables are joined together in a 'View' or 'query' in Access then it becomes uneditable because updating/adding of data is only supported on
one side of a
two table join and even then you have to 'identify' the single table that can be updated in that
two table join statement
(this is done in an ADP file via the 'Unique Table' property setting of the form) The net effect then with more than two tables is that it becomes an unditable snapshot.
So lets have a look at the stored procedure that would return a dataset of at least 'something' that we could use in the ADP so as
NOT to give us the blank screen you speak of:
This a a stored procedure based around the previous example tables I mentioned. For readability/brevity I have made it relate to a query/view called qryPerson which basically is a join between all three tables. You might want to do that first and save as a view/query
- CREATE PROCEDURE dbo.usp_RetrievePerson
-
(@PName varchar(35))
-
AS
-
SET NOCOUNT ON
-
DECLARE @mycount bit
-
IF EXISTS (Select Person_Id, PersonName FROM dbo.qryPerson WHERE PersonName=@PName)
-
BEGIN
-
SELECT Person_Id, PersonName FROM dbo.qryPerson WHERE PersonName=@PName
-
END
-
ELSE
-
SELECT null as Person_ID, 'No Records' AS PersonName
-
GO
-
So what is this procedure doing? well by using the
EXISTS keyword it is testing to determine if at least ONE record exists basing itself on that SELECT statement with criteria. If the condition is satisfied then it executes the SELECT on the server and returns the appropriate dataset to the client.
We could you may think use COUNT but remember EXISTS exits out of the test as soon as it find at least 'one' record whereas COUNT would count the entire table which becomes a performance hit on large datasets.
If the EXISTS condition is NOT satisfied ie: there are no records resulting from the SQL statement, then the second SQL statement is executed as part of the ELSE logic and this returns a single row of data where null is returned, representing Person_Id together with the words
'No Records' representing the column data output for the field PersonName. Here then we have an aside benefit in that seeing a recordset like this on screen, serves if you like as a message to the user that no records exist the server having tested the criteria logic and returned nothing at all.
Hope this helps you by looking at 'method' in perhaps a different way. Obviously any implementation of this on your side requires adjustment of anything given here to suit you.
Regards
Jim :)
19
