ADO write not commiting to database

This seems about the best place for my query however it does cross over a bit ... dont shoot me.

SQL 2000 sp4 on server 2003

dev env VB6

users XP Sp2

Mdac 2.8

I have a legacy app that required some simple ammendment, insert a single row of data into a simple table (Not rocket Science) Sample : insert into maintcon_mach (maintcon_id, mach_id, date) values (123, 456, getdate())

PK on maintcon_id, mach_id so that single relationship exists between maintcon_id and mach_id in the table.

The procedure above is followed by a verification procedure that checks to see if table maintcon_mach has a mach_id 456 against maintcon_id 123, this procedure returns true. So far so good, however if you step through the procedure till disconnecting from the database and then reconnect and do only the verification again, the data is missing.

The following steps to resolve have resulted in no improvement.

1. Verify MDAC in entire project and on users computers all 2.8

2 Remove transactions (just in case) and still the same.

3 Replace SQL insert with ADO (As below) and still the same.. tried this with and without transactions.

4 Tried referecing older MDACs

5 Installed SQLredist on sample machines and still the same.

rs.Open "maintcon_mach", conMach, adOpenForwardOnly, adLockOptimistic
With rs
.AddNew
!maintcon_id = lngmaint
!mach_id = lngMachID
!Date = Format(Now(), "yyyy-mm-dd")
.Update
End With

Is the problem in SQL or MDAC?

Perhaps I've missed something (Obviously have) but have run out of places to look... Any idea's

In advance ...thanx

I have thought that perhaps there may be pending uncommitted transaction on the table, a server stop and restart was done and the results are still the same.?

Still confused...

|||Put the insert into a stored proc and OK?