Want advice: SQL Server and ADO Seek -- how?

[javaguy]

I'm writing an Access application that will want to grow up and use SQL
Server someday.

I've a logic section in this multi-user app that says: My form was pointing
at this here record, known by its (auto-numbering) key value, that points at
a record in the (possibly filtered) recordset. I will press the "Next
Record" button, which will navigate to the "next" record in the recordset,
based on the current filter (where clauses) and indexes (order by). An
obvious thing to do is to make the recordset settle on that record and then
do MoveNext.

Suppose the "current" record was deleted by some other user. The (auto-
numbering) key value doesn't exist any more, and there is no record to settle
on. So I need some other method here.

I have a way of making this work when the indexes can be reduced to a single
key value:
select * from mytable (where filters) and (keyfield > previous_key_field)
order by keyfield

Suppose now that I want to sequence things by (last_name, first_name). If I
have Adam Smith, Betty Smith and Charley Smith, and I was on the (now deleted)
Betty Smith, how do I arrange things so that the "next" button moves me to
Charley Smith? In its more generic form, I have a form-dependent set of
index fields, and can't hard-code tests on index values ("select ... where
firstfield > firstvalue or (firstfield = firstvalue and secondfield >
secondvalue) or (firstfield = firstvalue and secondfield = secondvalue and
thirdfield...)...

With ADO Seek this is easy, as it honors multi-field indexes and has the
adSeekAfter directive. But I'm constantly informed that I can't combine ADO
Seek and SQL Server, even in its newest versions.

Perhaps I'm missing something important here...

 

[Sylvain Lafontaine]

First, this newsgroup is about ADP and SQL-Server and has nothing to do with
DAO, ODBC linked tables and other JET/MDB stuff; so you should better ask
your question in a newsgroup such as m.p.a.odbcclientsvr or
m.p.a.externaldata if you want an answer from an knowledgeable person.

Second, you're right, you can't combine SEEK with SQL-Server.

Third, you have a lengty explanation for something that should be simple
while using DAO, ADO, JET or SQL-Server. I think that you're making things
much more complicated that they are in reality and that all you need is a
little more experience.

 

[javaguy via AccessMonster.com]

OK. Sorry about "wrong posting". I don't even know what "ADP" is and
thought it meant "SQL Server and/or ADP". I'll move my discussion elsewhere.

First, this newsgroup is about ADP and SQL-Server and has nothing to do with
DAO, ODBC linked tables and other JET/MDB stuff; so you should better ask
your question in a newsgroup such as m.p.a.odbcclientsvr or
m.p.a.externaldata if you want an answer from an knowledgeable person.

Second, you're right, you can't combine SEEK with SQL-Server.

Third, you have a lengty explanation for something that should be simple
while using DAO, ADO, JET or SQL-Server. I think that you're making things
much more complicated that they are in reality and that all you need is a
little more experience.

I'm writing an Access application that will want to grow up and use SQL
Server someday.

[quoted text clipped - 38 lines]

Perhaps I'm missing something important here...

 

[Jamie Collins]

I'm writing an Access application that will want to grow up and use SQL
Server someday.

I've a logic section in this multi-user app that says: My form was pointing
at this here record, known by its (auto-numbering) key value, that points at
a record in the (possibly filtered) recordset. I will press the "Next
Record" button, which will navigate to the "next" record in the recordset,
based on the current filter (where clauses) and indexes (order by). An
obvious thing to do is to make the recordset settle on that record and then
do MoveNext.

Suppose the "current" record was deleted by some other user. The (auto-
numbering) key value doesn't exist any more, and there is no record to settle
on. So I need some other method here.

I have a way of making this work when the indexes can be reduced to a single
key value:
select * from mytable (where filters) and (keyfield > previous_key_field)
order by keyfield

Suppose now that I want to sequence things by (last_name, first_name). If I
have Adam Smith, Betty Smith and Charley Smith, and I was on the (now deleted)
Betty Smith, how do I arrange things so that the "next" button moves me to
Charley Smith? In its more generic form, I have a form-dependent set of
index fields, and can't hard-code tests on index values ("select ... where
firstfield > firstvalue or (firstfield = firstvalue and secondfield >
secondvalue) or (firstfield = firstvalue and secondfield = secondvalue and
thirdfield...)...

With ADO Seek this is easy, as it honors multi-field indexes and has the
adSeekAfter directive. But I'm constantly informed that I can't combine ADO
Seek and SQL Server, even in its newest versions.

Perhaps I'm missing something important here...

I don't quite understand what you are getting at. If you don't want to
see deleted records then don't do a dirty read! Requery the recordset
and the deleted row will disappear so that MoveNext will move to the
next non-deleted row.

I also don't get whether you are using WHERE in the SQL or Filter in
the recordset, ditto ORDER BY or Sort.

The last guy that asked about combining Sort and Seek in an ADO
recordset asked how to Seek in one recordset to grab the 'last' value
and use it to update a table via another recordset. The proposed
approach was to ditch fetching and updating via recordsets in favour
of a single SQL update (ANSI standard SQL).

Consider this contrived example (ANSI standard SQL, works in SQL
Server 2005 and Access/Jet in ANSI-92 Query Mode):

CREATE TABLE Persons
(
last_name VARCHAR(35) NOT NULL,
first_name VARCHAR(35) NOT NULL,
PRIMARY KEY (last_name, first_name),
last_entered_value INTEGER
)
;
CREATE TABLE MyTable
(
ID INTEGER IDENTITY(1, 1) NOT NULL,
last_name VARCHAR(35) NOT NULL,
first_name VARCHAR(35) NOT NULL,
FOREIGN KEY (last_name, first_name)
REFERENCES Persons (last_name, first_name),
some_value INTEGER NOT NULL
)
;
INSERT INTO Persons (last_name, first_name)
VALUES ('Smith', 'Adam')
;
INSERT INTO Persons (last_name, first_name)
VALUES ('Smith', 'Betty')
;
INSERT INTO Persons (last_name, first_name)
VALUES ('Smith', 'Charley')
;
INSERT INTO MyTable (last_name, first_name, some_value)
VALUES ('Smith', 'Adam', 10)
;
INSERT INTO MyTable (last_name, first_name, some_value)
VALUES ('Smith', 'Adam', 20)
;
INSERT INTO MyTable (last_name, first_name, some_value)
VALUES ('Smith', 'Adam', 30)
;
INSERT INTO MyTable (last_name, first_name, some_value)
VALUES ('Smith', 'Betty', 199)
;
INSERT INTO MyTable (last_name, first_name, some_value)
VALUES ('Smith', 'Betty', 299)
;
INSERT INTO MyTable (last_name, first_name, some_value)
VALUES ('Smith', 'Betty', 399)
;
INSERT INTO MyTable (last_name, first_name, some_value)
VALUES ('Smith', 'Charley', 55)
;

UPDATE Persons
SET last_entered_value =
(
SELECT MAX(M1.some_value)
FROM MyTable AS M1
WHERE M1.last_name = Persons.last_name
AND M1.first_name = Persons.first_name
)
WHERE EXISTS
(
SELECT *
FROM MyTable AS M1
WHERE M1.last_name = Persons.last_name
AND M1.first_name = Persons.first_name
);

While SQL Server supports the above ANSI standard UPDATE syntax,
Access/Jet does not, plus has a known bug when UPDATE is combined with
a set function (http://support.microsoft.com/kb/116142) so here's a
rare-for-me translation into proprietary Access/Jet SQL:

UPDATE Persons
SET last_entered_value =
DMAX("[some_value]", "[MyTable]", "[last_name] = '" & last_name & "'
AND [first_name] = '" & first_name & "'")

PS I know, I know: last_entered_value shouldn't be redundantly stored
but this is just an unrealistic example fabricated for the purposes of
demonstrating the update approach

Jamie.

--