RecordSet & New Class - Good idea?

Hi All,

I have the following situation:

A database held in an access mdb file. The data is pulled out into an
excel front-end by use of a RecordSet object (CopyFromRecordSet
is used to populate a worksheet range).

There are about 1600 records currently (growing) and 32 fields per
record (about 50,000 data points).

Changes made to the worksheet are then transferred back to the mdb
using the update method of the recordset object. Users do not access
the worksheet directly - data is presented to them in a userform that
they amend if necessary and then update to the worksheet (which then
goes back to the mdb).

The reason for the Excel front end is that it already exists (the
original data was stored in a worksheet and later moved to an mdb due
to issues with shared workbooks being flaky), it works, and we don't
really want to invest time and effort to replicate something that is
already there.


Question:

Would it be better to avoid puttig the data into a worksheet range,
and instead create a new class object containing each of the records,
with the fields being properties of the objects?

Would it be faster / better?


Thanks for any comments you have. If you need more information,
please do post a question - I have tried to give the salient points
without going into too much detail, but not having used classes before
I may be missing a point somewhere!

Thanks,

Alan.

Alan,

Can I ask why you need to put the data in a worksheet anyway? You already
have the data in the ADODB class library, which allows you to access each
field using the recordset object. Surely there is no need to create an
additional class that will just replicate part of what you can do using the
recordset.

Robin Hammond
www.enhanceddatasystems.com

"Alan" <alan@alan.alan> wrote in message
news:O$7HspZjFHA.2444@tk2msftngp13.phx.gbl...
>
> Hi All,
>
> I have the following situation:
>
> A database held in an access mdb file. The data is pulled out into an
> excel front-end by use of a RecordSet object (CopyFromRecordSet
> is used to populate a worksheet range).
>
> There are about 1600 records currently (growing) and 32 fields per
> record (about 50,000 data points).
>
> Changes made to the worksheet are then transferred back to the mdb
> using the update method of the recordset object. Users do not access
> the worksheet directly - data is presented to them in a userform that
> they amend if necessary and then update to the worksheet (which then
> goes back to the mdb).
>
> The reason for the Excel front end is that it already exists (the
> original data was stored in a worksheet and later moved to an mdb due
> to issues with shared workbooks being flaky), it works, and we don't
> really want to invest time and effort to replicate something that is
> already there.
>
>
> Question:
>
> Would it be better to avoid puttig the data into a worksheet range,
> and instead create a new class object containing each of the records,
> with the fields being properties of the objects?
>
> Would it be faster / better?
>
>
> Thanks for any comments you have. If you need more information,
> please do post a question - I have tried to give the salient points
> without going into too much detail, but not having used classes before
> I may be missing a point somewhere!
>
> Thanks,
>
> Alan.
>
>
>
>
>

"Robin Hammond" <rjNOrhSPAM@PLEASEnetvigator.com> wrote
in message news:%23JC13hbjFHA.3300@TK2MSFTNGP15.phx.gbl...
>
> Alan,
>
> Can I ask why you need to put the data in a worksheet anyway? You
> already have the data in the ADODB class library, which allows you
> to access each field using the recordset object. Surely there is no
> need to create an additional class that will just replicate part of
> what you can do using the recordset.
>
> Robin Hammond
> www.enhanceddatasystems.com
>

Hi Robin,

Good question - I may be missing the point entirely!

I was thinking that it would allow me to apply 'custom methods' to the
individual records.

The dataset is a client project tracking database. One example of
what I was thinking is that I could have a method akin to:

MyProject.Start NOW()

Which would update the 'Project_Started' field with the current date /
time.

At the moment, with the data in a worksheet, I use a range / offset
function to update the cell value.

One of the issues with that, is that if a new field is added in the
middle of the existing fields, then I have to maintain the code
throughout to change the offsets (all the fields in the worksheet to
the right of the new field shift across one row).

Now, I *could* just create constants that record the field offset
values in one place and then change them once if necessary. That is
what I was looking at doing, when I suddenly though perhaps a class
object might be a better approach.

Would the existing ADODB.Recordset class do everything I need already?

Thanks,

Alan.

Alan,

the specs seem to have changed a bit from your first question...

First, there's no reason that a standard sub cannot handle data changes
without having to have anything in the workbook. You could put this in a
class if you want, which might make it more reusable, but I don't really see
the need.

Adding fields can be problematical if you are doing it remotely. Hopefully,
you mean that somebody has added a field at the db, which then appears in
your worksheet data potentially screws things up severely in your code.

So, by using recordset objects, and not using a worksheet, you should be
able to refer to the new field purely by name, e.g. rsData("MyNewField"),
which is a lot safer than having to worry about hard coded cell locations. A
few lines are usually all it takes to handle the new field.

The corollary is that if you must use a worksheet, I'd name every column so
that you can use named ranges to refer to the data rather than the offsets
you are now using.

To argue the flip side, reasons that you might want to use a worksheet would
be:
you want to edit the data directly in the sheet;
you need the excel maths engine to perform calculations on or analysis of
the data;
you want to be able to view the data in one go in easy tabular form
(although it is easy enough to dump it onto a sheet to do this without
having any other functionality involved)

Not sure if I've helped much, but good luck with it.

Robin Hammond
www.enhanceddatasystems.com


"Alan" <alan@alan.alan> wrote in message
news:%23oklPBMkFHA.3064@TK2MSFTNGP15.phx.gbl...
> "Robin Hammond" <rjNOrhSPAM@PLEASEnetvigator.com> wrote
> in message news:%23JC13hbjFHA.3300@TK2MSFTNGP15.phx.gbl...
>>
>> Alan,
>>
>> Can I ask why you need to put the data in a worksheet anyway? You
>> already have the data in the ADODB class library, which allows you
>> to access each field using the recordset object. Surely there is no
>> need to create an additional class that will just replicate part of
>> what you can do using the recordset.
>>
>> Robin Hammond
>> www.enhanceddatasystems.com
>>
>
> Hi Robin,
>
> Good question - I may be missing the point entirely!
>
> I was thinking that it would allow me to apply 'custom methods' to the
> individual records.
>
> The dataset is a client project tracking database. One example of
> what I was thinking is that I could have a method akin to:
>
> MyProject.Start NOW()
>
> Which would update the 'Project_Started' field with the current date /
> time.
>
> At the moment, with the data in a worksheet, I use a range / offset
> function to update the cell value.
>
> One of the issues with that, is that if a new field is added in the
> middle of the existing fields, then I have to maintain the code
> throughout to change the offsets (all the fields in the worksheet to
> the right of the new field shift across one row).
>
> Now, I *could* just create constants that record the field offset
> values in one place and then change them once if necessary. That is
> what I was looking at doing, when I suddenly though perhaps a class
> object might be a better approach.
>
> Would the existing ADODB.Recordset class do everything I need already?
>
> Thanks,
>
> Alan.
>
>
>
>
>
>
>

"Robin Hammond" <rjNOrhSPAM@PLEASEnetvigator.com> wrote
in message news:uI346VNkFHA.572@TK2MSFTNGP15.phx.gbl...
>
> Alan,
>
> the specs seem to have changed a bit from your first question...
>

Apologies - I probably oversimplified and combined with my lack of
understanding in the first instance I probably missed out important
information.

>
> First, there's no reason that a standard sub cannot handle data
> changes without having to have anything in the workbook. You could
> put this in a class if you want, which might make it more reusable,
> but I don't really see the need.
>

I guess upon reflection I was just thinking that it would allow me to
go for a 'custom method' such as:

MyProject.Start NOW()

where MyProject is a member of the new class, and Start is a method of
that class.

Another option would be perhaps to use subs:

Start(MyRecord, Now())

Where Start is a sub that takes the record (MyRecord) and Date as
arguments.

The former seems more 'object oriented'? Am I trying to make things
more complicated than necessary?

>
> Adding fields can be problematical if you are doing it remotely.
> Hopefully, you mean that somebody has added a field at the db, which
> then appears in your worksheet data potentially screws things up
> severely in your code.
>

Definately!

Any fields to be added would be done in the database (an MDB) and
users would only add data to the field in individual records (if they
weren't populated automatically when the field was added).

>
> So, by using recordset objects, and not using a worksheet, you
> should be able to refer to the new field purely by name, e.g.
> rsData("MyNewField"), which is a lot safer than having to worry
> about hard coded cell locations. A few lines are usually all it
> takes to handle the new field.
>
> The corollary is that if you must use a worksheet, I'd name every
> column so that you can use named ranges to refer to the data rather
> than the offsets you are now using.
>

Good idea, but I am definately bent on removing the worksheet from the
scene now!

>
> To argue the flip side, reasons that you might want to use a
> worksheet would be:
> you want to edit the data directly in the sheet;
> you need the excel maths engine to perform calculations on or
> analysis of the data;
> you want to be able to view the data in one go in easy tabular form
> (although it is easy enough to dump it onto a sheet to do this
> without having any other functionality involved)
>

None of those apply, except the last, and as you point out
CopyFromRecordset is fine for export / reporting purposes if necesary.

>
> Not sure if I've helped much, but good luck with it.
>
> Robin Hammond
> www.enhanceddatasystems.com
>

Immeasurably. I really appreciate your responses.

Thanks again,

Alan.