PC Review


Reply
Thread Tools Rate Thread

How to dump recordset into a table ?

 
 
peter_blind@trashmail.net
Guest
Posts: n/a
 
      6th Jul 2007
Hello

I need somebodies help

I have a recordset, that I fill with data:

#--
Set check_64000 = CurrentDb.OpenRecordset(clipstr2)
--#

Now I need to export this recordset into a excelfile, but the only
methode I have found is "CopyFromRecordset", but the methode is really
slow.

Now I try to dump the recordset into a table, that has to have the
same fields and stuff, like my recordset. Then I'll use
"transferspreadsheet" to export it into a excelfile.


Question:
How do I create a table that matches my recordset, and how do I dump
the data into this table ?


thanks

bye

 
Reply With Quote
 
 
 
 
Steve Schapel
Guest
Posts: n/a
 
      6th Jul 2007
Peter,

What is 'clipstr2'? If it is a query, or else a query could be used
instead to return the same data, then you could use TransferSpreadsheet,
or (preferably) OutpoutTo, to export to Excel directly from the query.
This would be much more efficient.

--
Steve Schapel, Microsoft Access MVP

http://www.pcreview.co.uk/forums/(E-Mail Removed) wrote:
> Hello
>
> I need somebodies help
>
> I have a recordset, that I fill with data:
>
> #--
> Set check_64000 = CurrentDb.OpenRecordset(clipstr2)
> --#
>
> Now I need to export this recordset into a excelfile, but the only
> methode I have found is "CopyFromRecordset", but the methode is really
> slow.
>
> Now I try to dump the recordset into a table, that has to have the
> same fields and stuff, like my recordset. Then I'll use
> "transferspreadsheet" to export it into a excelfile.
>
>
> Question:
> How do I create a table that matches my recordset, and how do I dump
> the data into this table ?
>
>
> thanks
>
> bye
>

 
Reply With Quote
 
 
 
 
peter_blind@trashmail.net
Guest
Posts: n/a
 
      9th Jul 2007
Hello


Well........ I need to read the recordset out of the variable
check_64000. This is nessesary, because I use the
check_64000.recordcount to count the records first, before I use
transferspreadsheet.

If i'd do a second query with the same string named 'clipstr2' and
dump it directly into a table, then I'd calculate the same recordset
twice. This would be a waste of time, because the query is pretty big
and our DB holds about 2 million records.


So please......If you know a nice solution, pease let me know

Bye

Robert


Steve Schapel wrote:
> Peter,
>
> What is 'clipstr2'? If it is a query, or else a query could be used
> instead to return the same data, then you could use TransferSpreadsheet,
> or (preferably) OutpoutTo, to export to Excel directly from the query.
> This would be much more efficient.
>
> --
> Steve Schapel, Microsoft Access MVP
>
> (E-Mail Removed) wrote:
> > Hello
> >
> > I need somebodies help
> >
> > I have a recordset, that I fill with data:
> >
> > #--
> > Set check_64000 = CurrentDb.OpenRecordset(clipstr2)
> > --#
> >
> > Now I need to export this recordset into a excelfile, but the only
> > methode I have found is "CopyFromRecordset", but the methode is really
> > slow.
> >
> > Now I try to dump the recordset into a table, that has to have the
> > same fields and stuff, like my recordset. Then I'll use
> > "transferspreadsheet" to export it into a excelfile.
> >
> >
> > Question:
> > How do I create a table that matches my recordset, and how do I dump
> > the data into this table ?
> >
> >
> > thanks
> >
> > bye
> >


 
Reply With Quote
 
Steve Schapel
Guest
Posts: n/a
 
      9th Jul 2007
Robert,

My apologies, but I'm afraid you have lost me completely here.

I still don't know what 'clipstr2' refers to. Nor have you said what
'check_64000' is either, apart from it is a variable, but you don't read
a recordset out of a variable, you read a recordset out of a table or a
query.

And why do you need a recordset to count records? Why can't you use the
Count() function, or the DCount() function, or the TOP predicate?

And which records are you exporting? All of them? So in batches of
64000 records, one batch to each of x number of spreadsheets? (Maybe
investment in Excel 2007 would pay off in that case?)

So, if you can explain a little more detail, it will help. Thanks.

--
Steve Schapel, Microsoft Access MVP

(E-Mail Removed) wrote:
> Hello
>
>
> Well........ I need to read the recordset out of the variable
> check_64000. This is nessesary, because I use the
> check_64000.recordcount to count the records first, before I use
> transferspreadsheet.
>
> If i'd do a second query with the same string named 'clipstr2' and
> dump it directly into a table, then I'd calculate the same recordset
> twice. This would be a waste of time, because the query is pretty big
> and our DB holds about 2 million records.
>

 
Reply With Quote
 
peter_blind@trashmail.net
Guest
Posts: n/a
 
      10th Jul 2007
Hello

Sorry for the confusion. I try again

Step 1:
#dim clipstr2 as string
#dim check_64000 as recordset
#Set check_64000 = CurrentDb.OpenRecordset(clipstr2)

clipstr2 is a string containing a sql query("SELECT.....")
This string clipstr2 is being created during runtime, so it differs
every time I run the macro. So the query is never the same.

Step 2:
Now I have check_64000 the holds all the data, from the sql query. Now
I want to know how many records check_640000 holds. If it holds more
than a excel file is able to store,
then the macro quits(it does something else, bit this doens't matter).
The point is that I do not want to create a table using the clipstr2
query, until I know how many records my query clipstr2 will throw
back.
So I need to count the records. I use the method
check_64000.recordcount

Step 3:
If the number of records does not exceed 65536, than I'd like to
export the data stored in check_640000 to a excel file. If there is no
fast way to do this(I tried the method CopyFromRecordset), then I'd
like to store the data into a table, and export the data afterwards
using transferspreadsheet.


That's it.
1. Create recordset
2. Count records
3. Create excel file somehow, if the recordcount does not exceed 65536


Thanks

Steve Schapel wrote:
> Robert,
>
> My apologies, but I'm afraid you have lost me completely here.
>
> I still don't know what 'clipstr2' refers to. Nor have you said what
> 'check_64000' is either, apart from it is a variable, but you don't read
> a recordset out of a variable, you read a recordset out of a table or a
> query.
>
> And why do you need a recordset to count records? Why can't you use the
> Count() function, or the DCount() function, or the TOP predicate?
>
> And which records are you exporting? All of them? So in batches of
> 64000 records, one batch to each of x number of spreadsheets? (Maybe
> investment in Excel 2007 would pay off in that case?)
>
> So, if you can explain a little more detail, it will help. Thanks.
>
> --
> Steve Schapel, Microsoft Access MVP
>
> (E-Mail Removed) wrote:
> > Hello
> >
> >
> > Well........ I need to read the recordset out of the variable
> > check_64000. This is nessesary, because I use the
> > check_64000.recordcount to count the records first, before I use
> > transferspreadsheet.
> >
> > If i'd do a second query with the same string named 'clipstr2' and
> > dump it directly into a table, then I'd calculate the same recordset
> > twice. This would be a waste of time, because the query is pretty big
> > and our DB holds about 2 million records.
> >


 
Reply With Quote
 
Steve Schapel
Guest
Posts: n/a
 
      11th Jul 2007
Robert,

If you are building the clipstr2 Select Query in code, then presumably
you could equally easily build a corresponding Append Query in code, and
run it to add the records to a table, prior to your Excel export. Would
that work?

--
Steve Schapel, Microsoft Access MVP

(E-Mail Removed) wrote:
> Hello
>
> Sorry for the confusion. I try again
>
> Step 1:
> #dim clipstr2 as string
> #dim check_64000 as recordset
> #Set check_64000 = CurrentDb.OpenRecordset(clipstr2)
>
> clipstr2 is a string containing a sql query("SELECT.....")
> This string clipstr2 is being created during runtime, so it differs
> every time I run the macro. So the query is never the same.
>
> Step 2:
> Now I have check_64000 the holds all the data, from the sql query. Now
> I want to know how many records check_640000 holds. If it holds more
> than a excel file is able to store,
> then the macro quits(it does something else, bit this doens't matter).
> The point is that I do not want to create a table using the clipstr2
> query, until I know how many records my query clipstr2 will throw
> back.
> So I need to count the records. I use the method
> check_64000.recordcount
>
> Step 3:
> If the number of records does not exceed 65536, than I'd like to
> export the data stored in check_640000 to a excel file. If there is no
> fast way to do this(I tried the method CopyFromRecordset), then I'd
> like to store the data into a table, and export the data afterwards
> using transferspreadsheet.
>
>
> That's it.
> 1. Create recordset
> 2. Count records
> 3. Create excel file somehow, if the recordcount does not exceed 65536

 
Reply With Quote
 
peter_blind@trashmail.net
Guest
Posts: n/a
 
      11th Jul 2007
Sure it would...... but the tool I've create is suppose to create
excel files only. I am not really interested in creating tables inside
access. This is a waste of time, and I'll have to compress the access
DB after each run.

So the best way for me is the one described.
1.count records inside check_64000
2. fits to excel file?
3. if it does...dump data from check_64000 into excel, if not......
do something else


well






Steve Schapel wrote:
> Robert,
>
> If you are building the clipstr2 Select Query in code, then presumably
> you could equally easily build a corresponding Append Query in code, and
> run it to add the records to a table, prior to your Excel export. Would
> that work?
>
> --
> Steve Schapel, Microsoft Access MVP
>
> (E-Mail Removed) wrote:
> > Hello
> >
> > Sorry for the confusion. I try again
> >
> > Step 1:
> > #dim clipstr2 as string
> > #dim check_64000 as recordset
> > #Set check_64000 = CurrentDb.OpenRecordset(clipstr2)
> >
> > clipstr2 is a string containing a sql query("SELECT.....")
> > This string clipstr2 is being created during runtime, so it differs
> > every time I run the macro. So the query is never the same.
> >
> > Step 2:
> > Now I have check_64000 the holds all the data, from the sql query. Now
> > I want to know how many records check_640000 holds. If it holds more
> > than a excel file is able to store,
> > then the macro quits(it does something else, bit this doens't matter).
> > The point is that I do not want to create a table using the clipstr2
> > query, until I know how many records my query clipstr2 will throw
> > back.
> > So I need to count the records. I use the method
> > check_64000.recordcount
> >
> > Step 3:
> > If the number of records does not exceed 65536, than I'd like to
> > export the data stored in check_640000 to a excel file. If there is no
> > fast way to do this(I tried the method CopyFromRecordset), then I'd
> > like to store the data into a table, and export the data afterwards
> > using transferspreadsheet.
> >
> >
> > That's it.
> > 1. Create recordset
> > 2. Count records
> > 3. Create excel file somehow, if the recordcount does not exceed 65536


 
Reply With Quote
 
Steve Schapel
Guest
Posts: n/a
 
      11th Jul 2007
Robert,

(E-Mail Removed) wrote:
> Sure it would...... but the tool I've create is suppose to create
> excel files only. I am not really interested in creating tables inside
> access.


This was your idea, not mine.

> This is a waste of time, and I'll have to compress the access
> DB after each run.


This is not correct. You could create a temporary table on the fly.

> So the best way for me is the one described.


This is also incorrect.

In my opinion, the best way is to create a query and then export to
Excel from there. But you didn't like that idea.

--
Steve Schapel, Microsoft Access MVP
 
Reply With Quote
 
peter_blind@trashmail.net
Guest
Posts: n/a
 
      12th Jul 2007
Hey

Well if you say a temporary table will not blow up the filesize and I
do not have to compress over and over again, then I am listening

My solutions with creating tables in access always blew up the size of
the whole database

Bye


Steve Schapel wrote:
> Robert,
>
> (E-Mail Removed) wrote:
> > Sure it would...... but the tool I've create is suppose to create
> > excel files only. I am not really interested in creating tables inside
> > access.

>
> This was your idea, not mine.
>
> > This is a waste of time, and I'll have to compress the access
> > DB after each run.

>
> This is not correct. You could create a temporary table on the fly.
>
> > So the best way for me is the one described.

>
> This is also incorrect.
>
> In my opinion, the best way is to create a query and then export to
> Excel from there. But you didn't like that idea.
>
> --
> Steve Schapel, Microsoft Access MVP


 
Reply With Quote
 
Steve Schapel
Guest
Posts: n/a
 
      12th Jul 2007
Robert,

Sorry, I wasn't clear. You are correct that repeatedly adding and
deleting from a table in an MDB will cause the file to bloat, unless it
is compacted.

Of course, compacting in code is a pretty straightforward step. So that
would be one option.

But I was referring to a using a temporary table in a separate MDB that
you create at runtime. There is some good information about this here:
http://www.granite.ab.ca/access/temptables.htm

On the other hand, the approach I was suggesting was along these lines...

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.CreateQueryDef("Temp", clipstr2)
If DCount("*","Temp") < 65536 Then
DoCmd.TransferSpreadsheet ...
End If

--
Steve Schapel, Microsoft Access MVP

(E-Mail Removed) wrote:
> Hey
>
> Well if you say a temporary table will not blow up the filesize and I
> do not have to compress over and over again, then I am listening
>
> My solutions with creating tables in access always blew up the size of
> the whole database

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Do I need to close a recordset clone like a table recordset? Jeff Stroope Microsoft Access Security 4 7th Aug 2008 05:43 AM
Dump recordset data into a table =?Utf-8?B?V1ND?= Microsoft Access VBA Modules 1 16th Nov 2006 08:15 PM
Mini dump or dump.exe =?Utf-8?B?c3F1ZWVreWtpdHRlbg==?= Windows XP Help 1 24th Jan 2005 11:29 PM
How to convert recordset of only PKs into full field recordset? Bam Bam Microsoft Access 3 4th Oct 2004 04:04 AM
How to dump a table into an msdos text file Ruby Tuesday Microsoft Access Forms 5 12th Apr 2004 04:22 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:24 PM.