How to dump recordset into a table ?

[peter_blind]

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

 

[Steve Schapel]

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.

 

[peter_blind]

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]

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.

 

[peter_blind]

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]

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?

 

[peter_blind]

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]

Robert,

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.

 

[peter_blind]

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]

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

 

[peter_blind]

Hey


Well...... I'll try the temp DB approach. Sound pretty good

DCOUNT doesn't take querydefinitions as input.

thank you very much

 

[Steve Schapel]

Robert

Well...... I'll try the temp DB approach. Sound pretty good

Let us know how you get on.

DCOUNT doesn't take querydefinitions as input.

That's the reason I used the CreateQueryDef method. DCount function
will work with the query thus created. Trust me, it's the simplest and
most efficient way to do this! Did you look closely at the concept I
was proposing?

thank you very much

You're welcome.

 

[peter_blind]

K it works with the querydef...... I had a spelling mistake..sorry.

But this method calls the query 2 times. Once it counts the records,
then it is being called again to export the data. This is time
consuming. I stick to your second suggestion

thank you

bye

 

[peter_blind]

It finally works thanks you again ............well.....and again




##############
Set ws = CreateObject("dao.DBengine.36")
If Dir(CurrentProject.Path & "\temp.mdb", vbNormal) <> "" Then
Kill CurrentProject.Path & "\temp.mdb"
End If

ws.CreateDatabase CurrentProject.Path & "\temp.mdb",
";LANGID=0x0409;CP=1252;COUNTRY=0", dbVersion40
Set acapp = GetObject(CurrentProject.Path & "\temp.mdb",
"Access.Application")

DoCmd.SetWarnings False
Call astatus("Datensätze zählen")
DoCmd.RunSQL clipstr2
DoCmd.SetWarnings True

Set check_64000 = acapp.CurrentDb.OpenRecordset(var_table)
If check_64000.RecordCount < 65536 Then
Call astatus("Daten exportieren...")

text_gewaehlter_pfad.SetFocus
acapp.DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel8, var_table, text_gewaehlter_pfad.Text &
var_table & ".xls"

End If



Set check_64000 = Nothing
acapp.Quit
############

 

[peter_blind]

Hello Steve

One more thing just came up. Please look at this code
########
Set acapp = GetObject(CurrentProject.Path & "\temp.mdb",
"Access.Application")
.......
Set check_64000 = acapp.CurrentDb.OpenRecordset(var_table)
########

the last line "set check..." gives an error during runtime, because it
tries to open a recordset before acapp has finished loading.

Error Message= "Microsoft Jet-Databasemodule could not find the
table..."

I tried to catch the error with "On Error" and make Access retry the
procedure until acapp is fully loaded, but "On error goto..." doesn't
react. It simply gives an error message

Is there a way of checking the status of an application ? I found the
method Syscmd, but it doens't apply to applications.


Any ideas ?



bye

 

[peter_blind]

Hey never mind

"On Error goto..." did not help
but
"On error resume next" + "err.number"

did

thanks again....very much