Re: VBA - Create a recordset with no records for inserting



Bob,

Thanks, that WHERE 1=2 trick is just what I needed.

And, you were also right that this is not the way to do this. I ended up
killing the process after 5 minutes. I never expected calling the stored
procedure 100,000 times would be so much faster than
ADODB.Recordset.UpdateBatch.

In my case, I need to be in Excel to gather the data because it's spread all
over the workbook in human friendly form. We tried submitting the workbooks
directly and using SSIS to do this, and our conclusion was we shouldn't be
getting SSIS involved until we had our data suitable for bulk insert.

In this case, we are already in Excel and have a database connection, so I'm
going to get a hidden share on the database server, have Excel drop text
files suitable for bulk insert, and then call a procedure which uses the
Service Broker to push an event into the queue, which bulk inserts the file
into the table in question. This also has the advantage of giving control
back to Excel immediately.

I'm wearing enough hats already. Admittedly, having also graphically
programmed in LabVIEW for 20+ years, I find the interface to SSIS too painful
for me. I'd rather write scripts in Management Studio and wait till at least
version 3 of SSIS before I try that route again.

Thanks
Bob

"Bob Barrows [MVP]" wrote:
dim rs as adodb.recordset
dim cn as adodb.connection
dim sSQL as string

sql = "select ColumnA,ColumnB,Columnc,Columnd,Columne," & _
"Columnf, Columng from yourtable where 1=2"
' the "where 1=2" guarantees that an empty recordset will be returned

set cn=new adodb.connection
cn.open "<you