Checking to see if record exsists BEST WAY?

[Ktx7ca]

HI everyone

I have a split Database in access2003 I'm working on with 3 to 5 users. The table I'm working with has around 350,000 records in it.

I want to check if a record exsists.and give a message or run some code either way. I don't want to display the info at this point just know if it's there.

Each user would be checking if some record exsists every few seconds.

what going to be the best way to do this for ease and speed over the network

1)Check the recordcount of a query?

2)Do a Dlookup (I think this would be slow) ?

3)Open a database or recordset in code ?

4)Or some better way that I havn't though of?

Any thoughts and implementation Ideas Welcome

Thanks Chris

 

[fneily]

There are two types of speed - actual and perceived. Sometimes what the user perceives outweighs the actual.
Some actual ways to improve performance:
Compact your database regularly. Databases become fragmented.
Increase Ram
Close other applications
Play with the Jet registry. Get a book on this.

Have a good database design. NORMALIZE!!!! If tables aren't well constructed, nothing will help.

Index fields you are searching on. Be careful not to use too many - adding new records can actually slow things down due to updating the indexes.

Limit the columns in your query. If it's not needed, don't add the field to the query.

Aggregate functions, such as Dlookup and DCount, slow down queries.

However, you're using the Jet engine. That is a file server engine, it transfers the whole table across the network and the work is done at the local computer. Obviously, this takes time for large tables and the EFFICIENCY of the network. You may want to talk to the network administrator for ideas.
You may want to look at using the MSDE engine. This is a client/server engine. You send the query to the server, work is done there and only the result is sent back. So less data transfer over the network.

Perceived speed - use an hourglass, display messages, etc.

Which way is best? Try it and which ever seems faster, then that's it. Or ask them, What's the hurry?

 

[TheAceMan1]

How are ya Ktx7ca . . .

[blue]1)Check the recordcount of a query[/blue] would be the fastest, however, since you require intermediary control, [blue]a recordset that looks at recordcount of a query (not SQL) is the next fastest! . . .[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Ktx7ca]

HI Aceman

I was leaning is that diection, so I made a query and put this code together but of course I get the Evil

"Run-Time error 3061 Too few parameters, Expected 1" error

The query works fine if I enter somthing in the critia area, It also works if I point it to the form and enter data in the text box when I click on the query it's self

but of course when I try it with my code it does not.

Dim Ghostcheck As dao.Recordset
Set Ghostcheck = CurrentDb.OpenRecordset("query6")

If Ghostcheck.RecordCount = 0 Then

MsgBox "blah", vbOKOnly

Else

MsgBox "YEAH", vbOKOnly

End If

not to surprising the error is on this line
Set Ghostcheck = CurrentDb.OpenRecordset("query6")

 

[TheAceMan1]

Ktx7ca . . .

For starters, you need to set CurrentDb as DAO as well:

[blue]   Dim db As dao.Database, Ghostcheck As dao.Recordset
   
   Set db = CurrentDb
   Set Ghostcheck = db.OpenRecordset("query6")[/blue]

Now, I'm assuming the code is running in the same form where the textbox supplies criteria. If this is true try the following:

[blue]   Dim db As dao.Database, Ghostcheck As dao.Recordset
   
   If Trim(Me!TextboxName & "") <> "" Then
      Set db = CurrentDb
      Set Ghostcheck = db.OpenRecordset("query6")
      
      If Ghostcheck.BOF Then
         [green]'Record Doesn't Exist Code![/green]
      Else
         [green]'Record Exist Code![/green]
      End If
      
      Set Ghostcheck = Nothing
      Set db = Nothing
   Else
      MsgBox = "TextboxName has no value!"
   End If[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[JoeAtWork]

Since you are only interested as to whether a record exists or not, I would try for a query that only ever returns 1 or no records, something like:

SELECT DISTINCT ProspectID FROM Donations WHERE ProspectID = 3

I never use RecordCount, as I have found it to be unreliable and often you need to move to the last record before it is populated.

The 100% accurate way of knowing whether your recordset has recordsets is checking both BOF and EOF,

Dim recordExists As Boolean
recordExists = Not (rs.BOF And rs.EOF)

However, most of the time I just use DLookup. Only if the delay is perceivable by the user would I bother optimizing it.

 

[fneily]

Apollo and Artemis (or is it Freyr and Freya?), the original post mentioned there were 350,000 records and "what going to be the best way to do this for ease and SPEED OVER THE NETWORK".
Neither addressed how to move the 350,000 records over to their respective code/SQL.

 

[JoeAtWork]

Neither addressed how to move the 350,000 records over to their respective code/SQL.

Seeing as this is Access and not SQL Server, the reading of 350,000 records is inevitable no matter what method is used. There's nothing that can be done programmatically to overcome this first required step.

Beefing up the network and upgrading to SQL Server (Express if he wants the current free version) are really the only solutions I see for faster network transfer. But I assumed he wanted an answer based on his current set up.

 

[gwinn7]

Joe at Work

Can you explain your statement...

"The 100% accurate way of knowing whether your recordset has recordsets is checking both BOF and EOF..."

I have never ever seen a situation where I was checking the existence of a record and needed to check the EOF too.

Btw, Dlookup is not a function I would use if you wanted to verify existence of a record in a table containing a lot of records. Instead I would write something like this...

dim rs as dao.recordset
set rs = currentdb.openrecordset(
"SELECT myfield FROM mytable WHERE"
"id = " & myid, dbopenforwardonly)
if rs.bof = true then
' Record does not exist!
else
' Record exists
end if

Explanation...

1- checking BOF is all you need (unless there is a valid argument otherwise!)
2- use the "dbopenforwardonly" is very fast considering DAO opens recordsets by default as editable dynasets which is more overhead. Or, you can use "dbopensnapshot".
3- you do not need to declare and assign a dao.database object, just call the "currentdb" function and save the memory for something else.
4- I don't use the "set rs = nothing" because I just let access handle the garbage collection. It just seems logical to me that Access should handle it better on its own anyway.
5- Limit the number of fields to return to 1, since you are only verifying the record's existence.

You can convert this into your own re-usable lookup function, like Dlookup. There are others out there like 'Elookup' which is really nice!

I also agree that RecordCount is not reliable, use your own method instead; dbopensnapshot is your friend!


Gary
gwinn7

 

[Ktx7ca]

HI Gary

I thought I'd give this a try , but beening monday my brains a little slow.... ok alot slow

"SELECT myfield FROM mytable WHERE"
"id = " & myid, dbopenforwardonly)

myfield= The field on my form with the data in it i'm checking for?

mytable = simple enough the table i'm looking in

id and myid one I gather is returned from the table

any chance you can clarify that for me

Thanks

 

[TheAceMan1]

How are ya gwinn7 . . .
[ol][li]I agree with you on not having to test [blue]EOF[/blue] as well to test for empty recordset.[/li]
[li]

[blue]Btw, Dlookup is not a function . . .[/blue]

[blue]DLookup Function

You can use the DLookup function to get the value of a particular field from a specified set of records (a domain). Use the DLookup function in Visual Basic, a macro, a query expression, or a calculated control on a form or report.[/blue]

[/li]
[li]

[blue]I don't use the "set rs = nothing" because . . .[/blue]

[blue]Nothing

The Nothing keyword is used to disassociate an object variable from an actual object. Use the Set statement to assign Nothing to an object variable. For example:

Set MyObject = Nothing

Several object variables can refer to the same actual object. When Nothing is assigned to an object variable, that variable no longer refers to an actual object. [purple]When several object variables refer to the same object, memory and system resources associated with the object to which the variables refer are released only after all of them have been set to Nothing[/purple], either explicitly using Set, or implicitly after the last object variable set to Nothing goes out of scope.[/blue]

[/li][/ol]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

gwinn7 . . .

Forgot one . . .

[blue]3- [purple]you do not need to declare and assign a dao.database object[/purple], just call the "currentdb" function and save the memory for something else.[/blue]

Since A2K and the introduction of [blue]ADO[/blue], when using recordsets (not clones), if you don't specify [blue]DAO or ADO[/blue], you'll raise an object error!

Try it! . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

Ktx7ca . . .

Make a query for what you've shown in your last post and use that instead. [purple]Its faster than SQL![/purple]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[JoeAtWork]

I prefer checking both BOF and EOF because it is the 100% sure way of knowing that there are no records in the recordset.

The only thing that you know for sure if just checking BOF is whether or not you are positioned before the first record.

Your experience may be that just checking BOF is sufficient, however I have not yet seen official documentation explicitly stating this. Maybe some day you will need to port an existing application to some obscure database that positions an empty recordset at EOF, or perhaps positions a populated recordset to BOF - either of which will break your code because of the assumption you made.

But really, my main reason is simply because I never like to use methods that rely on a "side-effect" of the particular programming language. I prefer to use the properties and methods in the way they were designed to behave. The fact that empty recordsets always seem to be positioned at BOF strikes me as convention, but not design.

For similar reasons I prefer using Nz rather than the Trim "trick" for handling possible Null values. The former was designed specifically for that work, the latter just happens to work - but you shouldn't rely on it because that's not what it was designed for.

Finally, using such "tricks" makes the code less readable. For example, a programmer who does not know the Trim "trick" will not realize that the value might be Null, and one who does not know the BOF "trick" will not interpret the logic as being "if no records".

 

[TheAceMan1]

[blue]1) The only thing that you know for sure if just checking BOF is whether or not you are positioned before the first record.[/blue]

[blue]When you [purple]first[/purple] open a Recordset object containing no records, the BOF and EOF properties are set to True, and the Recordset object's RecordCount property setting is 0.

2) When you [purple]first[/purple] open a Recordset object that contains at least one record, [purple]the first record is the current record[/purple] and the BOF and EOF properties are False;[/blue]

So . . . when you [purple]first[/purple] open a recordset, their either both true, [purple]or both false![/purple]

[blue]The above quote means, when you [purple]first[/purple] open a recordset, there's no way for just BOF or EOF alone to be true! If it ever does become true (when you [purple]first[/purple] open a recordset), then there's corruption in your installation.[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

To All . . .

[purple]first[/purple] in my prior post was added by me! . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[gwinn7]

Aceman,

RE: Dlookup
I know this is a function. I just indicated in my statement that I would avoid using it!

RE: Set ... Nothing
Of course I know what this is. I just don't think its necessary to include in most situations.

RE: .BOF
Thanks for confirming my assertion from Microsoft.

RE: Currentdb
Yes, agreed. I am coming from a DAO world and I was using KtX7Ca's posts about DAO. If you remove the ADO Reference and add DAO, then you don't need to specify it.

KtX7ca,

RE: myfield
This is an arbitrary column. Its only an example. Just pick one column to return since the SELECT statement will require at least one field.

Gary
gwinn7

 

[JoeAtWork]

Well, Aceman you provided the documentation I asked for, however I think I will still be using both BOF and EOF just because I like the explicitness of the syntax. Also I note in the same quote that they claim that RecordCount will be zero, which I know from experience will not always be true. So if they are incorrect about one property.....

Regarding the "set to Nothing" debate, this subject was pretty much flogged to death in the VB5/6 forum, and there StrongM pretty much convinced it was unnecessary in cases where the object will be going out of scope at the end of the procedure.

 

[Ktx7ca]

HI everyone there is some really good info in the posts and I thank you all.

I'm unable to get this code to work can any one see what I'm over looking?

THanks for all your Help

I'm still getting the

"Run-Time error 3061 Too few parameters, Expected 1" error

Dim db As dao.Database, Ghostcheck As dao.Recordset

If Trim(Me!Text49 & "") <> "" Then
Set db = CurrentDb
Set Ghostcheck = db.OpenRecordset("query6")

If Ghostcheck.BOF Then
'Record Doesn 't Exist Code!
MsgBox "Boooo", vbOKOnly

Else
'Record Exist Code!
MsgBox "YEAH", vbOKOnly

End If

Set Ghostcheck = Nothing
Set db = Nothing
Else
MsgBox "Enter something Fool", vbOKOnly

End If


Query6 info

SELECT Book.BookNumber
FROM Book
WHERE (((Book.BookNumber)=[Forms]![INS-Main]![Text49]));

any Idea's

 

[JoeAtWork]

Something is wrong with the parameter:

[Forms]![INS-Main]![Text49]

Are you sure that form is open, and that Text49 is not null?