Subject: | |
From: | |
Reply To: | |
Date: | Mon, 26 Oct 2009 06:44:57 -0400 |
Content-Type: | text/plain |
Parts/Attachments: |
|
|
Hi Dave,
>
> Here is my code:
>
> Private Sub Get_Order_Detail(OrderId, Act_Amt)
>
> Dim Record_Count As Integer 'For testing
> Set dbs = CurrentDb()
> Set rstOrderDetail = dbs.OpenRecordset("Order Details")
> ' strCriteria is going to look like this - [OrderId] =
> '######' - where ######
> ' is the OrderId that came in on the call
> strCriteria = "[OrderId] = " & "'" & OrderId & "'"
> ' strCriteria = "[OrderId] = " & OrderId
> MsgBox ("I am going to look on the order detail for: " & strCriteria)
> Record_Count = 0
> Act_Amt = 0
>
> With rstOrderDetail
> MsgBox "Starting the find"
> .Find strCriteria
> MsgBox ("I found: " & .RecordCount)
> If .EOF Then
> MsgBox ("No order found for: " & strCriteria)
> Else
> Record_Count = Record_Count + 1
> MsgBox ("Reading Record #: " & Record_Count)
> Act_Amt = Act_Amt + rstOrderDetail.Unit_Price
> End If
> End With
> rstOrderDetail.Close
> MsgBox ("Number of Order Details found: " & Record_Count)
>
> End Sub
>
> Everything is good up until the .Find strCriteria. It is
> failing there because it
> never hits the MsgBox ("I found: " & .RecordCount) part of
> the logic. It just
> exits the subroutine without displaying a error message.
There are two ways to access an Access database: DAO and ADO. I would highly
recommend using ADO to do this. ADO (and ADO.Net) is the recommended way to
access data in variety of database providers. You can write your code for MS
Access but then move it to SQL Server or Oracle just by changing a few lines
of code. As you've seen, the .Find method does a serial read of the entire
table. In ADO, you create your SQL statement and you'll only read the
records that match your SQL WITH clause.
Some good examples at:
http://www.codedigest.com/Articles/ADO/44_Using_ADO_in_VB_and_Access.aspx
There are pros and cons for each method. IMHO, it all depends on if you plan
on always using MS Access or if you plan on moving into other .Net
technologies like ASP.Net. Here's a good discussion of
advantages/disadvantages of using DAO vs. ADO:
http://msdn.microsoft.com/en-us/library/aa164825%28office.10%29.aspx
Mark W.
* To join/leave the list, search archives, change list settings, *
* etc., please visit http://raven.utc.edu/archives/hp3000-l.html *
|
|
|