ADP - ADO beginner Search Records with a Sp ~ help please

[John S.]

I have about 2 million records. I orginally set the Record Source of
my main form to the entire table, made a basic search (code below) and
everything worked well, but I quickly realized that it was taking way
too much memory to do.

_____________________________________________
' find the record that matches the control

Dim rs As Object
Dim MyMsgBox As VbMsgBoxStyle
Dim stDocName As String
Dim stLinkCriteria As String

Set rs = Me.Recordset.Clone
rs.Find "[phone] = '" & Me![txtPhoneLookup] & "'"

If rs.EOF Then
MyMsgBox = MsgBox("That phone number is not in the database.
Do you want to add it?", vbCritical + vbYesNo, "Record Not Found")

If MyMsgBox = 6 Then
stDocName = "frmNewBor"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Me.txtPhoneLookup = Null
Me.txtPhoneLookup.SetFocus
Else

Me.Bookmark = rs.Bookmark

End If
___________________________________

SO I changed the Record Source to bring in only one record, then the
Search ^^^ wouldn't work.

So as a test - I tried to create a SP to base the database off of.
If I set this SP (code below) to the Record Source of the form, then
it will prompt me for the proper phone number and works for that just
fine (almost). It brings up the record that I want, and I can edit it
no problems. But I can't message box to ask the user if they want to
add a new record from that.

______________________________
ALTER PROCEDURE dbo.sprocProspects
(@Phone nvarchar(10))
AS SELECT ProspectID, fname, mi, address, lname, city, st, zip,
[+4], phone, fileID
FROM dbo.tblProspects
WHERE phone = @Phone
________________________________

SO my question is:

How do I make my search work with the Stored Procedure to where I can
prompt to add the record like I did before?

Or do I need a SP to do it? Perhaps I could use something like
this...

_____________________________
Private Sub txtPhoneLookup_AfterUpdate()
' find the record that matches the control

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

rst.Open "tblProspects", cnn, adOpenDynamic, adLockPessimistic

With rst

.Fields("phone") = Me![txtPhoneLookup]
.Update
.Close
End With
Set rst = Nothing

_______________________________

Only I can't seeem to get even that to work - Can anyone help me
please?

TIA for any replies,
JOhn Sanborn

 

[Mary Chipman]

You'll want to move away from the whole idea of using recordsets. That
adds another (slow) layer to your application that you don't need.
Instead, either write parameterized stored procedures with a WHERE
clause (don't forget to add SET NO COUNT ON) for searching, and you'll
need additional stored procedures for updating/adding/deleting. If you
elect not to go the stored procedure route (with is a lot more
difficult to write), then use direct SQL statements.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

I have about 2 million records. I orginally set the Record Source of
my main form to the entire table, made a basic search (code below) and
everything worked well, but I quickly realized that it was taking way
too much memory to do.

_____________________________________________
' find the record that matches the control

Dim rs As Object
Dim MyMsgBox As VbMsgBoxStyle
Dim stDocName As String
Dim stLinkCriteria As String

Set rs = Me.Recordset.Clone
rs.Find "[phone] = '" & Me![txtPhoneLookup] & "'"

If rs.EOF Then
MyMsgBox = MsgBox("That phone number is not in the database.
Do you want to add it?", vbCritical + vbYesNo, "Record Not Found")

If MyMsgBox = 6 Then
stDocName = "frmNewBor"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Me.txtPhoneLookup = Null
Me.txtPhoneLookup.SetFocus
Else

Me.Bookmark = rs.Bookmark

End If
___________________________________

SO I changed the Record Source to bring in only one record, then the
Search ^^^ wouldn't work.

So as a test - I tried to create a SP to base the database off of.
If I set this SP (code below) to the Record Source of the form, then
it will prompt me for the proper phone number and works for that just
fine (almost). It brings up the record that I want, and I can edit it
no problems. But I can't message box to ask the user if they want to
add a new record from that.

______________________________
ALTER PROCEDURE dbo.sprocProspects
(@Phone nvarchar(10))
AS SELECT ProspectID, fname, mi, address, lname, city, st, zip,
[+4], phone, fileID
FROM dbo.tblProspects
WHERE phone = @Phone
________________________________

SO my question is:

How do I make my search work with the Stored Procedure to where I can
prompt to add the record like I did before?

Or do I need a SP to do it? Perhaps I could use something like
this...

_____________________________
Private Sub txtPhoneLookup_AfterUpdate()
' find the record that matches the control

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

rst.Open "tblProspects", cnn, adOpenDynamic, adLockPessimistic

With rst

.Fields("phone") = Me![txtPhoneLookup]
.Update
.Close
End With
Set rst = Nothing

_______________________________

Only I can't seeem to get even that to work - Can anyone help me
please?

TIA for any replies,
JOhn Sanborn