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" wrote in message
...

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" wrote
in message ...

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" wrote in message
...
"Robin Hammond" wrote
in message ...

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" wrote
in message ...

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.