SQL Server method for user record selection

Question

I am migrating a large Access application to SQL Server.

One technique that I use in Access is to use a selection table in the frontend joined to a shared backend table to allow the user to select individual records. Once the user has specified the selection, I then use the selection as a criteria to update, export or report on the selected records.

The selection table simply consists of a foreign key field and a boolean field used to display a checkbox for the record selection.

Here is an example showing the equivalent of what I do in Access:

CREATE TABLE tblJob (
    JobNumber int NOT NULL,
    JobName nchar(255) NULL
)
ALTER TABLE tblJob ADD  CONSTRAINT PrimaryKey PRIMARY KEY CLUSTERED 
(
    JobNumber ASC
)
GO

INSERT INTO tblJob (JobNumber, JobName) VALUES (1, 'Job 1')
INSERT INTO tblJob (JobNumber, JobName) VALUES (2, 'Job 2') 
INSERT INTO tblJob (JobNumber, JobName) VALUES (3, 'Job 3') 
GO

CREATE TABLE tblSelect (
    SelectID int NOT NULL,
    Selected int NOT NULL
)
--CREATE UNIQUE NONCLUSTERED INDEX PrimaryKey ON tblSelect (
--  SelectID ASC
--)
ALTER TABLE tblSelect ADD  DEFAULT (0) FOR Selected
GO

-- Create a view joining the selection table tblSelect to the data table tblJob
CREATE OR ALTER VIEW vwJobSelected
AS
SELECT
    JobNumber, 
    JobName, 
    Selected
FROM tblJob 
    LEFT OUTER JOIN tblSelect ON tblJob.JobNumber = tblSelect.SelectID
GO


-- The application initialises tblSelect with the Job keys
DELETE FROM tblSelect
GO
INSERT INTO tblSelect(SelectID, Selected)
SELECT JobNumber, 0 FROM tblJob
GO

-- The user selects the second record via an editing form in the application
UPDATE vwJobSelected 
SET Selected=1 
WHERE JobNumber = 2
GO

-- Show the selected record(s)
SELECT * 
FROM vwJobSelected
WHERE Selected=1
GO

This works well in Access but will not work in SQL Server for the following reasons:

1. The selection tblSelect is in the frontend database and therefore is unique to the user/workstation/running instance of the application. However with the migrated database this would create a “heterongenous join” which would join the tables on the client instead of the server and therefore drag the whole data table tblJob across the network.

2. Access allows the query with the left outer join to be modified seamlessly. However SQL Server will not let you delete records since there are multiple base tables. This would require an INSTEAD OF trigger on the view.

So my question is: What is the recommend method for performing this user selection process in SQL Server?

I have considered:

a) Adding columns for UserName (= SUSER_NAME()) and Workstation (=HOST_NAME()) to tblSelect so that there are user/workstation specific selections available. However I think Access will require a unique index on SelectedID to keep the query updatable and this presents problems.

b) Temporary tables. However I get the impression that these only persist for the duration of a connection to the server and the Access application will surely disconnect and reconnect to the server within the duration of running a session.

c) Asking the good folk at Stack Overflow for advice! :-)

Kind regards

Neil Sargent