too slow database update

hi,

Currently I'm programming a reservation tool for a hotel. This includes a graphical layout (vbscheduler 2.0) where you can click on a kind of calendar to reserve a chamber for a day. When you click there is an entry made in the database (ms-access), now this takes about 2-3 seconds to update...

way to slow... the problem is when you click you get a label which you can resize... when you resize, the database gets updated...

so if you resize to fast, or click to fast, you get screwed up reservations...

now the question is... how to make the database update faster? Im using ado and ms-jet engine, changing the database (for example to my-sql) is not an option :s (project too far)

edit: ms-access db is on local machine

add code:
ds.AddNew
ds("v_res_id") = reservatieNr ' invullen van het reservatienummer
ds("v_i_id") = CrntData(c).ResNumber ' invullen van het item id
ds("v_i_color") = CrntData(c).dColor
ds("v_i_index") = CrntData(c).dIndex
ds("v_i_text") = CrntData(c).dText
sd = ConvToValue(Sch(Idx), CrntData(c).yGrpNum, 0)
st = ConvToValue(Sch(Idx), CrntData(c).xStart, 1)
ds("v_vandatum") = sd + Int(st) 'opslaan van datum
ds("v_vanmoment") = st - Int(st) 'opslaan van het uur
et = ConvToValue(Sch(Idx), CrntData(c).xEnd + 1, 1)
ed = ConvToValue(Sch(Idx), CrntData(c).yGrpNum, 0)
ds("v_totdatum") = ed + Int(et)
ds("v_totmoment") = et - Int(et)
ds("v_gevr") = 1
ds.Update
end add code
modify code
ds.MoveFirst
ds.Find "v_i_index = '" & CrntData(c).dIndex & "'"
If ds.EOF Then
MsgBox "error"
Else
ds("v_i_id") = CrntData(c).ResNumber
'ds("ItemResName") = CrntData(c).ResName
ds("v_i_color") = CrntData(c).dColor
ds("v_i_text") = CrntData(c).dText
sd = ConvToValue(Sch(Idx), CrntData(c).yGrpNum, 0)
st = ConvToValue(Sch(Idx), CrntData(c).xStart, 1)
'sd = sd + Int(st)
ds("v_vandatum") = sd + Int(st)
ds("v_vanmoment") = st - Int(st)
sd = sd + st
CrntData(c).dIndex = Year(sd) & Month(sd) & Day(sd) & Hour(sd) & Minute(sd) & Second(sd) '& CrntData(c).ResNumber
ds("v_i_index") = CrntData(c).dIndex
ed = ConvToValue(Sch(Idx), CrntData(c).yGrpNum, 0)
et = ConvToValue(Sch(Idx), CrntData(c).xEnd + 1, 1)
ds("v_totdatum") = ed + Int(et)
ds("v_totmoment") = et - Int(et)
ds.Update
End If
end modify code

connection:
Set cnDomein = New ADODB.connection
cnDomein.CursorLocation = adUseClient
cnDomein.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;data source=" & locatie & ";"

ds recordset:
sql = "SELECT * FROM verhuur WHERE v_totdatum >=" & sd & " and v_vandatum <=" & ed
Set ds = New ADODB.Recordset
ds.Open sql, db, adOpenDynamic, adLockOptimistic

Hi,

>the problem is when you click you get a label which you can resize... when you resize, the database gets updated...

A bit unclear what do you mean here ...
Also could you show the code where you update the table.
Is the Access database on the network or on your machine?

Shurik.

message edited (added code) and the access is on local machine

Well I still don't follow that sentence with label resizing (what doest it have to do with the updating/add new?)

For the rest if you are not satisfied with a 2 sec delay then you could try to execute
"INSERT INTO verhuur..."
"UPDATE verhuur ..."
sql against your connection object which is uin principle should be faster.


Shurik.

when you click you get a label (click creates database entry), that label you can resize, drag etc, after resize or move the database is updated... the label thingy is just how it looks on the screen

the problem is you can resize/move the label before the update or add is complete, which results in loss of the update(resize/move operation) or a double add etc...

well generally speaking one has to avoid 'firing' execution in such a way.
I can't see the layout of your program to be more concrete, but you have to
think about executing the sqls say in a _Click event of the button or the like.

I think you should use some kind of a boolean to check wether the mouse has been loosen or the dragging has stopped and call an updatefunction.

Eventhough you can check in you db if the particular record has been updated already I guess....

Another methode is to show a msgbox which asks the user if he's ready to update the database :D

BTW : If click the database entry creates, you probably use addNew which has been suggested (by Shurik12) to not being used: use INSERT statements on your connection which are faster ;)

problem solved:

first, to insert a new item I used SQL (thx dudes :D), its a LOT faster

and i changed the modify, delete to:
ds.Requery
ds.MoveFirst
ds.Find "v_i_index = '" & CrntData(c).dIndex & "'"

'while no record found, update/add/delete not complete, wait...
Do While ds.EOF
ds.Requery
ds.MoveFirst
ds.Find "v_i_index = '" & CrntData(c).dIndex & "'"
Loop

this code allows me to loop (wait) until the recordset is updated