none
How to connect to SQL Server database with Excel VBA

    Question

  • I have never connected to a database with VBA.  Currently my company has a web-based database.  The server is located in our office though.  What information do I need to get to connect to the database?  Can I connect to it thru the network or must it be web-based?  How do I find the name of the SQL Server Name?  Do I need a password?  What libraries do I need to reference in the VBA IDE?  Is there anything I need to do or search in the SQL Server. Details would greatly be appreciated!

    Thanks, Ryan


    Ryan
    Thursday, April 14, 2011 1:36 AM

Answers

  • Below is an example from ACEES VBA help.  I usually refer to the ACCESS VBA help when performing a connection to a database.  The Access code will work exactly the same as Excel VBA provided you add the corect references to your VBA Projhect.  The ADO method is the Active Data Object..  So to use the code do the following in Excel VBA

     

    Tools - Rerences add the follwing two objects.  Make sure you click the box next to the object and press OK.

    1) Microsoft Access XX.X Object Library

    2) Microsoft ActiveX Data Objects X.X Library

    Use the latest version of each library on your PC.

     

    Don't change the string : driver={SQL Server};

    Connection 1 should work but left all the options in just in case the 1st doesn't work.

     

    If none of these connection string work then you can look at this webpage

    http://www.connectionstrings.com/sql-server-2005

     

    I would step through the code using F8 in VBA.  If yo can step past the "Open" statement without getting an error the your successfuly made the connection.  If you can make the connection then you can access the databae either by sequencing through the each record/row in the database or using SQL statements.  The SQL statements create a recordsets which is a substet of rows and columns in the database.  Lets concentrate on getting the connection wooking and then I wil help with getting the data.  the simpliest option is to create a QueryTable and pull the entire database into excel.  The simpliest form of the SQL would be

    SELECT * FROM TABLE1;

    You can replace the wildcard * with only the columns/fields you want.  You can also add a WHERE to the SQL to return only specific rows from the database.

     

    Public Sub ConnectionStringX()
    
      Dim cnn1 As ADODB.Connection
      Dim cnn2 As ADODB.Connection
      Dim cnn3 As ADODB.Connection
      Dim cnn4 As ADODB.Connection
    
      ' Open a connection without using a Data Source Name (DSN).
      Set cnn1 = New ADODB.Connection
      cnn1.ConnectionString = "driver={SQL Server};" & _
        "server=srv;uid=sa;pwd=pwd;database=Pubs"
      cnn1.ConnectionTimeout = 30
      cnn1.Open
      
      ' Open a connection using a DSN and ODBC tags.
      Set cnn2 = New ADODB.Connection
      cnn2.ConnectionString = "DSN=Pubs;UID=sa;PWD=pwd;"
      cnn2.Open
      
      ' Open a connection using a DSN and OLE DB tags.
      Set cnn3 = New ADODB.Connection
      cnn3.ConnectionString = "Data Source=Pubs;User ID=sa;Password=pwd;"
      cnn3.Open
      
      ' Open a connection using a DSN and individual 
      ' arguments instead of a connection string.
      Set cnn4 = New ADODB.Connection
      cnn4.Open "Pubs", "sa", "pwd"
     
      ' Display the state of the connections.
      MsgBox "cnn1 state: " & GetState(cnn1.State) & vbCr & _
        "cnn2 state: " & GetState(cnn2.State) & vbCr & _
        "cnn3 state: " & GetState(cnn3.State) & vbCr & _
        "cnn4 state: " & GetState(cnn4.State)
    
      cnn4.Close
      cnn3.Close
      cnn2.Close
      cnn1.Close
    
    End Sub
    
    Public Function GetState(intState As Integer) As String
    
      Select Case intState
        Case adStateClosed
          GetState = "adStateClosed"
        Case adStateOpen
          GetState = "adStateOpen"
      End Select
    
    End Function
    
    
    

    jdweng
    • Marked as answer by Bruce Song Tuesday, April 26, 2011 2:25 AM
    Saturday, April 16, 2011 8:39 AM

All replies

  • Are yo uywriting to the database or reading from the database.  What version of Excel are you using.  which SQL Server are you connecting to.

     

    Connecting with VBA should be the same as connecting maually in terms of Uername and Password.  Yo ualso should be using the same connecttion string that yo are using when connecting with other software or terminals.

    There are lots of ways of connecting connecting so dependingg on the version of Excel and the SQL server would determine the libraries you will use.

    I would thing usnin gthe Microsoft Jet Engine is the most robust method that will work for most combinations of Excel and SQL server, but may not be the most effficient method or provide the moist features.

    You questtion can havve many answers and if you asked the same questtion to 10 MVP's you will get 10 diffferent answerrs.  Please provide more details so we can give a better answer.

    What I always recommend doing is to  start recording a macro and the using the Datta menu in excel trying connecting manually and see if you are sucessful.  The recorded macro wil be helpful in starting you VBA code.

    When importing data from a SQL server to Excel you can use a query tabble (2003) or list table (2007, and 2010) which will automticaly download data with even writingg a macro.  the list tabbles and query tabbles can be setup to auttomatically update periodically by using a timer, or can be setup to only update when the workbook is opened.

    You also have options of usingg a query alreqady stored in the SQL server or generate a unique query in the excel workbook.  You may not evven want to write the VBA code in excel, instead put the VBA code into Access.  Or maybe you want to write the code in Visual Studio that will open transfer the data between excel and the SQL Server.

    I've done this task lots of diffferent ways myself and don't have one single metthod that I recoimmend over any other method.  I also use VBA, VBnet, and C# in writing the code.  When I write the code I always consider who is going to use the code, and who is going to maintain the code.  I think the macros should be writen where most of the data is going to be entered into the dattabase.  If you already have forms developed in the SQL server than I would recommend writing the code in the server.  If most of your data is being entered into the excel workbooks, then I would recommend writine the macros in Excel VBA.  If you have you data automatically being generated fro some applications that outputs the datta into excel and you need to take the workbooks and automtically upload them into the SQL Server than it probably doesn't matterr which method you use.

     

     


    jdweng
    Thursday, April 14, 2011 5:59 AM
  • Thanks for replying.  Hopefully I can supply more details in this post.

    Some users are using Excel 2003, but most are now running Excel 2007.  For now I would I would like only read the database, but in the near future I would like to run some updates.  I think we are running SQL Server Express 2000 or 2005.  I can find out for sure if I need to.

    The developer of our database interface left years ago and we do not have his contact information anymore.  So I'm in the dark as to finding out any passwords to the database.  I may be able to find and open his interface program solution file and search for the connection string he uses.  I'm not sure if he used C# or VB.

    Let me describe the scope of my project.  I have an Excel workbook named Quote Generator (aka QG).  The sales team uses this workbook to quote products we sell.  It utilizes many modules of code and forms I wrote.  On the main worksheet I have a button labeled "Customer Information".  When the users clicks this button a userform is shown.  Currently, they have to type all the customers information by hand.  I'd like to access that same data in the database so they don't have to manually enter contact information every time.

    So when the form is loaded, I'd like the user to enter the company name or contact person's name then click a Search button.  When "Search" is clicked I will write a query to find all related data and return the dataset onto the form.  Then the user can select which contact they want to use and that data will be applied to the worksheet.  I can either write the query in VBA or write a stored procedure in SQL Server.  I guess the stored procedure would be faster.

     

    Thanks of the help on this so far,

    Ryan H


    Ryan
    Thursday, April 14, 2011 1:28 PM
  • How are you presently connecting to the databae? what commands are you using?


    jdweng
    Thursday, April 14, 2011 5:11 PM
  • I'm really not sure at this point.  The programmer designer left years ago.  I'll have to try to find the solution file for the interface he built, then look at all the procedures to see how he sets up a connection.  I will try to find that tomorrow.

    I did confirm that we are running SQL Server 2000.

    Stand by...

    Thanks, Ryan


    Ryan
    Thursday, April 14, 2011 9:02 PM
  • Ok, I have some more information.  He actually used C# and XML in Visual Web Developer 2005 Express Edition along with SQL Server Express 2005 (9.0.4035, SP3).  I know nothing about web development and very little about XML.  But I was able to connect to the database using the wizard and this info:  Server=ADSVR01, Database=wm, User ID=1234, Password=instinct.

    I'd like to return a data set, but not sure the best form controls to use in VBA to display the dataset.  The dataset will be company address info and contact person's info.

    Is this enough information to move forward?

     

    Thanks, Ryan


    Ryan
    Saturday, April 16, 2011 1:06 AM
  • Below is an example from ACEES VBA help.  I usually refer to the ACCESS VBA help when performing a connection to a database.  The Access code will work exactly the same as Excel VBA provided you add the corect references to your VBA Projhect.  The ADO method is the Active Data Object..  So to use the code do the following in Excel VBA

     

    Tools - Rerences add the follwing two objects.  Make sure you click the box next to the object and press OK.

    1) Microsoft Access XX.X Object Library

    2) Microsoft ActiveX Data Objects X.X Library

    Use the latest version of each library on your PC.

     

    Don't change the string : driver={SQL Server};

    Connection 1 should work but left all the options in just in case the 1st doesn't work.

     

    If none of these connection string work then you can look at this webpage

    http://www.connectionstrings.com/sql-server-2005

     

    I would step through the code using F8 in VBA.  If yo can step past the "Open" statement without getting an error the your successfuly made the connection.  If you can make the connection then you can access the databae either by sequencing through the each record/row in the database or using SQL statements.  The SQL statements create a recordsets which is a substet of rows and columns in the database.  Lets concentrate on getting the connection wooking and then I wil help with getting the data.  the simpliest option is to create a QueryTable and pull the entire database into excel.  The simpliest form of the SQL would be

    SELECT * FROM TABLE1;

    You can replace the wildcard * with only the columns/fields you want.  You can also add a WHERE to the SQL to return only specific rows from the database.

     

    Public Sub ConnectionStringX()
    
      Dim cnn1 As ADODB.Connection
      Dim cnn2 As ADODB.Connection
      Dim cnn3 As ADODB.Connection
      Dim cnn4 As ADODB.Connection
    
      ' Open a connection without using a Data Source Name (DSN).
      Set cnn1 = New ADODB.Connection
      cnn1.ConnectionString = "driver={SQL Server};" & _
        "server=srv;uid=sa;pwd=pwd;database=Pubs"
      cnn1.ConnectionTimeout = 30
      cnn1.Open
      
      ' Open a connection using a DSN and ODBC tags.
      Set cnn2 = New ADODB.Connection
      cnn2.ConnectionString = "DSN=Pubs;UID=sa;PWD=pwd;"
      cnn2.Open
      
      ' Open a connection using a DSN and OLE DB tags.
      Set cnn3 = New ADODB.Connection
      cnn3.ConnectionString = "Data Source=Pubs;User ID=sa;Password=pwd;"
      cnn3.Open
      
      ' Open a connection using a DSN and individual 
      ' arguments instead of a connection string.
      Set cnn4 = New ADODB.Connection
      cnn4.Open "Pubs", "sa", "pwd"
     
      ' Display the state of the connections.
      MsgBox "cnn1 state: " & GetState(cnn1.State) & vbCr & _
        "cnn2 state: " & GetState(cnn2.State) & vbCr & _
        "cnn3 state: " & GetState(cnn3.State) & vbCr & _
        "cnn4 state: " & GetState(cnn4.State)
    
      cnn4.Close
      cnn3.Close
      cnn2.Close
      cnn1.Close
    
    End Sub
    
    Public Function GetState(intState As Integer) As String
    
      Select Case intState
        Case adStateClosed
          GetState = "adStateClosed"
        Case adStateOpen
          GetState = "adStateOpen"
      End Select
    
    End Function
    
    
    

    jdweng
    • Marked as answer by Bruce Song Tuesday, April 26, 2011 2:25 AM
    Saturday, April 16, 2011 8:39 AM
  • Hi Ryan,

    Have you resolved your problem yet and do the suggestions help you? If you still have any concern on the thread, feel free to follow up. 

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, April 20, 2011 8:52 AM