Upload entire sheet to SQL database

[paulmezick]

I know there has to be an easier way to do this. I'm at work right now and I use an existing VBA macro to upload about 40 different sheets of data to a database. For some reason, whoever coded this, decided it would be a great idea to submit each record one line at a time via an INSERT query. Essentially its as follows:

SQL = "INSERT row1 INTO..."
run query
SQL = "INSERT row2 INTO..."
run query
Repeat for every row

This wouldn't be too bad if there were only a few lines, but we're talking 10,000 lines per sheet. This whole process takes hours when it should not take nearly as long.

I'm assuming there is a way to write some code to insert a block of text (ex: columns A:D) into a table, essentially automating what would be done if I used a DTS package to load a csv or txt file. Am I correct in assuming this? Does anyone know how to do this?

Thanks!

[rylo]

Hi

At the bottom of this thread is some code that will allow you to open access from excel and import a spreadsheet. Seems like it should point you in the right direction.

HTH

rylo