If you are using Excel2000 or higher you can do this:

dim cn as new adodb.connection, rs as new adodb.recordset
cn.Open "Provider=SQLOLEDB;Data Source=FRKfwd03;" _
& "Initial Catalog=ORS2K;UID=SA;PWD=;"
rs.Open "tbl1", conn, adOpenDynamic, adLockPessimistic

Sheets("Sheet1").Range("A1").CopyFromRecordset

If you have Excel97 then you can still use the above but
the copyfromrecordset thing won't work with ADO (wasn't
invented yet). So you need to create a DAO recordset in
Addition to the ADODB recordset (make a reference to DAO
3.51, also need to make a reference to Microsoft ActiveX
Data Objets 2.5 or higher (MDac2.5or higher)) and copy row
by row the data from rsADo to rsDAO and then you can use
CopyFromRecordset. Way faster than ODBC even for a few
thousand records - (note: if you have more than a few
thousand records to plant into excel for sql Server, then
you need to do more crunching on the sql server side).
You should be able to transfer a few thousand records this
way (with Excel2000 or higher) in less than one second,
especially if you use the ADODB command object and get the
data from a stored procedure on Sql Server using
copyfromrecordset.

A J

-----Original Message-----
I added some VBA code into an excel Spreadsheet to get
some data from a SQL
server using QueryTables and ODBC (I defined a DSN, etc.).

Is there a faster method to access SQL Server (ADO)?

Note: I am a beginner in VBA.

Thanks!
Danut


.