Update Recordset with VBA

[Don Barton]

I am trying to update a table using VBA and SQL. A form is filled out
with user ID and PW, then these data are sent to global variables.
The global variables are used to create a record in tblUserActivity.

From what I've read of previous posts, this should be pretty straight

forward. Regarding the code below, everythings seems to be working
except when I get to the recordset.update. When execution occurs at
this point, my error routine fires. Is this a primary key field
issue? I have a primary key autonumber field defined for this
table.
------------------------------------------
Public Function WriteAuditTrail()
On Error GoTo Err_WATError

Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()

strSQL = "SELECT * FROM tblUserActivity;"
Debug.Print strSQL

Set rst = db.OpenRecordset(strSQL)
With rst
.AddNew
!PWUserName = gActiveUser
!PWDateTime = Now
!pwActivity = gUserActivity
!pwSecStatus = gSecStatus
.Update
End With

rst.Close

Exit_WriteAuditTrail:
Exit Function

Err_WATError:
MsgBox "There has been a error in recording UserInfo"
Resume Exit_WriteAuditTrail

Set rst = Nothing
Set db = Nothing

End Function

 

[tina]

well, i wouldn't bother opening a recordset at all, i'd just run an Append
query straight from VBA, as

CurrentDb.Execute "INSERT INTO tblUserActivity " _
& "( PWUserName, PWDateTime, pwActivity, " _
& "pwSecStatus ) SELECT '" & gActiveUser _
& "', #" & Now & "#, '" & gUserActivity _
& "', '" & gSecStatus & "'", dbFailOnError

the above code assumes that all variable values are String data type. if you
have any Number data types, remove the *single* quotes around the number
type variable(s).

having said all that, i didn't see anything wrong with the code you posted.
you didn't say what the actual error message is, which might be helpful (or
not). unless you're trying to write a value to the Autonumber field, i doubt
that the primary key has anything to do with the error. here are some things
to check: is each global variable declared as an explicit data type? if
not, they're dimmed as Variant, and then Access has to guess what the
correct data type is for each stored value - and it may guess wrong. if they
are declared with explicit data types, make sure the data types match the
table fields you're trying to insert the data into. also check the field
properties in the table itself - are there any validation rules applied that
the new data may be violating? or any table validations, ditto? also check
the table for unique indexes (besides the autonumber primary key), if there
are any, is the new record violating the index requirement? also check field
*size*, are you trying to insert 30 letters into a 25 letter field? or a
Long Integer value into a Byte-sized field?

hth

 

[Marshall Barton]

I am trying to update a table using VBA and SQL. A form is filled out
with user ID and PW, then these data are sent to global variables.
The global variables are used to create a record in tblUserActivity.
forward. Regarding the code below, everythings seems to be working
except when I get to the recordset.update. When execution occurs at
this point, my error routine fires. Is this a primary key field
issue? I have a primary key autonumber field defined for this
table.
------------------------------------------
Public Function WriteAuditTrail()
On Error GoTo Err_WATError

Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()

strSQL = "SELECT * FROM tblUserActivity;"
Debug.Print strSQL

Set rst = db.OpenRecordset(strSQL)
With rst
.AddNew
!PWUserName = gActiveUser
!PWDateTime = Now
!pwActivity = gUserActivity
!pwSecStatus = gSecStatus
.Update
End With

rst.Close

Exit_WriteAuditTrail:
Exit Function

Err_WATError:
MsgBox "There has been a error in recording UserInfo"
Resume Exit_WriteAuditTrail

Set rst = Nothing
Set db = Nothing

End Function
------------------------------------------

In addition to tina's excellent analysis, a couple of code
changes might help you track the problem down.

First, make sure you are opening a writable recordset:
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
or, without using strSL:
Set rst = db.OpenRecordset("tblUserActivity", dbOpenTable)

Then modify your exit and error handler code:
. . .
End With

Exit_WriteAuditTrail:
rst.Close : Set rst = Nothing
Set db = Nothing
Exit Function

Err_WATError:
MsgBox Err.Number & " - " & Err.Description", , _
"Error in recording UserInfo"
Resume Exit_WriteAuditTrail
End Function

OTOH, I like tina's idea of scrapping the recordset and
using an append query instead.

 

[tina]

yeah, i'm too lazy to bother with recordsets unless i have to! <g>
of course, if the problem is with the data being inserted, an Append query
won't work any better than a recordset update, so...

ps. thanks for the pat on the back, Marsh!

 

[Jamie Collins]

I like tina's idea of scrapping the recordset and
using an append query instead.

How would you like, then, going one step further and creating a
parameterized procedure at the engine level? e.g. one time only at
design time (aircode):

CurrentProject.Connection.Execute "CREATE PROCEDURE AddUserActivity ("
_
& " arg_PWUserName VARCHAR(35)," _
& " arg_pwActivity VARCHAR(20) = 'Development'," _
& " arg_pwSecStatus CHAR(7) = 'Current'") AS " _
& " INSERT INTO tblUserActivity (
& " PWUserName, PWDateTime, pwActivity, " _
& " pwSecStatus) VALUES (arg_PWUserName, NOW()," _
& " arg_pwActivity, arg_pwSecStatus);"

Advantages here include ease of maintenance (because the logic is in
the data layer not buried in VBA code in one front end...) and usage
(...and all users and front ends can/must use the same centralized
procedure), plus you get default parameter values when created using
SQL DDL.

At run time you could use Parameter objects (either ADO Command object
or DAO flavour) to pass strongly-typed parameter values -- after all,
you don't declare all your VBA variables as String, do you? ;-)

Jamie.

--