[AccessD] Setting data into and getting data from Named Range s

Hi Shamil

If I should guess your class is more suitable.
As I see it, it also overcomes the trouble when linking an Excel range containing varying or unknown data where the isam driver tries to guess for each column if it will contain text or numeric data. When you read the range directly to an array, you can do all sorts of validation before writing the array or use Variant datatype and validate after.

/gustav

>>> shamil at users.mns.ru 24-01-2006 20:12:17 >>>
Gustav,

My guess is that linking an MS Excel range and then reading it row by
row will be slower than getting whole range into array at once.

Another question is what is more suitable for a certain task:

- flexibility of reading MS Excel worksheet's range data using DAO/ADO
recordsets or
- speed of getting all the data of an MS Excel range into an in memory
array?

Of course for small data sets MS Excel instance start-up and workbook
opening will "eat" all the gains of getting MS Excel range into in
memory array...

Shamil

----- Original Message ----- 
From: "Gustav Brock" <Gustav at cactus.dk>
To: <accessd at databaseadvisors.com>
Sent: Tuesday, January 24, 2006 9:32 PM
Subject: Re: [AccessD] Setting data into and getting data from Named
Range s


> Hi Shamil
>
> Well, I just wondered .. 65K records isn't much for ADO and Access.
>
> Yes, to avoid linking is the goal as the servicepacks blocks update
and append.
> However, reading is still possible and if that is, say, 10 fold faster
than reading a range from Excel with your class it could be considered
an option to mix the two techniques. I have no idea .. perhaps it is the
other way around: For reading, your class is faster than linking??
>
> /gustav
>
> >>> shamil at users.mns.ru 24-01-2006 18:36:28 >>>
> Hi Gustav,
>
> Yes, MS Excel worksheet is limited to have 65536 rows and 256 columns
> AFAIK...
>
> "large recordset" is a relative term - it all depends of course - it
> should be small enough to fit a certain application reqirements for
> processing speed - for one application it can be 10 rows, for
another -
> 10000 rows....
>
> As for linking MS Excel worksheet range as an MS Access table - as far
> as I understood the main question/task to solve of this thread was to
> NOT use linked MS Excel worksheets because of licensing troubles...
>
> Shamil
>
> ----- Original Message ----- 
> From: "Gustav Brock" <Gustav at cactus.dk>
> To: <accessd at databaseadvisors.com>
> Sent: Tuesday, January 24, 2006 1:14 PM
> Subject: Re: [AccessD] Setting data into and getting data from Named
> Range s
>
>
> > Hi Shamil
> >
> > That's a nice and clean solution with the disconnected dataset.
> Thanks.
> >
> > But what is a "large recordset"? Isn't Excel limited to 65536 rows?
> > Would it be faster to link such a large range as a table in Access?
> That's what I use but I've never handled recordsets this way with more
> 100 rows.
> >
> > /gustav
> >
> > >>> shamil at users.mns.ru 24-01-2006 01:03 >>>
> > John,
> >
> > Have a look here is a generic code to get data to ms excel worksheet
> > from MS Access database and to save data to MS Access db from ms
excel
> > worksheet using disconnected ADO recordsets. This sample uses arrays
> > because an MS Excel range value is in fact a variant array. It
> shouldn't
> > be used with very large source recordsets but as far as I understood
> you
> > will not have such recordsets...