04-16-2002, 09:21 AM
I want to import a text file that has 100K+ records into a Access 97 table on a regular basis. I have tried to find the fastest way to do this. I have found reference to adCmdText and Sql but I have not found a thorough example of this. It is my understanding that I can do the import without referencing a recordset which will actually make it quicker. Anyone have a good example? The text file could be fixed-width or delimited.
04-16-2002, 09:48 AM
I recently discovered that building a recordset and using .AddNew was significantly faster than using an SQL INSERT INTO. With Access at least. Which is contrary to what I've always been taught. I haven't had a chance to test this with SQL Server yet. I know ADO is very optimized, but I still find it strange. In a test I did, the time to insert ~30k records dropped from 40 seconds to 8. I'm wondering if it is actually all the string manipulations (which vb is terrible at) building the SQL that actually caused this.
Your question gives me a good opportunity to check for opinions about this. :)
Also, is your file delimited, or fixed length? If it's fixed length, making a UDT, opening the file As Random, and using a Get is much faster than using Line Input and Mid$ to parse it.
And as far as "do the import without referencing a recordset", I'm not sure what that's supposed to mean.
If this doesn't help, post back and let us know which parts of this you don't know how to do.
04-16-2002, 10:11 AM
reboot, when you were doing the Insert SQL, were you using the
prepare and execute model with the Command object?
04-16-2002, 10:27 AM
Reboot, I can use either delimited text file or fixed width. Does delimited make it easier/quicker?
04-16-2002, 10:59 AM
Uhmmm.... No I wasn't. I guess I didn't realize Access supported that.
04-16-2002, 11:09 AM
I am pretty sure that it does. I can't remember if I have ever tried
it with Access, but I see examples in msdn that indicate it does.
04-16-2002, 11:19 AM
Neat. I'll try it like that too. I don't do a lot of Access, so I'm not all that familiar with it. I actually thought 8 seconds wasn't too bad, but DTS was still faster. Maybe this will get it down to that kind of speed.
04-16-2002, 11:55 AM
Ok, so it does work with Access. :)
I switched it over to prepared statements, but it didn't really seem to make any difference. Well, it's actually a couple of seconds slower, but that might catch up with more records. When I get a chance, I'm going to try all three methods in SQL Server just out of curiosity. Or maybe just the .AddNew and the prepared, since the plain INSERT INTO in a loop is obviously always going to be the slowest.
04-16-2002, 03:14 PM
Yeah Thinker, it does support the Command object, remember a
couple of weeks ago we both gave the solution to a post, my
version used the Command. ( I can't find it now, it was an insert
into a different database)