Access 97, ODBC, no error with insert but new row doesn't happen

[dmcdivitt]

I'm pointing an Access 97 app to an SQL Server backend. Connections are of course ODBC. Since people will want to see the code I'll post it:

Dim IntakeKey&, InCase$[FONT=monospace]
[/FONT]IntakeKey = 3409[FONT=monospace]
[/FONT]InCase = "1311-059"[FONT=monospace]
[/FONT]CurrentDb.Execute "INSERT INTO Intake (InUnique,InCase) VALUES (" & IntakeKey & ",""" & InCase & """);"

I placed the line of code in a new form, closed Access, opened again, relinked tables, and ran. No error is produced but the row does not appear in the table. If I open the table, scroll to the bottom, and insert manually, the row is created. I'm able to insert rows in other tables similar to the above without issue. This is a really weird deal. The SQL Server on my desktop is 2008 R2, but the server the production database will be added to is 2000. Help would be appreciated.

 

[MarkK]

I think the you need to explicitly tell the execute method to fail if there is an error. Try ...

CurrentDb.Execute _
  "INSERT INTO Intake " & _
    "( InUnique, InCase ) " & _
  "VALUES " & _
    "( " & IntakeKey & ",'" & InCase & "');", [COLOR="DarkRed"]dbFailOnError[/COLOR]

... and see if you get a trappable error.

 

[dmcdivitt]

Thanks! Never had to use that before. Way back when, with ODBC, I always used runsql, but since started using execute. But execute has always failed without that. Maybe ODBC is different.

 

[MarkK]

Yeah, that execute method doesn't raise data errors to VBA unless you tell it too. You can also check the RecordsAffected property of the database to see if your data operation succeeded, but then you need a database variable too...

dim dbs as dao.database
set dbs = currentdb
dbs.execute "DELETE FROM Table" [COLOR="Green"]'not using dbFailOnError[/COLOR]
if dbs.recordsaffected = 0 then 
[COLOR="Green"]  'checking .recordsaffected instead[/COLOR]
  msgbox "Using RecordsAffected to determine success!!!", vbInformation
end if

...or a with block would work too

with currentdb
  .execute ...
  msgbox .recordsaffected
end with

Cheers,