Excel and SQL 2000

[Sammy25]

Hi.

We are working with SQL server 2000. We have developed an interface in Excel (VBA) that returns a record set to a worksheet then moves that data into memory. What we actually want to do is return the record set directly to memory. Can anyone help with the most straight foward way to achieve this?

 

[RoyVidar]

I don't know how you're performing this now, but opening a recordset seems what you're after. Involves declaring, instantiating and opening a connection and recordset object (ADO - requires a reference to Microsoft ActiveX Data Objects 2.# Library - in VBE - Tools | References):

[tt]dim cn as adodb.connection
dim rs as adodb.recordset

set cn = new adodb.connection
' find connection string info, for instance here
' Connection String for SQl Server
cn.open
set rs = new adodb.recordset
with rs
.activeconnection = cn
.locktype = adlockoptimistic
.cursortype = adopenkeyset
.open "name of table/view"
if ((not .bof) and (not .eof)) then
' opened recordset
end if
end with[/tt]

The above shoule give an updateable recordset.

There are many ways of opening recordsets based on tables, stored procedures etc from SQL server. A search including some of those keywords, would probably give some results. Perhaps also extend the search to some of the Access fora (forum705 in perticular), and the VB5/6 forum (forum222).

Roy-Vidar