Best method for moving 100k records from Excel into new Access table?

[VBAjedi]

Hey all,

I've written some VBA in Excel that will generate about 100,000 rows of data, and I need these to end up in a new table in an existing Access database. Performance/speed is obviously a concern here.

Conceptually, would it be best to load the rows into an ADO recordset as I'm looping through creating them, and then write them all at once to Access (using a maketable query or???)? Or should I have the code go out and create a blank table in Access first, then write the rows across one by one as my code creates them in Excel?

Just wanted opinions on approach before I start figuring out the code (haven't done much with ADO).

Thanks!

Luke



VBAjedi

 

[kjv1611]

Any reason you're creating them in Excel rather than Access to begin with? If you are creating the records in Excel to go to Access, I'd say move the code to Access, and create the records there.

 

[VBAjedi]

For now my boss wants it in Excel. The code reads all of its runtime parameters from settings worksheets, and also uses some worksheet functions (VLOOKUP, etc). Porting the code over to Access is definitely feasible, just not in the cards at the moment.

So if that's the best option, what's second-best?

VBAjedi

 

[PHV]

What about the TransferSpreadsheet method in Access ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VBAjedi]

Hmmm... that sounds interesting, PH! I'm dealing with more rows than Excel can handle on a single worksheet, but for testing purposes I've broken up the output to multiple sheets. So I do have it on sheets that I could import from Access.

However, one of the things I'm after here is to see if I can gain performance/speed by skipping the "write results to sheet" step and writing the output directly to the Access database. My code currently takes 6 minutes to execute, but if I turn off the "write results to sheet" part it runs in a bit over 1 minute (and yes, that 6 minutes is after setting Application.Screenupdating = False and xlCalculationManual). So I have that five minutes in my crosshairs.

Still, I'll take a look at TransferSpreadsheet to see if it can play a role in what I'm doing!



VBAjedi

 

[kjv1611]

Also, you say it's more records than Excel can handle - what version of Excel? It could be that it's worth upgrading to Excel '07 or '10 if possible... that is if you plan on keeping it in Excel after all.. Beginning in '07, Excel can handle up to 1 million rows vs the 65k in previous versions.

 

[VBAjedi]

Yeah, this is government work. We're on Office 2003... the upgrade to Office 2010 is "coming" but it will be next fall before it actually gets here. Maddening!

I decided to go ahead and code out the necessary pieces to open an ADOBC connection and write the records across to an Access table one row at a time as I create them. That cut my runtime from 6 minutes to 4 and eliminated the old manual transfer step. So it's a "win".

I definitely wouldn't mind a better way, though. I keep wondering about loading all the created records into an ADO recordset and then pushing that across to Access all at once (i.e. in a single data block without having to loop through all the recordset rows). I'm guessing that would be faster still if it's feasible.

VBAjedi

 

[kjv1611]

I would guess at that being faster as well.

As PHV mentioned, will the DoCmd.TrasnferSpreadsheet from Access not work on this data? I have had an instance or two in the past where that wouldn't work, but where it does, it's extremely fast.

OR if the transferspreadsheet will not work, what about (and you could put all this in Access' VBA rather than Excel if you want - and I'd recommend, therefore having it in one place):
1. Use Excel to Export to Text
2. Use Access (using a import spec) to import the data with docmd.transfertext).

Reason I mention putting the code in Access vs Excel, is that beginning with Excel '07, if the workbook isn't named with an "m" in the file extension, then no code will run at all anyway... besides all the extra security blocks that are in place unless you disable them. So at least that way, you'd have:
1. One code source to worry about
2. No concerns about file extension in Excel (assuming you don't save the VBA in your personal macro workbook)
3. One less application to worry about with security prompts and/or settings.

By the way, if/when you do ever get upgraded, if you need/want o change the security settings (will depend upon corporate policies of course), you can modify as needed under the file menu, then "Excel Options" or "Access Options", then Trust Center - Trust Center Settings - the 2 you'd most likely have to deal with are: Macro Security and Trusted Locations.