[AccessD] Updating a specific record using VBA.

Jurgen,

I did not read through your original mail because it appears in my reader
(Outlook 2007) as one big line and I did not feel like reformatting it to
read it.  Anyone else getting Jurgen's emails as one big line (it does wrap,
but still).

I did not know about the ODBC issue with Jet.  I never use ODBC, but it is
good to know.

Thanks,
Bobby

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jurgen Welz
Sent: Friday, October 24, 2008 1:35 AM
To: Access Developers discussion and problem solving
Subject: Re: [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
_________________________________________________________________

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com