Saturday, March 14, 2009

How to Connect SQL Express 2005 from VBA

Excel VBA retrieve data from SQL Server 2005


Here is a way to connect to SQL Express 2005 from Excel VBA


Sub Connect2SQLXpress()
Dim oCon As ADODB.Connection
Dim oRS As ADODB.Recordset
Set oCon = New ADODB.Connection
oCon.ConnectionString = "Driver={SQL Native Client};Server=.\SQLEXPRESS;Database=DB1; Trusted_Connection=yes;"
oCon.Open
Set oRS = New ADODB.Recordset
oRS.ActiveConnection = oCon
oRS.Source = "Select * From Table1"
oRS.Open
Range("A1").CopyFromRecordset oRS
oRS.Close
oCon.Close
If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCon Is Nothing Then Set oCon = Nothing
End Sub


The code uses ActiveX Data Objects (ADO). You need to add a reference to it as shown below

9 comments:

  1. Anonymous4:27 AM

    Good Post. Thanks

    ReplyDelete
  2. Anonymous3:17 PM

    Hi. Any idea why I would get

    Cannot open database "Database1" requested by the login. The login failed.

    I'm using Windows Authentication. Thanks !

    ReplyDelete
  3. Anonymous6:35 AM

    Check database name....I think that is the problem.....

    ReplyDelete
  4. Anonymous9:15 AM

    THANK YOU sooooooooooooooo much

    that works like a charm

    ReplyDelete
  5. Anonymous6:41 AM

    hi all,
    when i run this code, i would get "run time error -2147467259
    automation error unspecified error"

    i used debug, and the error accord in oCon.Open

    please help me.

    ReplyDelete
  6. Please check if the following helps you http://support.microsoft.com/kb/328306

    http://support.microsoft.com/kb/318432/

    ReplyDelete
    Replies
    1. Anonymous11:33 PM

      hi shasur,
      Thank u for your reply ,i have checked the link u posted. i didnt find any helpfully info

      Delete
  7. Anonymous1:59 AM

    Iam getting Run time error

    Automation Error,
    Unspecified Error

    ReplyDelete
  8. Thank you for being awesome!

    If anyone want to use the value in your code instead of printing it to your sheet use:

    oRS.MoveFirst
    Debug.Print oRS.Fields(0)

    Instead of:

    Range("A1").CopyFromRecordset oRS

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group