Cannot add records

[NevilleT]

I have a form that uses VBA to insert a record. Using an Access backend it
works fine. Using SQL Server, it does not work at all. No record created.
No error messages. Firstly I generate a string (strSQL) which I printed
using debug.print.

INSERT into tblTask(MSPID, ProjectNo) VALUES(1, 1);

I then have the following code.

Set dbs = CurrentDb

Set qdf = dbs.CreateQueryDef("", strSQL) ' Create
new QueryDef.
qdf.Execute dbSeeChanges
' Run the insert query

I suspect it is something wrong in how I set up SQL rather than the app.
Any suggestions?

[NevilleT]

Sorted it out myself. For the record, the problem was that there was a field
in the SQL table that would not allow nulls. In Access it did allow nulls.
There was no error message, but as soon as I added the field name and gave it
a value, the record was created. Hopefully this will save someone else hours
of anguish. SQL line now reads.
INSERT into tblTask(MSPID, ProjectNo, SummaryTask) VALUES(1, 1,0);

Post by NevilleT
I have a form that uses VBA to insert a record. Using an Access backend it
works fine. Using SQL Server, it does not work at all. No record created.
No error messages. Firstly I generate a string (strSQL) which I printed
using debug.print.
INSERT into tblTask(MSPID, ProjectNo) VALUES(1, 1);
I then have the following code.
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("", strSQL) ' Create
new QueryDef.
qdf.Execute dbSeeChanges
' Run the insert query
I suspect it is something wrong in how I set up SQL rather than the app.
Any suggestions?

[Tony Toews [MVP]]

Post by NevilleT
qdf.Execute dbSeeChanges

Also use the dbfailonerror variable.

Tony