 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / September 2007Selecting Data in a Table to Copy to Excel
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 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. > 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 [quoted text clipped - 14 lines] > Thanks! > Graham 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? > 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 [quoted text clipped - 24 lines] > > Thanks! > > Graham Check out TransferSpreadsheet in Help file.  SignatureKen Snell <MS ACCESS MVP> >I need to modify data in a table, but I would like to create a backup of >this [quoted text clipped - 19 lines] > Thanks! > Graham 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 > Check out TransferSpreadsheet in Help file. > [quoted text clipped - 21 lines] > > Thanks! > > Graham 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 SignatureKen Snell <MS ACCESS MVP> > 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 [quoted text clipped - 34 lines] >> > Thanks! >> > Graham 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 > 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 [quoted text clipped - 45 lines] > >> > Thanks! > >> > Graham 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. > 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 [quoted text clipped - 14 lines] > Thanks! > Graham Thanks Mark, this is exactly what I needed. I got it working perfectly and it's very quick. Graham > copyfromrecordset method? > [quoted text clipped - 20 lines] > > Thanks! > > Graham |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.