Writing records from VBA module

[Paul Hanna]

I have constructed a database which uses VBA to write and update records in a
table. Unfortunately, I seem to be the only one for whom it works.
When other people use it on the network, data is not written to the table.
No error message appears and the pop-up information box (saying the data has
been written) does not show up, either. We have computers running on Windows
2000 and XP Pro, running Office XP and Office 2003. None of them write data,
except my computer which has Windows 2000 for the operating system and is
running Office XP, SP 3.
The database is split, record locking is on, and data entered on a data
entry form created through the form wizard writes data OK.
Do we need to update Visual Basic? Or what?
Any suggestions greatly appreciated.

 

[Tim Ferguson]

Unfortunately, I seem to be the only one for whom it works.
When other people use it on the network, data is not written to the
table. No error message appears and the pop-up information box
(saying the data has been written) does not show up, either.

How exactly are you writing the data? The safest way is something like

jetSQL = "INSERT INTO MyTable(One, Two) " + _
"VALUES (""Eric"", 57);"

db.Execute jetSQL, dbFailOnError


I am not strong with ADO, but it should go something like this:

adoSQL = "INSERT MyTable(One, Two) " + _
"VALUES ('Eric', 57)"

With cmd
.ActiveConnection = CurrentProject().Connection
.CommandType = adCmdText ' or whatever
.CommandText = adoSQL

If .ActiveConnection.Errors.Count > 0 Then Raise ' something

End With


.... which is another reason to use DAO rather than ADO ..! If you use
methods like DoCmd.RunSQL then you can switch off user warnings, but that
will switch off error warnings too. Without seeing the error code,
though, it's not possible to know whether it's a SQL syntax error, or a
database specific one like a key violation, etc.

Hope it helps


Tim F