Database access time

basically what this is for is a online/offline contact list. Users are stored in a database, and when the timer fires, it has to download the users contact list, then check each contact to see if there status is online or offline. would seeing the code possibly help:? Thanks!

rs.Open "select `contact` from `" & frmLogin.Text1.Text & "`", conn
rs.MoveFirst
Do Until rs.EOF = True
rs2.Open "select * from `basicreg` where `username` = '" & rs.Fields("contact").Value & "'", conn2
If rs2.Fields("status").Value = "online" Then
Set nodx = TreeView1.Nodes.Add("online", tvwChild, rs.Fields("contact").Value, rs.Fields("contact").Value)
Else
Set nodx = TreeView1.Nodes.Add("offline", tvwChild, rs.Fields("contact").Value, rs.Fields("contact").Value)
End If
rs2.Close
rs.MoveNext
Loop
conn2.Close
rs.Close
conn.Close

I noticed that you have two connection objects, is this because the two tables are in different databases?

Ok, first thing I would do is to break this code up into seperate routines. When the application starts up create the connection objects and leave them open until the application terminates. You should make them global to the application and you should have one for each database. All routines would then use those connection obnjects whenever they are needed. You don't want the connection time added to your routine every time you check the online/offline status. Next I would also create a global recordset object for the contact list table and initialize/open it when the user logs in. Keep it open until the user logs off or the application terminates. Then I would have two routines, one that initialy loads the treeview control with the entries from the contact list and use 'basicreg' to places the node in the approprate location based on status. Basically the code you have now but with the recordset and connection objects already open, and no close at the end. Next I would create a routine to be called by the timer; it simply runs through the list just like the load routine but instead of adding them to the TreeView control you can simply change the node location based on the status from the 'basicreg' table.

also if possible is there anyway i can talk to you on a messenger about this? got a couple quick questions and you seem to know alot about this stuff thanks!

so far the way i have it going if someone adds or removes a contact when the routine fires on the timer1_timer control, it automatically updates. so that parts working right now with only a couple contacts its pretty quick and doesnt bother much but when someone get say 25+ contacts its going to start taking a while im trying to avoid problems later down the road, any ideas? the ones you gave me so far are awesome im already working them into the program just wanna make sure i have this ready so we can start using it

yes this program will be the only way they access the database ( and its done entirely through the program, Thank you!

yeah i think we got that pretty much setup, only thing im noticing is as more contacts are added it does in fact take longer to do the sequence, but i guess thats something i can work out later, for now ive used a few suggestions of yours and so far it IS in fact a little quicker, ive got bigger fish to fry now like finding a users IP for instant messaging! if you know anything about that, give me a shout i havea few questions i may start a new thread for it. Thanks!