Re: ADP - ADO beginner Search Records with a Sp ~ help please
From: Mary Chipman (mchip_at_online.microsoft.com)
Date: 03/28/04
- Next message: Mary Chipman: "Re: Error Handling"
- Previous message: Candace: "Output fields"
- In reply to: John S.: "ADP - ADO beginner Search Records with a Sp ~ help please"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 28 Mar 2004 07:51:27 -0500
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
On 27 Mar 2004 12:03:26 -0800, jsanborn@phhinvestments.com (John S.)
wrote:
>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
- Next message: Mary Chipman: "Re: Error Handling"
- Previous message: Candace: "Output fields"
- In reply to: John S.: "ADP - ADO beginner Search Records with a Sp ~ help please"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
