[AccessD] Updating a specific record using VBA.

Other than the redundant trailing semi colon and the incorrect tablename, your SQL now evaluates to what I posted previously in this thread.  I did not specify the dbFailOnError parameter, suggesting instead a check on the database object variable's RecordsAffected property since that allows one to confirm the update and hence, a means to verify the absence of an error.  Also, there was an unrectified bug acknowledged by M$ relating to the use of the dbFailOnError parameter against ODBC tables with Jet 3.5 and 3.51:  http://support.microsoft.com/kb/195226.  It can also trigger an IPF and crash Access with older operating systems: http://support.microsoft.com/kb/180348.  dbFailOnError is useful for development purposes because it will give a developer a meaningul error message explaining the reason for the error and provide a debugging tool.  The error is far less useful to a user as it is too cryptic.  For these reasons, I nearly always get rid of the parameter prior to delivery.
 
If you don't know how many records should be affected, which is not the case here, then the dbFailOnError raises a trappable error that can be used to do something like roll back a transaction should a block of records all need to be processed concurrently.
CiaoJürgen WelzEdmonton, Albertajwelz at hotmail.com> From: bheid at sc.rr.com> To: accessd at databaseadvisors.com> Date: Thu, 23 Oct 2008 21:57:22 -0400> Subject: Re: [AccessD] Updating a specific record using VBA.> > Darryl,> > Glad to help. I generally prefer to use the sql/db.execute method over the> .edit/.add and .update methods. I believe in many cases that the sql> methods are faster. But the .edit/.add methods do have their place.> > Just to make the example complete, to insert a new record via SQL would be> something like:> > 'No recordset needed> Dim db as dao.database> Dim sql as string> > Set db=currentdb()> > 'the following does not handle quotes in the text fields> Sql="INSERT INTO tablename (APLIDLoadedNumber, APLIDLoadedDesc,> APLIDasString) " & _> "VALUES (" & _> lngAPLID & ", " & _> "'" & strProgName & "', " & _> "'" & strAPLID & "');"> > Db.execute sql,dbfailonerror> > Db.close> Set db=nothing> > The thing with the above code is that you have to requery the db to get the> ID if you need it after insertion. In that case I would use the method that> you originally used.> > In looking at my original example, I left out a couple of quotes. Here's> the corrected version of the sql:> > Sql="UPDATE tablename " & _> "SET APLIDLoadedNumber = " & lngAPLID & ", " & _> " APLIDLoadedDesc = '" & strProgName & "', " & _> " APLIDasString = '" & strAPLID & "' " & _> "WHERE KeyID = " & lngMyKeyID & ";"> > Thanks,> Bobby
_________________________________________________________________