Selecting Data in a Table to Copy to Excel

[Guest]

I need to modify data in a table, but I would like to create a backup of this
table everytime before I do so. Right now I am opening the table in an ADODB
recordset, opening and Excel workbook and then looping through each record
and populating the Excel worksheet. The table has about 2000 records in it,
and the process of pupulating the Excel worksheet is painfully slow as it
loops through me while statement.

If I were to do this manually, I open the table in Access, select all of the
records and copy them, then I switch to Excel and paste them into my
worksheet. I can complete this process in about 5 seconds, and that includes
the time it takes me to move my mouse!

I'm wondering if there is any way of mimicking this action with VBA.

Any help would be great.

Thanks!
Graham

 

[Guest]

I've never tried this, but I think you can use the CopyObject action to copy
a table. Just define an empty Access database that you will use to store your
backup. Then in VBA code you can either use Docmd.CopyObject or else create a
macro using the CopyObject action and then run the macro in VBA code. The
parameters to COpyObject are the name of the database you are copying to, the
new name for the table (if you don't want to use the same table name), the
type of Object (table in your case) and the object to be copied (the table
itself). Doesn't sound like you really need to use Excel if all you're doing
is backing the table up.

 

[Guest]

I think that would probably work. I guess the only reason I have for wishing
to use Excel is familiarity. I do want the back ups to be outside of my live
database (which actually contains mostly linked tables to MSSQL), but your
right that creating .mdb files with the table in it would serve the same
purpose. I'll give it a try.

On the other hand, I would be interested for interests sake if nothing else
if anyone knows how to utilize commands with VBA as I had originally
intended. Is this possible?

 

[Ken Snell \(MVP\)]

Check out TransferSpreadsheet in Help file.

 

[Guest]

copyfromrecordset method?

http://www.mvps.org/access/modules/mdl0035.htm

I've used it with DAO recordsets, but I think it works for ADODB as well.

 

[Guest]

Thanks Ken, this is a good idea and based on what I've told you guys this
method should work. Uunfortunately though I don't think it gives me enough
options. I'm actually looping through multiple databases on our SQL Server
and backing up the table every time. I would like each backup to appear on
it's own worksheet in the Excel file. I don't think this method can produce
this result. I'm going to try Mark's suggestion.

Thanks

 

[Ken Snell \(MVP\)]

Yes, it can .... if the query name from which the data come is different
each time, you can export to the same EXCEL file and a new worksheet with
that table/query name will be created in the file.

If you're writing into an existing EXCEL file that already contains that
worksheet name, it'll overwrite the data on that sheet.

Here is info about how the Range argument of TransferSpreadsheet can be used
for exports (it's an undocumented feature):
http://alexdyb.blogspot.com/2006/07/export-to-excel-range.html

 

[Guest]

Thanks Mark, this is exactly what I needed. I got it working perfectly and
it's very quick.

Graham

 

[Guest]

Alright, thanks Ken. I'm sure you're absolutely right, but I got Mark's
suggestion working exactly how I wanted it to so I'll just stick with that.
Thank you very much for your help though.

Graham