DAO Error in VB2005 COM Interop implementation

[Paul Engel]

I have an application that must work with some old Access97 databases.
I have been able to accomplish the task w/ ADO.Net, but the
performance is about 25X slower. Since the program is working on the
same desktop as the Access database, I've tried to use .Net COM
interop. The variables seem to declare fine, and most of the code
looks like it is passing the IDE error check, but whenever I attempt
to update the value of a field using the .Fields() collection, either
using the index or the field name in quotes, the IDE flags the line as
an error: "Property 'Item' is 'ReadOnly'". I'm wondering if anyone out
there can spot what I am doing wrong. I've outlined my process and
code below w/ notations on the lines that show error highlighting...

I have set the DAO reference for my program and declared my variables
as follows:

Dim objAccessDB As DAO.Database
Dim objAccessRS As DAO.Recordset
Dim objDBEngine As New DAO.DBEngine

I set my Database object:

objAccessDB = objDBEngine.OpenDatabase(My.Settings.DataSource)

I populate my Recordset object:

strSQLStatement = "SELECT GroupNumber, GroupName " & _
"FROM tblGroups " & _
"Where GroupNumber = """ & Values(3) & """"

objAccessRS = objAccessDB.OpenRecordset(strSQLStatement, 2)
'dbOpenDynaset

Then I attempt to update or add records:

With objAccessRS
If .BOF And .EOF Then
.AddNew()
.Fields("GroupNumber") = Values(3)
*****ERROR IN IDE*******
.Fields("GroupName") = UCase(Values(4))
*****ERROR IN IDE*******
.Update()
Else
.Edit()
.Fields("GroupName") = UCase(Values(4))
*****ERROR IN IDE*******
.Update()
End If
End With

Thanks for any help,
Paul

 

[Armin Zingler]

I have an application that must work with some old Access97
databases. I have been able to accomplish the task w/ ADO.Net, but
the
performance is about 25X slower. Since the program is working on the
same desktop as the Access database, I've tried to use .Net COM
interop. The variables seem to declare fine, and most of the code
looks like it is passing the IDE error check, but whenever I attempt
to update the value of a field using the .Fields() collection,
either using the index or the field name in quotes, the IDE flags
the line as an error: "Property 'Item' is 'ReadOnly'". I'm wondering
if anyone out there can spot what I am doing wrong. I've outlined my
process and code below w/ notations on the lines that show error
highlighting...

....

An item in the Fields collection is a Field. You cannot change the field but
the Value property of the field:

.Fields("GroupNumber") = Values(3)

.Fields("GroupNumber").Value = Values(3)


BTW, I totally agree that ADO.net is unacceptably slow because the much
faster Edit/AddNew methods are not supported anymore. What a step back.


Armin

 

[Paul Engel]

DUH! I know better than that. Thanks. In my newer VB6 programs I ALWAYS use
the .Value instead of the default. I had copied this code from an old
program before I started applying better coding standards. Thanks for the
quick catch!

Paul