New download available:
Insert lots of records into a table : Access vs. SQL

Description:
Our German office recently upgraded an application from MSAccess to SQL Server, and noted some horrifying performance drops when adding many records to a table (ie. parsing a text file and inserting records). We scratched around and came up with the attached solution.
We tried 5 methods of adding data, and finally settled on the follwing:
Convert the data to add into an XML document and use SQL2000 XML capabilities to bulk-insert the data.
Try the tests for yourself and see the difference it made (approx. 500% faster than other methods).
Thought you might find it useful.

Note
You will have to register the kwconnectionstring.dll - it just constructs an ADO connection string for you, and you'll need MSXML4+.

Usage:
Run the SQL script to create the SQL database, and alter the connection string to point to your sql server.
Run the app.
Click Command1, this will run the inserts into SQL using ADO recordset and time it.
Change Frame1 to Access lokal and click again. You see the speed difference and issue!
Change Frame1 to SQL Server again and
select "Use SQL XML" and click Command1 again.
Multi-test will average out the tick count for a number of tests for you.