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 "<your connection string to sql server>"

set rs=new adodb.recordset
rs.cursorlocation = adUseClient
rs.Open sSQL,cn,adOpenStatic,adLockBatchOptimistic,adCmdText

'disconnect the recordset:
set rs.Activeconnection=Nothing
cn.close

' add your records to the recordset as normal
for <each record in the Excel dataset>
rs.AddNew
....
rs.Update
next

'reconnect the recordset
cn.Open
Set rs.ActiveConnection = cn

'Call the batch update method:
rs.UpdateBatch

rs.close: set rs=nothing
cn.close: set cn=nothing


You should probably investigate using SQL Server Integration Services
(SSIS) rather than ADO for this task. Using SSIS, data can be read
directly from the Excel spread*** and imported into the SQL Server
table ... much quicker than with ADO.

Bob Barrows



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.



.