Jet to SQL conversion- What is equivalent ot "FindFirst" in a recordset?

[Geoff Taylor]

We are attempting to convert our existing Access jet database to MSSqL.

We have a Form on which is a Combo box that selects records for a subForm.
The current syntax for the Event procedure on the main form that selects the
records for the subform is as follows...
=========================================================================
Private Sub cbSearch_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone

rs.FindFirst "[SUTKEY] = " & Chr(34) & Me.cbSearch.Column(3) & Chr(34)

Me.Bookmark = rs.Bookmark

End Sub
=========================================================================

Under SQL, FindFirst is not available.
Any suggestions on how to achieve the same functionality with SQL?

 

[Brendan Reynolds]

This is not a difference between Jet and SQL, this is a difference between a
DAO recordset and an ADO recordset. Instead of separate methods for
FindFirst, FindNext, etc., ADO has a single Find method, the behaviour of
which is determined by the arguments passed to the method when it is called.
Details are in the help files, or on-line at ...
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthfindmethodado.asp

 

[Albert D. Kallal]

Under SQL, FindFirst is not available.
Any suggestions on how to achieve the same functionality with SQL?

What you do mean under sql it is not aviabling? I see no reason as to what
the above would not work? Are you talking about NOT using ms-access forms
here? (SQL server has NO ability to make forms for you). I have to assume
you are talking about contirung to use ms-access, but have palced the data
on sql server? Am I correct in this assuming?

If I am, and you simply have a form with a linked table, there is NO reason
at all that the findfist, and you example code will no funaciton. One the
other had, if you are taking about a convertgion gto a dbp proejct, then
NONE of you dao reocrdset code will work at all. If I was converting an
aplction to sql server, and i had a LOT of code in sma-ccess, then I would
use linked tables, and NOT use a adp rpeojct. this way, about 95% or more of
your code will contue to run, and not have to be re-writtging.

However, you code aqs you have it, and if you are talking about a regular
mdb file linked to sql server (no a acces data preojct), then the findfirs5r
and you code as is should just work fine. I would likey as a rule advoied
findfirst in your example for reasons of good perfoamcne, but there is no
reason why your code would not continue to run un-modifed as it is (assuming
you are using linked tables to sql server).

 

[Albert D. Kallal]

opps...bumped the send key....sorry

Under SQL, FindFirst is not available.
Any suggestions on how to achieve the same functionality with SQL?

What you do mean under sql it is not available? I see no reason as to what
the above would not work? Are you talking about NOT using ms-access forms
here? (SQL server has NO ability to make forms for you). I have to assume
you are talking about continuing to use ms-access, but have placed the data
on sql server? Am I correct in this assuming?

If I am, and you simply have a form with a linked table, there is NO reason
at all that the findfirst, and you example code will no function. One the
other had, if you are taking about a conversion to a adp project, then
NONE of you dao reocrdset code will work at all. If I was converting an
application to sql server, and I had a LOT of code in ms-access, then I
would
use linked tables, and NOT use a adp project. this way, about 95% or more of
your code will continue to run, and not have to be re-write.

However, you code as you have it, and if you are talking about a regular
mdb file linked to sql server (no a access data project), then the findfirst
and you code as is should just work fine. I would likely as a rule avoid
findfirst in your example for reasons of good performance, but there is no
reason why your code would not continue to run un-modified as it is
(assuming
you are using linked tables to sql server).

 

[Geoff Taylor]

Thanks for the info...

The setup is as follows...
Back end tables upsized to Microsoft SQL Server 2000 Desktop Engine.
Front end linked to the dbo tables on the SQL Server.

On the main form in the front end, I constantly received error on the
"findfirst" line, indicating it was not available.

However, I happened to copy and paste the sub from my original copy of the
front end.
Suddenly the error message no longer pops up and the subform now loads the
correct record.
I cannot see any differences in the code, but now works.

On the other hand, I cannot now change values in the linked tables.
Either opening the linked table directly, or via the forms, I cannot change
any values.