DAO: AddNew/Update Method problems

[Peter Zeman]

I am writing an application that uses VB6 as a front end and Access 97 as a back end. I have come across one problem. I have been unable to add a new record to a table in the database from VB. I have tried using all kinds of recordsets, including table-type and have been unsuccessful.

I have been successful with the Edit/Update methods with existing records that were added through Access.

It seems that the AddNew method does create an empty record with null values at the end of the recordset and that the data is being correctly assigned to the recordset from the textboxes. However the data fails to show up in the Access table. The table has an autonumber primary key and that number is incremented every time I try to add a record to the table. I am at a loss as to where the problem is and therefore, how to solve it.

Is the problem in the VB code or is it in the Access database. If anyone can help it would be appreciated. I have included the basics of my latest code for this procedure.

Set db = DBEngine.Workspaces(0).OpenDatabase(DBPath)
Set QuDef = db.QueryDefs("QueryName")
Set rs = QuDef.OpenRecordset()

rs.AddNew
rs![FirstField] = form.textbox(1).text
.
.
.
rs![LastField] = form.textbox(n).text
rs.Update
rs.Bookmark = rs.LastModified

Thanks in advance,

Peter

[Peterl]

This could be a number of things. Are you sure that you don't have an error such as a type mismatch happening and you are doing and on error resume next? Also check that you are not trying to manually add in the autonumber value.

[VorTechS]

Other things to look out for are things like: -

1. Not setting a text field to be greater than it's length

2. Not pasting NULL into a field which has the 'Allow Zero Length' property set to 'No'

Also, check how are you connecting to the access database. When using the 'MS DAO 2.5/3.5 compatibility' reference you can usually get away without setting values for some fields. Other references may not be so forgiving and may require that every field has a value set.