ACCESS TO SQL

[rob muzzy]

I currently have an MS Access DB that is modified semi monthly by our
engineers with specifications and revisions based upon our vendors
requirements it has 3 columns SPEC_ID, REV_ID, NEW_SPEC_ID. and I have
a view in SQL that grabs data from a binary table and converts it to
NVARCHAR, the table is called REQUIREMENT_BINARY and has a bunch of
columns the only column I am currently interested in is
REQUIREMENT.BITS which I am stripping all the data except for the data
between the [ ] (this is the data I am looking for.

What I would like to do is have access search its database and replace
through the view where the spec_id matches with the new_spec_id.
Example in the sql database it may say AMS-A-22771 REV - I would like
it to read in the SQL AMS-A-22771 and replace it with whats in the
ACCESS Database which may be
AMS-A-22771 REV - J


the code for my view in SQL is

SELECT
B3.*,
CASE WHEN ConditionedStartingPosition = 0
THEN 'NOT FOUND'
ELSE SUBSTRING(BITS,
ConditionedStartingPosition, ConditionedEndingPosition)
END AS MyAnswer
FROM (
SELECT
B2.*,
CASE WHEN StartingPosition <= 1 or
EndingPosition <= 0
THEN 0
ELSE StartingPosition
END AS ConditionedStartingPosition,
CASE WHEN StartingPosition <= 1 or
EndingPosition <= 0
THEN 0
ELSE EndingPosition
END AS ConditionedEndingPosition
FROM (
SELECT
B1.*,
CHARINDEX('[', BITS) +
1 AS StartingPosition,
CHARINDEX(']', BITS) -
CHARINDEX('[', BITS) - 1 AS EndingPosition
FROM (
SELECT

WORKORDER_TYPE,

WORKORDER_BASE_ID,

WORKORDER_LOT_ID,

WORKORDER_SPLIT_ID,

WORKORDER_SUB_ID,

OPERATION_SEQ_NO,

PIECE_NO,

[TYPE],
CASE
WHEN (UNICODE(CAST(CAST(BITS AS VARBINARY(4000)) AS NVARCHAR(4000))) -
ASCII(LEFT(CAST(BITS AS VARBINARY(4000)),1))) = 0

THEN CAST(CAST(BITS AS VARBINARY(8000)) AS NVARCHAR(4000))

ELSE CAST(CAST(BITS AS VARBINARY(8000)) AS VARCHAR(8000))
END AS
BITS,

BITS_LENGTH
FROM
REQUIREMENT_BINARY
WHERE TYPE =
'D'
) B1
) B2
) B3


Can Someone Help me?

Thanks

Rob