accessing SQL server from Excel/VBA (instead of ODBC)

[Danut]

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

 

[Andy Wiggins]

Probably not a faster way.

Here's something that might help you. It was built for Excel and Access but
is easily changed for Sql Server.

It includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table and add data to it,
* select data from a table,
* delete a table,
* delete a database.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.


--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[A J]

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

 

[Danut]

I get "Compile error: User-defined type no defined" at

dim cn as new adodb.connection

Where is the "adodb" class defined for VBA? I looked in the Object Browser
and I did not find it.

I'm using Excel 2002 (10.4524.4219) SP-2, the one that comes with Office XP.

 

[Danut]

Thanks A.J.!
Thanks Andy!

I got it:
http://www.erlandsendata.no/english/vba/adodao/exportado.php
"The macro example assumes that your VBA project has added a reference to
the ADO object library.
You can do this from within the VBE by selecting the menu Tools, References
and selecting Microsoft ActiveX Data Objects x.x Object Library.
Use ADO if you can choose between ADO and DAO for data import or export. "


Now the macro is abotu 3-4 times fatser due to this change + other changes I
have made not related to the database access.

 

[Daniel P.]

Thanks Ender!!! ;-)

Probably not a faster way.

Here's something that might help you. It was built for Excel and Access but
is easily changed for Sql Server.

It includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table and add data to it,
* select data from a table,
* delete a table,
* delete a database.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.


--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"